#VALUE error when copying sheets

I´m using a UDF that is basically a vlookup simplified. Here´s the code:

Function SUELDOBASICO(Columna As Integer) As Double

SUELDOBASICO = Application.WorksheetFunction.VLookup(Application.Caller.Parent.Cells(Application.Caller.Row, 3), Application.Caller.Parent.Parent.Sheets("Escalas Salariales").Range("A3:DJ23"), Columna, False)

End Function

I´ve noticed that sometimes when copying sheets(within the same workbook), I get a #VALUE error. If I "edit" the cell in Excel, changing nothing, just using F2 and Enter, the error disappears. It used to happen when simply changing windows (to Firefox, and back to Excel, for instance). That´s why I used Caller and Parent so much in the code. It is almost completely fixed, except when copying sheets sometimes. I can´t seem to find the source of the error. Help please.

Solution

I know this isn't your exact question, but, if at all possible, I would suggest to just avoid VBA completely if that's at all an option and write your formula as follows:

=VLOOKUP(INDIRECT("C"&ROW()),'Escalas Salariales'!$A$3:$DJ$23,XXXXX,false)

and XXXXX can be the same as your Columna variable currently.

That would guarantee your code to work as needed.

Given what was discussed in the comments and trying my absolute best to ensure this works, I actually don't see anything wrong with your code and am just GUESSING it may have something to do with Application.Caller.

When this kind of thing happens to me, I try my best to just use the debugger to figure out why - That usually involves either Stop statements to be able to step into code and see what happened or Debug.Print Err.Description kind of messages.

Either way, I tried to break each part down, so, at the very least you can see where the issue comes from. To do so, I re-worked your function (with some major overkill)....

Function SUELDOBASICO(Columna As Integer) As Double

On Error GoTo ErrorCheck

Dim CellRef As Range
Dim LookupRef As Range

    Set CellRef = Cells(Application.Caller.Range("A1").Row, 3)
    Set LookupRef = Application.Caller.Worksheet.Parent.Sheets("Escalas Salariales").Range("A3:DJ23")

    SUELDOBASICO = Application.VLookup(CellRef, LookupRef, Columna, False)

    Exit Function

ErrorCheck:
    Stop
    Resume

End Function

(Also note that I changed Application.WorksheetFunction.VLookup to Application.VLookup - Look at this link for an explanation)

Once you figure it out, I would, though, remove the error code from the function as that isn't a good idea for production code - Just for Debugging.

Hopefully that can give you the answers you are looking for.

Hope that helps....

UPDATE #2:

Taking into account the possibility that copying the sheet is causing this error, here's a test to see if the process gets fixed:

Function SUELDOBASICO(Columna As Integer) As Double

On Error GoTo ErrorCheck

Dim NumTimesErrored As Integer
Dim StartTime As Double
Dim WaitSeconds As Integer
NumTimesErrored = 0

Dim CellRef As Range
Dim LookupRef As Range

    Set CellRef = Cells(Application.Caller.Range("A1").Row, 3)
    Set LookupRef = Application.Caller.Worksheet.Parent.Sheets("Escalas Salariales").Range("A3:DJ23")

    SUELDOBASICO = Application.VLookup(CellRef, LookupRef, Columna, False)

    Exit Function

ErrorCheck:
    ' This will make it tries this "hack" up to 3 times:
    If NumTimesErrored < 3 Then
        StartTime = Now
        WaitSeconds = 1 ' Wait one second
        Loop While Now - TimeStart < TimeSerial(0, 0, WaitSeconds)
            DoEvents ' Allows all the other processes to complete
        Loop
        ' Increment the number of times you've tried this:
        NumTimesErrored = NumTimesErrored + 1
        ' Go back to the calculation step that errored
        Resume
    End If

    Stop
    Resume

End Function