(VBA) FormulaArray Character Limit

[cheesy_goodness]

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)

=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.

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!

 

[RoryA]

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]

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]

You could do it in the code, enter the formula, then change them back.

 

[cheesy_goodness]

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:

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

                             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]

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]

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.