1

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 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 have removed quite a few lines.
I have experimented with a user-defined function MaxIf().

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
Community
  • 1
  • 1
Tony Lima
  • 65
  • 1
  • 9

1 Answers1

5

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.

Mathieu Guindon
  • 69,817
  • 8
  • 107
  • 235
  • 2
    Helpful hints to `Evaluate` +:) - It might be worth noting that executing a *qualified* `wsTarget.Evaluate()` upon a `sSearchValue` address prefixed by `'PVC'!...` seems to be sort of redundance; on the other hand an unqualified evaluation (regarding all caveats) would need at least the addition of something like `wsTarget.Range("...",External:=True)` – T.M. Oct 11 '21 at 06:56
  • correcting missing `.Address`... `wsTarget.Range("...").Address(False,False,External:=True)` – T.M. Oct 11 '21 at 15:56
  • Very helpful. I thought for sure I had checked the variable definitions. (I thought the compile step was supposed to find errors like that?) Even more thanks for clarifying how Evaluate works. Extremely educational and useful. – Tony Lima Oct 12 '21 at 00:57
  • 2
    @TonyLima Agree with your positive feedback that the comparison of `[_Global].Evaluate` and a directly worksheet related [`Worksheet.Evaluate`](https://learn.microsoft.com/en-us/office/vba/api/excel.worksheet.evaluate) is *"extremely educational and useful"*. - Side note: you might consider accepting a helpful answer in order to give other users guidance, too (acceptance is indicated by a colored checkmark next to the answer). C.f. ["Someone answers"](https://stackoverflow.com/help/someone-answers) – T.M. Oct 12 '21 at 16:16
  • Another good tip for the elderly, infrequent visitor! – Tony Lima Oct 12 '21 at 21:13
  • 1
    Addendum. Just learned Excel limits file name length to 218 characters including the full path. Guess we’re back to CP/M 8.3 names. Source: [https://support.microsoft.com/en-us/office/excel-specifications-and-limits-1672b34d-7043-467e-8e27-269d656771c3] – Tony Lima Oct 17 '21 at 19:20