Excel vba Evaluate function giving error 2029-CodePudding

For the life of me I can't figure out why Evaluate is throwing error 2029 (#NAME). The project is using Census procedures to allocate seats in the House of Representatives for any given number of house members (currently 435). There are a number of proposals to expand the size of the House. I'm investigating some of the budget and economic implications.

My workbook has a sheet named PVC. I'm trying to find the maximum value in column E (number of seats allocated to a given state) for a value in column C (two-letter state abbreviations). I apologize in advance for the extraneous stuff in this code. Believe it or not, I have removed quite a few lines.

Thanks for any advice. I have experimented with a user-defined function MaxIf() but had very little luck.

Sub CountSeatsEval()

  Dim lNoSeats, lG2, lastrow, lStateRow, lStateSeats, lStateNo As Long
  Dim sFileName, sPathName, sFunction, sSearchValue, sSearchState As String
  Dim sStateAbbr, vStateSeats As Variant
  Dim wsSource, wsTarget As Worksheet
  Dim rMaxRange, rSearchValue, rSearchState As Range
  Dim rLookup1 As Range
  Set wsSource = ThisWorkbook.Worksheets("PVC")
  'Following line is to make life easy temporarily
  Set wsTarget = ThisWorkbook.Worksheets("PVC")
  lNoSeats = wsSource.Range("G2").Value
...
  'Copy and paste G2 to replace formula with value
  wsTarget.Range("G2").Copy
  wsTarget.Range("G2").PasteSpecial (xlPasteValues)
  lastrow = wsTarget.Cells(Rows.Count, 6).End(xlUp).Row
...
  sSearchValue = "'PVC'!E2:$E$" & lastrow
  sSearchState = "'PVC'!$C$6"
...
   sStateAbbr = "CA"
   lStateRow = 6
   vStateSeats = Evaluate("IF((MAXIFS(sSearchValue, sSearchState, sSearchState))>0,(MAXIFS(sSearchValue, sSearchState, sSearchState)),1)")
End Sub

CodePudding user response：

sSearchValue and sSearchState are VBA local variables:

Dim sFileName, sPathName, sFunction, sSearchValue, sSearchState As String

Note that this statement declares sSearchState as a String, and then leaves all other 4 variables without a declared type, making them implicit Variant variables (see VariableTypeNotDeclared Rubberduck inspection details).

Being local VBA variables, they live in the VBA runtime context, and Excel doesn't have the slightest idea about their existence - so you get a #NAME? error:

immediate pane showing CVErr(xlErrName) outputting 'Error 2029'

In Excel you get a #NAME? error whenever you try to evaluate a formula that contains a name that Excel cannot resolve in the current context.

So you need to have VBA evaluate the variables' values before you send the resulting expression over to Excel's calculation engine; you can do this by splitting up the string and using the concatenation operator (&):

vStateSeats = wsTarget.Evaluate("IF((MAXIFS(" & sSearchValue & "," & sSearchState & "," & sSearchState & "))>0,(MAXIFS(" & sSearchValue & "," & sSearchState & "," & sSearchState & ")),1)")

Unqualified, Evaluate will invoke [_Global].Evaluate, which is essentially Application.Evaluate, which may or may not produce the desired results - by qualifying it with a specific Worksheet object, the formula evaluates in the context of that worksheet.

CodePudding user response：

You need an equals sign before the evaluate statement. Try...

Evaluate("=IF((MAXIFS(sSearchValue, sSearchState, sSearchState))>0,(MAXIFS(sSearchValue, sSearchState, sSearchState)),1)")