(VBA) FormulaArray Character Limit

cheesy_goodness

New Member
Joined
May 19, 2014
Messages
24
I'm running into some problems with the character limit on the .FormulaArray property in vba. I've seen some workarounds but as far as I can tell, none of them would apply to my situation.

The workbook I'm working on is a model that, in part, imports data from another workbook. The formula array I'm having trouble with is a simple Index/Match formula, but it is rather lengthy because 1) the path to the other workbook is long and 2) I'm matching three values, so the path has to be in the formula a total of 4 times (once for the Index function and 3 times for the Match function).

The formula is meant to be put in each cell within a certain range. The formula for one cell is below (note the paths and range names in the other workbook have been altered for this example)
Code:
=INDEX('H:\Cost Studies\Company Cost Studies\Company 2018 Cost Study\Surveys\1 Consolidated Surveys Company.xlsm'!Survey_Results_Totals,MATCH(N3&O3&P3,'H:\Cost Studies\Company Cost Studies\Company 2018 Cost Study\Surveys\1 Consolidated Surveys Company.xlsm'!Branch_Name&H:\Cost Studies\Company Cost Studies\Company 2018 Cost Study\Surveys\1 Consolidated Surveys Company.xlsm'!Company_Division&H:\Cost Studies\Company Cost Studies\Company 2018 Cost Study\Surveys\1 Consolidated Surveys Company.xlsm'!Company_Category,0))
I know the formula above works because if it's put in as a formula, and I manually make it a formula array (CTRL+SHIFT+ENTER), it returns the correct result. I believe the only issue is the character limit. However since the formula above can't really be stripped down into smaller parts, I'm not sure what's the best way to accomplish the goal is.

The line of the macro that's supposed to put the formula array in the workbook is below, where SurveyFormulaX is some part of the string above.
Code:
For Each cell In ActiveSheet.Range(ForMac.Range("Range_For_1st_Number_Of_Hours_Spent_Originating_Survey_Responses").Value)
                With cell
                     .FormulaArray = "=" & SurveyFormula1 & SurveyFormula2 & cell.Row & "&" & SurveyFormula3 & cell.Row & "&" & SurveyFormula4 & cell.Row & SurveyFormula5
                End With
Any ideas would be appreciated

Thank you!
 

Some videos you may like

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,057
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
How long is it without the path? If it's under 255 characters, just open the workbook before putting the formula in and don't include the path.
 

cheesy_goodness

New Member
Joined
May 19, 2014
Messages
24
Thanks for the reply!

With the path removed, it's just a shade over 300 characters. I could shorten the other workbook name and/or change the named ranges used in the other workbook, but that could potentially be problematic since (off the top of my head) there's at least one other macro that relies on those same ranges. It could be done, it would just be time consuming. I was hoping I had been missing some sort of obvious work around.
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,057
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
You could do it in the code, enter the formula, then change them back.
 

cheesy_goodness

New Member
Joined
May 19, 2014
Messages
24
Thanks again for the reply.

If I'm understanding correctly, you're saying to alter the named ranges in the formula array to be something shorter (Rng1 for example) to get the formula array to work, then immediately replace the generic name with the actual name? If so I just tried it and it looks like I'm still having the same issue.

I altered my formula to be:
Code:
INDEX('1 Consolidated Surveys  Company.xlsm'!Rng1,MATCH(N3&O3&P3,'1 Consolidated Surveys  Company.xlsm'!Rng2&'1 Consolidated Surveys Company.xlsm'!Rng3&'1  Consolidated Surveys company.xlsm'!Rng4,0))
Now my macro looks like this
Code:
                             For Each cell In  ThisWorkbook.Sheets("Input").Range(ForMac.Range("Range_For_1st_Number_Of_Hours_Spent_Originating_Survey_Responses").Value)
                                With cell
                                    .Select
                                     .FormulaArray = "=" & SurveyFormula1 & SurveyFormula2 &  cell.Row & "&" & SurveyFormula3 & cell.Row & "&"  & SurveyFormula4 & cell.Row & SurveyFormula5
                                    .Replace "Rng1", "Survey_Results_Totals", xlPart
                                    .Replace "Rng2", "Branch_Name", xlPart
                                    .Replace "Rng3", "Company_Division", xlPart
                                    .Replace "Rng4", "Company_Category", xlPart
                                End With
                            Next
However it still errors out on the formula array line, even though my character count is right around 200.
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,057
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
I was actually suggesting renaming the actual ranges temporarily.

However it might be simpler to provide two distinct numbers (eg 44 and 99 which don’t appear elsewhere in the formula) for the match values initially, then replace each one with the MATCH formulas.
 

cheesy_goodness

New Member
Joined
May 19, 2014
Messages
24
Thanks for the reply

Since the bulk of the characters came from the path I decided it would be easier to copy and paste the source workbook into a shorter path (ie H:\New Folder) and shorten the name of the workbook. That seemed to solve the problem, so I'll likely end up making that part of the whole macro to get it to come out right. It's certainly not ideal but it works. I still don't understand why my approach above didn't work, but it is what it is.

Hopefully this helps someone later.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,971
Messages
5,471,768
Members
406,781
Latest member
aproberts1980

This Week's Hot Topics

Top