#NAME? Error in Excel VBA

Question

So I have something like this:

        A        B        C      ...
1       11       12       13
2       10       20       15
3       1        -8       -2
...

So A3, B3, and C3 is generated by subtracting A1 to A2 and so on.

If you look at the top of the sheet there is a long formula bar that shows the formula of a cell when you click on one. If you are filling in the sheet manually, you can type in that bar something like = A1 - A2 and it will fill A3 for you.

In my case, I am using .Formula = "IFERROR(A1 - A2, ""N/A""") in my code.

The problem I am having is that when the sheet is generated, instead of the desired output shown above, it is displaying something like this

       A        B        C      ...
1      11       12       13
2      10       20       15
3      #NAME?   #NAME?   #NAME?
...

If I click on the cell, the formula bar actually shows that it is apply the correct formula, and if I hit Enter after clicking on the formula bar, the correct numbers show up. So it's like I'm manually entering the formula.

This is my code. ConvertToLetter() takes in an integer and convert to column character.

Public Function ProcessExcelRpt(dataArray(,) As Object) As Integer
   Dim ws As Worksheet
   Dim r As Range

   Try
       For i As Integer = 1 To xlWorkBook.Sheets.Count
           ws = xlWorkBook.Sheets(i)

           r = ws.Range("A8")
           ws.Range("A8").Resize(dataArray.GetUpperBound(0) + 1, dataArray.GetUpperBound(1) + 1).Value2 = dataArray
           ws.Range("A2").Value2 = ws.Range("A2").Value2.ToString() & FormatDate(ReportDate, "MMMM dd, yyyy")

           FormatColumns(ws, 8, dataArray.GetUpperBound(0) + 8)
           excel.CalculateFull()

           xlWorkBook.SaveAs(saveAs)
           Exit For
       Next
   Catch ex As Exception    
        Return -1
   End Try  

   Return 0
End Function

Public Sub FormatColumns(ws As Worksheet, ByVal firstRow As Integer, ByVal lastRow As Integer)
    Dim rng As Range

    Try
        Dim colCnt, rowCnt, i As Integer
        i = 0
        For rowCnt = firstRow To lastRow
           Dim row1, row2 As Integer

           row1 = rowCnt - 2 ' go back 2 rows 
           row2 = rowCnt - 1 ' go back 1 row

           ' Apply formula to each cell in each row
           For colCnt = 1 To 3
               rng = ws.Range(ConvertToLetter(colCnt) & rowCnt) ' A1 for ex  
               rng.Formula = "=IFERROR(" & ConvertToLetter(colCnt) & row1 & "-" & ConvertToLetter(colCnt) & row2 & ", ""N/A"")"
           Next

       Next      
    Catch ex As Exception

    End Try
End Sub

Or try this `.Formula = "=IFERROR(" & Cells(1,colcnt).address(false,false) & " - " & Cells(2,colcnt).address(false,false)& ", ""N/A"")` — Scott Craner, Oct 30 '15 at 19:40
`worksheet` appears to be undefined... And is your code being run through a UDF `Function`? Or through a `Sub`? I would expect it to fail if run as a function. — David Zemens, Oct 30 '15 at 19:40
hard-coding the cells still gave me the problem. worksheet is declared As WorkSheet in another function, and this is a sub that takes worksheet as a parameter — PTN, Oct 30 '15 at 19:42
They are Integer. I am trying to trim my code so I don't copy and paste a huge block of code here. But I will post more. — PTN, Oct 30 '15 at 19:47
It looks like you're using s custom function `ConvertToLetter`, `CStr` has the same affect, this could maybe be the problem? — Alex Weber, Oct 30 '15 at 19:50
Well I tried hard-coding like what David did up there, and I still got the same problem — PTN, Oct 30 '15 at 19:51
@AlexWeber `cstr(1)` yields `"1"`. I think OP expects 1 --> "A" — David Zemens, Oct 30 '15 at 19:51
If you used [`FormulaR1C1`](http://stackoverflow.com/a/21689949/11683), you wouldn't need `ConvertToLetter`. Moreover, you would set the same constant formula to the entire range in one command, e.g. `Cells(firstRow, 1).Resize(lastRow - firstRow + 1, 3).FormulaR1C1 = "=R[-2]C-R[-1]C"`. — GSerg, Oct 30 '15 at 20:01

David Zemens · Answer 1 · 2015-10-30T19:54:12.190

1

If you run this as a Sub it is working for me:

Sub foo(firstRow, lastRow)
Dim rng As Range
For rowCnt = firstRow To lastRow
   Dim row1, row2 As Integer

   row1 = rowCnt - 2 ' go back 2 rows
   row2 = rowCnt - 1 ' go back 1 row

   ' Apply formula to each cell in each row
   For colCnt = 1 To 3
      Set rng = Cells(rowCnt, colCnt) ' A1 for ex
      rng.Formula = "=IFERROR(" & Cells(row1, colCnt).Address & "-" & Cells(row2, colCnt).Address & ", ""N/A"")"
   Next

Next
End Sub

If you are trying to execute this from a Function call as a UDF, it will not work because of explicit limitations on what UDFs can manipulate on the worksheet, specifically that you cannot:

Change another cell's value.

https://support.microsoft.com/en-us/kb/170787

edited Oct 30 '15 at 19:54

answered Oct 30 '15 at 19:50

David Zemens

53,033
11
81
130

I just ran the debugger and found that the sheet is empty when this sub is called. – PTN Oct 30 '15 at 19:53
The way my program works is that this is supposed to be a formatting function, and it will fill in the sheet with arrays of integer after this is called – PTN Oct 30 '15 at 19:54
1

It can't be done as a `Function` called from the worksheet, without some un-recommended workarounds. There are several Q&A's here about using Application.Evaluate to fudge your way through this limitation, but I would generally prefer to avoid doing any of that. – David Zemens Oct 30 '15 at 19:54

#NAME? Error in Excel VBA

1 Answers1