• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to debug an UDF?

Martin

New Member
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

New Member
@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

New Member
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

New Member
@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

Excel Ninja
Staff member
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

New Member
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

New Member
@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 !
 
Top