How to avoid value errors in VBA with UDFs

Question

I am currently trying to call the function written below, but I am getting a #VALUE error that I simply can't figure out. When I call the function in the sub below, I have no problems and I am able to return the values I am looking for. I am just confused and looking for any advice!! Thanks in advance!!!!

Function findNum(VehName As String, iqsNum As String) As Double
    Windows("Numbers Sheet.xlsx").Activate 'activates the sheet where we get data
    Dim SCol1 As Integer 'Dimension variables
    Dim SRow As Integer
    With Range("A3:A999") 'selects the range where we are looking

    Set PValue = .find(What:=iqsNum, LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False) 'looks for the value
    
    'goes through the sheet and grabs the location of the cell and makes it SRow and SCol
    If Not PValue Is Nothing Then
        Cell_Split_R = Split(PValue.Address(ReferenceStyle:=xlR1C1), "R")
        Cell_Split_C = Split(Cell_Split_R(1), "C")
        SRow = Cell_Split_C(0)
        SCol = Cell_Split_C(1)
        
    End If
    End With
    
    With Range("A3:ZZ3")

    Set PValue = .find(What:=VehName, LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)
    
    If Not PValue Is Nothing Then
        Cell_Split_R = Split(PValue.Address(ReferenceStyle:=xlR1C1), "R")
        Cell_Split_C = Split(Cell_Split_R(1), "C")
        SRow1 = Cell_Split_C(0)
        SCol1 = Cell_Split_C(1)
        
    End If
    End With
    Cells(SRow, SCol1).Select 'selects the cell we're looking at
    Cells(3, 30) = Cells(SRow, SCol1)
    findNum = Cells(SRow, SCol1)
End Function
Sub caller() 'using this sub to verify the code in findNum() actually works
    Dim Z As Double
    Z = findNum("BigName", 178)
    Windows("Analysis Sheet.xlsm").Activate
    Cells(3, 31) = Z
End Sub

UDFs shouldn't `.Select` or `.Activate`. [Avoid using Select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). — BigBen, Jul 26 '22 at 13:51
^^^ in fact when they are called by a cell formula they CANNOT `.Select` or `.Activate` or they will return `#Value` — Scott Craner, Jul 26 '22 at 13:52
They also cannot set a value to any other cell that is not the one being called. `Cells(3, 30) = Cells(SRow, SCol1)` is not allowed in a UDF that is called from a cell. — Scott Craner, Jul 26 '22 at 13:53
Tim Williams [already pointed out](https://stackoverflow.com/questions/73068295/vba-copy-paste-between-worksheets-with-a-function#comment129052985_73068295) the limitations of UDFs. — BigBen, Jul 26 '22 at 13:55
Ah, that took me way too long to figure out... It's an `=index(array,match(),match())` function. — Cameron Critchlow, Jul 26 '22 at 19:06

score 0 · Answer 1 · answered Jul 26 '22 at 19:18

0

Try Using "=Index(Array, Match(), Match())"

=INDEX($C$3:$M$16,MATCH(D19,B3:B16,0),MATCH(D20,C2:M2,0))

answered Jul 26 '22 at 19:18

Cameron Critchlow

1,814
1
4
14

How to avoid value errors in VBA with UDFs

1 Answers1