Step into udf

[tiredofit]

This is my udf:

Public Function MyFunction(ByRef a As Integer, ByRef b As Integer, ByRef c As Integer) As Integer

    MyFunction = a + b + c

End Function

In Excel, if I typed:

=MyFunction(1,2,3)

it returns 6, as expected.

If I put a break point in the function, it hits it.


However, if I typed:

=MyFunction(1,2,apples)

it DOES NOT hit the break point.

Is that normal behaviour?

 

[RoryA]

Yes, it is.

 

[tiredofit]

Yes, it is.

If you change something on a worksheet and put a break point at the start of:

Private Sub Worksheet_Change(ByVal Target As Range)

it will be hit, so with a udf, I expected the same behaviour, regardless of what valid or invalid parameters I pass to it.

 

[RoryA]

That's not a like for like comparison at all. If you called the UDF from a sub and passed invalid parameters, the type mismatch would occur in the calling sub, not in the UDF. That's what is happening in your current scenario; the error is raised in the Excel UI, not in the code.

 

[plettieri]

Hi tiredofit.

I'm not sure here but

It seems that you qualify your udf with integers...when it sees "apples" , a non integer, it does not satisfy the inputs. Should produce an error

plettieri

 

[tiredofit]

That's not a like for like comparison at all. If you called the UDF from a sub and passed invalid parameters, the type mismatch would occur in the calling sub, not in the UDF. That's what is happening in your current scenario; the error is raised in the Excel UI, not in the code.

I see, thanks for the explanation.

 

[tiredofit]

Hi tiredofit.

I'm not sure here but

It seems that you qualify your udf with integers...when it sees "apples" , a non integer, it does not satisfy the inputs. Should produce an error

plettieri

Yes, I purposely entered a string, thinking it'll still step into the code but it didn't.

 

[Scott Huish]

If that's really your scenario you would have to program the UDF for that contingency.

Public Function MyFunction(a, b, c)
Dim ctr As Long
If IsNumeric(a) Then ctr = ctr + 1
If IsNumeric(b) Then ctr = ctr + 1
If IsNumeric(c) Then ctr = ctr + 1
If ctr = 3 Then MyFunction = a + b + c Else MyFunction = "Invalid Input"
End Function

Book1
	A
1	6
Sheet1

Cell Formulas
Range	Formula
A1	A1	=MyFunction(1,2,3)

Book1
	A
1	Invalid Input
Sheet1

Cell Formulas
Range	Formula
A1	A1	=MyFunction(1,2,apples)

 

[tiredofit]

If that's really your scenario you would have to program the UDF for that contingency.

Public Function MyFunction(a, b, c)
Dim ctr
If IsNumeric(a) Then ctr = ctr + 1
If IsNumeric(b) Then ctr = ctr + 1
If IsNumeric(c) Then ctr = ctr + 1
If ctr = 3 Then MyFunction = a + b + c Else MyFunction = "Invalid Input"
End Function

Book1
	A
1	6
Sheet1

Cell Formulas
Range	Formula
A1	A1	=MyFunction(1,2,3)

Book1
	A
1	Invalid Input
Sheet1

Cell Formulas
Range	Formula
A1	A1	=MyFunction(1,2,apples)

Thanks, I was only testing when I came across this.

Won't need to test for non-integer inputs.