How to debug an UDF?

[Martin]

hi All,


I've written this User Defined Formula, to extract a string from within another, delimited by underscores, e.g. AB_CDE-1234-56-7_89_FGH, and the result is CDE-1234-56-7.


here's the code (it's in Spanish, but it makes no difference)


Function extraecontrato(celda As range)


Dim InicioCadena, FinCadena As Double, Cadena As String


InicioCadena = application.WorksheetFunction.Find("_", celda.Value, 1) + 1

FinCadena = application.WorksheetFunction.Find("_", celda.Value, InicioCadena)

Cadena = Mid(celda.Value, InicioCadena, FinCadena - InicioCadena)

If IsError(InicioCadena) Then Cadena = celda.Value


extraecontrato = Cadena


End Function


My problem is that when there is no underscore, that is, when an error is returned, the IF sentence does not work, and as a result, I get a #VALUE on my spreadsheet.


any suggestions?


Thanks !!!


P.S: I've tried to debug the UDF with breakpoints and F8, but it doesn't seem to work...

 

[keymaster]

@Martin..


You can add "on error resume next" line to your code or even write "on error goto" to handle such errors. Just google for these to learn more about these or read datapigs http://datapigtechnologies.com/blog/index.php/are-goto-statements-still-dirty/

 

[JP]

If there's no underscore, then InicioCadena will equal 1. To be sure, change the UDF so it starts out at zero and only add one to it if it isn't equal to zero. Otherwise, exit with a simple error message.


Function extraecontrato(celda As range)


Dim InicioCadena, FinCadena As Double, Cadena As String


InicioCadena = application.WorksheetFunction.Find("_", celda.Value, 1)


If InicioCadena > 0 Then

InicioCadena = InicioCadena + 1

Else

extraecontrato = "Error"

Exit Function

End If


FinCadena = application.WorksheetFunction.Find("_", celda.Value, InicioCadena)

Cadena = Mid(celda.Value, InicioCadena, FinCadena - InicioCadena)


If IsError(InicioCadena) Then Cadena = celda.Value


extraecontrato = Cadena


End Function

 

[Martin]

@Chandoo: I've read the post, and found nothing that I can use.


@JP: thanks for the code !. IT works the same, and still cannot debug line-by-line the code when error. And the "error" sentence does not appear.


For instance, I have "Client1" as a value in a cell, and when applying the UDF, returns a #VALUE error. What I want is that, in case of an error, just return the whole .value of the target cell.


Thanks !

 

[Hui]

Martin


Try using the following which uses "InStr" instead of application.WorksheetFunction.Find


===========


Function extraecontrato(celda As Range)


Dim InicioCadena, FinCadena As Double, Cadena As String


InicioCadena = InStr(1, celda.Value, "_")


If InicioCadena > 0 Then

InicioCadena = InicioCadena + 1

Else

extraecontrato = "Error"

Exit Function

End If


FinCadena = InStr(InicioCadena, celda.Value, "_")


Cadena = Mid(celda.Value, InicioCadena, FinCadena - InicioCadena)


If IsError(InicioCadena) Then Cadena = celda.Value


extraecontrato = Cadena


End Function

 

[JP]

You can step through this function. Just set a breakpoint on the first line by clicking anywhere in the first line and pressing F9. Then enter the function in a cell. You should immediately jump to that line. Then press F8 repeatedly to step through the function and see where it errors.


You might also have to declare celda As String in order for the function to work properly.

 

[Martin]

@Hui: Thanks, worked as a charm !!!


I've done a small addition after the calc of FinCadena, as I still got a #VALUE error, for those cells where there was only a single "_":


If FinCadena = 0 Then FinCadena = Len(celda.Value)


In that way, I always get a positive length of the string.


@JP: don't know why, but this time the breakpoint did work. Thanks !