How to test for a cverror condition returned from one UDF to another

[JenniferMurphy]

I have a little UDF that will convert a column number into a column letter:

Public Function CvtColNum2Ltr(ByVal ColNum As Integer) As String
Const MaxCol As Long = 16384   'Numerical value of maximum column (XFD).
          
If ColNum > MaxCol Then                 'If column number if too large,
  CvtColNum2Ltr = CVErr(xlErrValue)    'Return a value error
  Exit Function
End If

CvtColNum2Ltr = Split(Cells(1, ColNum).Address, "$")(1)
End Function

The UDF is in one of my personal addins.

If I call this UDF from a worksheet, it works perfectly. But if I call it from another UDF that is called from that same worksheet, I get "Compile error: Sub or Function not defined".

What am I doing wrong?

 

[dmt32]

Does your function reside in a STANDARD module?

Dave

 

[JenniferMurphy]

Does your function reside in a STANDARD module?

The CvtColNum2Ltr function is in an add-in module that is not attached to any worksheet. It is my understanding that that makes it a standard module.

The function that calls is, PCTally, is in Module1, a module that is attached to the Readings workbook. It is my understanding that that is not a standard module.

If I call CvtColNum2Ltr from sheet1 in the Readings workbook, it works. But if I call it from PCTally, which is called from that same sheet in that same workbook, it gets that error.

Did I answer your question?

 

[mikerickson]

1) You have CvtColNum2Ltr defined as a string function, but for large column numbers, the CvtColNum2Ltr = CVErr(xlErrValue) line gives a mismatch error when called from VBA. Excel's worksheet UDF handler masks that by giving a #Val error. The function should be a variant type if you want error values returned.

2) You say that the Not Found error occurs when you use CvtColNum2Ltr in a different UDF. Could you post the code for that UDF please?

 

[JenniferMurphy]

1) You have CvtColNum2Ltr defined as a string function, but for large column numbers, the CvtColNum2Ltr = CVErr(xlErrValue) line gives a mismatch error when called from VBA. Excel's worksheet UDF handler masks that by giving a #Val error. The function should be a variant type if you want error values returned.

That makes sense. I should have thought of that. Here's the corrected code:

Public Function CvtColNum2Ltr(ByVal ColNum As Integer) As Variant
Const MaxCol As Long = 16384   'Numerical value of maximum column (XFD).

If ColNum > MaxCol Then    'If column number if too large,
   CvtColNum2Ltr = CVErr(xlErrValue)   'Return a value error
   Exit Function
End If

CvtColNum2Ltr = Split(Cells(1, ColNum).Address, "$")(1)

End Function

2) You say that the Not Found error occurs when you use CvtColNum2Ltr in a different UDF. Could you post the code for that UDF please?

The UDF that calls CvtColNum2Ltr is quite long. Here's the relevant parts excerpted:

Public Function PCTally(pAMRange As Range, pAMSkip As Range) As String

Dim OU as string
OU = CvtColNum2Ltr(27)

End Function

 

[mikerickson]

Actually, I don't think those are the relevant parts. I would guess that somewhere in PCTally, there is a mis-spelling, or a missing character or a something to trigger the not found error, not just in the lines refering to CvtColNum2Ltr.

 

[JenniferMurphy]

Actually, I don't think those are the relevant parts. I would guess that somewhere in PCTally, there is a mis-spelling, or a missing character or a something to trigger the not found error, not just in the lines refering to CvtColNum2Ltr.

I simplified the code and it still fails.

Here's a simplified version of PCTally. It resides in a workbook code module that contains several other UDFs. It still gets the same "Sub or Function not defined" error. (The error message doesn't say "Not Found".) All of the other UDFs in that code module work and this one works, too, if I remove the call to CvtColNum2Ltr. I had been using it for months, but decided to add code so it could report the addre3ss of a cell containing invalid data.
[/CODE]
Public Function PCTTest() As String
MsgBox CvtColNum2Ltr(27)
PCTTest = "Done"
End Function
[/CODE]

And here's the CvtColNum2Ltr code again. It resides in an add-in module with dozens of other UDFs that all work.

Public Function CvtColNum2Ltr(ByVal ColNum As Integer) As Variant

Const MaxCol As Long = 16384 'Numerical value of maximum column (XFD).

If ColNum > MaxCol Then 'If column number if too large,
   CvtColNum2Ltr = CVErr(xlErrValue) 'Return a value error
   Exit Function
End If

CvtColNum2Ltr = Split(Cells(1, ColNum).Address, "$")(1)

End Function

I also tried moving PCTTest to the same add-in module with CvtColNum2Ltr. It then gets a Name error as if the worksheet cannot find it. But the same sheet can execute other UDFs in that add-in module.