CODEWITHSUNDEEP
excelvbaargumentsuser-defined-functions
user3641311
user3641311

Reputation: 185

How to pass as argument cells operation instead of single cell in Excel VBA UDF?

I've created a UDF to calculate a nonlinear equation which considers 3 arguments (A1, B1 and C1). The results comes in D1 (=SLV(A1; B1; C1)).

What I am trying to do is to give a operation between cells instead of A1, B1 or C1. As example: instead of A1, I would like to write G1/H1^(K1*10^-4).

My UDF function is given below:

Function SLV(ByVal a, b, c As Range) As Single

    Dim det, root1, root2, rootf As Single

    <several operations>

    SLV = result

End Function

This results in error #VALUE.

Many thanks in advance.

Upvotes: 0

Views: 80

Answers (1)

BigBen
BigBen

Reputation: 50162

1: You need to specify the type in variable declaration, otherwise the variables are actually Variants: Dim det as Single, root1 as Single, root2 as Single.... Though As Double would be preferred here for more precision.

2: Since the arguments are no longer Ranges, change the types:

Function SLV(ByVal a As Double, ByVal b As Double, ByVal c As Double) As Double

    Dim det As Double, root1 As Double, root2 As Double, rootf As Double, result As Double

    <several operations>

    SLV = result

End Function

Upvotes: 1

Related Questions