0

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.

Bond
  • 16,071
  • 6
  • 30
  • 53
Santiago Settecasi
  • 28
  • 7
  • 1
    My gut says that when you copy sheets, perhaps you are changing workbooks? So that above, you refer to Sheets("Escalas Salariales").Range... but really you should say Workbooks("WorkbookName.xlsx").Sheets("Escalas... – Grade 'Eh' Bacon Aug 06 '15 at 14:29
  • I´m not copying to another workbook. It´s within the same workbook. – Santiago Settecasi Aug 06 '15 at 14:45

2 Answers2

1

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
John Bustos
  • 19,036
  • 17
  • 89
  • 151
  • Thanks, as I mentioned in another answer, I´m trying to make the formula shorter, that´s why I made the UDF. – Santiago Settecasi Aug 06 '15 at 14:40
  • @SantiagoSettecasi, Did you look at / consider Grade Eh Bacon's comment - That makes the most amount of sense to me.... If that's not the case let me know and I'll look into posting a different solution.... – John Bustos Aug 06 '15 at 14:42
  • I have considered it. I´m copying the sheet to the same workbook, not a different one. – Santiago Settecasi Aug 06 '15 at 14:47
  • @SantiagoSettecasi, I edited my answer with something new that may at least help point you in the right direction as to why this is happening - Hope it helps.... – John Bustos Aug 06 '15 at 15:16
  • Thanks. I must admit that I know very little about VBA, but I´m Trying to learn. I added the line Debug.Print Err.Description before the Stop instruction. What I get in the inmediate window is "subindex out of interval". What could that be? – Santiago Settecasi Aug 06 '15 at 16:37
  • @SantiagoSettecasi, try using `Application.VLookup` rather than `Application.WorksheetFunction.VLookup` - It may fix the probem completely, or, at the very least, it will give you a more logical error - I'm going to update my code to that too as that makes more sense anyways..... – John Bustos Aug 06 '15 at 17:07
  • I´ve tried everything you suggest, but still no solution. I´ve been able to identify where the problem lies: If I add the line `Debug.Print Application.Caller.Worksheet.Parent.Sheets(2).Range("A3:DJ23").Count` after the `set CellRef` line, I get the count when It´s working properly, and just an empty string when I copy the sheet. If I were to edit the cell with the UDF and press enter again, everything works ok and I get the count, as mentioned earlier. Any new suggestions? Thank you very much. – Santiago Settecasi Aug 06 '15 at 20:20
  • The easiest solution I can think of is **if you are going to always have this function in the same workbook that calls it**, you no longer need the `Application.Caller.Worksheet.Parent` part which I'm sure is causing the issue - You can replace it with `ThisWorkbook` or just nothing... Let me know and we'll move forward from there.... – John Bustos Aug 07 '15 at 13:00
  • Unfortunately I´m not. The UDF is in a module of an add-in. I use the UDF in multiple workbooks. – Santiago Settecasi Aug 07 '15 at 13:07
  • I´d like to add that I´ve also tried adding the line `Debug.Print Application.Caller.Worksheet.Parent.Sheets(2).Name` and it works correctly until that point, giving "ESCALAS SALARIALES". It seems that the `Range` part has some minor problem when copying sheets. – Santiago Settecasi Aug 07 '15 at 13:12
  • Ok, I **THINK** I know what your problem is, then.... In copying over the worksheet, it's probably trying to re-calculate the cells before the copy is complete and, therefore, messing up the it's messing up the `Application.Caller` portion... I'll come up with a "hack" to test this and let me know if it works.... – John Bustos Aug 07 '15 at 13:38
  • I think I´m having the same problem as in [this post](http://stackoverflow.com/questions/6470263/excel-is-calculating-a-formula-with-a-vba-function-as-an-error-unless-it-is-re-e?rq=1). However, I don´t know how to apply a solution to my particular problem. What do you think? – Santiago Settecasi Aug 07 '15 at 14:46
  • Yup, same issue - It's what I commented on above... Busy today, but will post an update to my answer above.... – John Bustos Aug 07 '15 at 14:53
  • @SantiagoSettecasi, I posted up new code - I didn't test it because I'm pretty busy today, but it should work for testing the hypothesis... Give it a try. – John Bustos Aug 07 '15 at 15:01
  • Ok, a few comments: 1 - I think there was a small error in the code. I had to change `Loop While` for `Do While`. 2 - Unfortunately it´s not fixing the problem. The code keeps reaching the line `Stop`. 3 - I tried changing `NumTimesErrored < 3` and `WaitSeconds = 1`to 10 and 60 and I keep reaching `Stop`. – Santiago Settecasi Aug 07 '15 at 15:52
  • Well, I´ve tried a lot of things and nothing worked. Luckily the las thing I tried was adding `Application.Volatile` and THAT somehow seemed to do the trick. I don´t know why but it works. I´m leaving this here for anyone with the same problem, or for someone to explain to me why this happens. Thanks! – Santiago Settecasi Aug 10 '15 at 14:40
0

The is no need to use caller and parent.

Function SUELDOBASICO(Cell as Range, LookupRange as range, Columna As Integer) As Double

' When you call the function :
' set Cell to be  the cell in column C in the same row

' Set LookupRange to Sheets("Escalas Salariales").Range("$A$3:$DJ$23")

SUELDOBASICO =     Application.WorksheetFunction.VLookup(Cell, LookupRange, Columna, False)
End Function

example of formula in a cell...

=SUELDOBASICO(C10,'Escalas Salariales'!$A$3:$DJ$23)

HarveyFrench
  • 4,440
  • 4
  • 20
  • 36
  • Thanks. Actually I made the UDF with only one variable to be able to type it fast. There´s really just one variable here(Columna). I know I could use a name for the range but still, I´m disinclined to use that for the same reason. Also, I´m curious as to why this error occurs. – Santiago Settecasi Aug 06 '15 at 14:39