VBA - Including own function within Evaluate()

Question

In VBA, I am used to calculate values of a column, which is the function of another column, with Evaluate() in order not to use a loop. For instance, to assess column 2, whose values are the exponential of column 1's values, I would write (here limited to the first 10 rows):

Dim rng1 As Range
Dim rng2 As Range

Set rng1 = Worksheets("Sheet1").Range(Cells(1,1),Cells(10,1))
Set rng2 = Worksheets("Sheet1").Range(Cells(1,2),Cells(10,2))

rng2 = Evaluate("EXP(" & rng1.Address & ")")

I would now like to proceed on the same way with my own functions. In order to make a test, I created a function that returns the exponential value of the input:

Function TestExpo(alpha) As Double
    TestExpo = EXP(alpha)
End Function

and I proceeded in the same way as previously stated with Evaluate():

rng2 = Evaluate("TestExpo(" & rng1.Address & ")")

I did not get an error, but nothing happened on the Excel sheet. I tried to trigger an error by having a typing error in the function name for instance:

rng2 = Evaluate("TestEx(" & rng1.Address & ")")

and I actually got a "Name" error at each cell of rng2 on the sheet. Consequently, the calling function "TestExpo" works. I also tried to use a real number instead of rng1.Address:

rng2 = Evaluate("TestExpo(" & 2 & ")")

and I got the correct value of exp(2) in all cells of rng2 on the sheet, so calling the function and getting back the value in the cells works as such.

I tried to specify differently the type of the input and output. For instance:

Function TestExpo(alpha) ' No error, but no values on the sheet
Function TestExpo(alpha) As Range ' No error, but no values on the sheet
Function TestExpo(alpha) As Variant ' No error, but no values on the sheet
Function TestExpo(alpha As Range) ' No error, but no values on the sheet
Function TestExpo(alpha As Variant) ' No error, but no values on the sheet
Function TestExpo(alpha As Double) ' Value error for each cell of the range rng2

Except for the last test with alpha As Double, I did not get any error but also no values on the Excel sheet.

I am running out of ideas... I tried to find the code for the EXP() function of VBA to see how the input and output are specified, thinking that if I use the same types and arguments in the function "TestExpo", it may work, but I could not find the code of the EXP() function, it may not be public.

Would anyone have had a similar situation and solved it ?

Kind Regards Xavier

---

Update:

Thanks for your contributions! I moved on!

When I used, as suggested by @FaneDuru

rng2 = Evaluate("""=TestExpo(" & rng1.Address & ")""")

I got a Value error in each cell. Checking the cells in the worksheet, I could see that every cells got attributed the formula:

"=@TestExpo($A$1:$A$35)"

I compared with writing

rng2 = Evaluate("""=EXP(" & rng1.Address & ")""")

which worked and attributed to each cells the formula

"=EXP(@$A$1:$A$35)"

So the @ appeared at a different location. I manually corrected the formula "=@TestExpo($A$1:$A$35)" into "=TestExpo(@$A$1:$A$35)", and it worked !

So the problem is the location of @, and I do not understand why @ gets by default placed in front of the range within the brackets when the function EXP is used but in front of the function name when my own function is used.

Nevertheless, correcting the formula in the cells manually is not handy. Inspired by the proposal of @FaneDuru, I corrected my code by adding .Cells(1,1).Address(0,0) after the range name in the following way:

rng2 = Evaluate("""=TestExpo(" & rng1.Cells(1,1).Address(0,0) & ")""")

and this worked !

Now, the formula in the first cell looks like this:

"=@TestExpo(A1)"

And the cell address is adapted to each line (line 2 the formula is "=@TestExpo(A2)", line 3 "=@TestExpo(A3)", etc)

The @ still appears in front of the function name, but there is one cell name within the brackets instead of the range, and this makes the formula work.

Now I have to admit that I do not really understand how the code syntax works. In fact, adding .Cells(1,1).Address(0,0), I expected that all cells of rng2 would refer to the Cell(1,1) of the range rng1, but instead the formula got adapted to each cell in the proper way.

So, my initial problem has been solved using:

rng2 = Evaluate("""=TestExpo(" & rng1.Cells(1,1).Address(0,0) & ")""")

or even more simple:

rng2 = "=TestExpo(" & rng1.Cells(1,1).Address(0,0) & ")"

Nevertheless, I would be keen on understanding how the code syntax works - i.e. if the code explicitly refers to cell(1,1) in rng1.Cells(1,1).Address(0,0), how is it possible that all the cells of rng2 do not refer to cell(1,1) but refer to the proper cells (cell(2,1), cell(3,1), etc) ?

And I would also like to understand why @ gets by default placed in front of the range when the function EXP is used but in front of the function name when my own function is used. Would anyone have an idea ?

Answer 1

Unfortunately, I don't think you can do what you want in this way. I think that your example isn't doing what you expect.

rng2 = Evaluate("EXP(" & rng1.Address & ")")

Will return an array of values, but will only enter the first value into each of the cells in your rng2. So, even if you could mimic what EXP is doing, it still won't do what you want.

However, if you still want to mimic the (possibly incorrect) functionality you get with the EXP function:

The difference between EXP and your UDF is that EXP function can handle and return arrays, where as your UDF does not.

This is a very quick function that can (and should) be improved significantly, but it might give you a starting point.

Public Function test(a As Variant) As Variant
    Dim arr() As Double
    If IsNumeric(a) Then
        ReDim arr(1 To 1)
        arr(1) = Exp(a)
    Else
        ReDim arr(1 To a.Count)
        Dim i As Long
        For i = 1 To a.Count
            arr(i) = Exp(a(i))
        Next i
    End If
    test = arr
End Function

UPDATE:

Apparently, there also appears to be a difference between the EXP function used in a formula on a worksheet and the VBA version.

The VBA version only take a cell or value, whereas the worksheet version is able to accept a range/array of values. Normally, you could access the worksheet version using Application.WorksheetFunction, but unfortunately this doesn't seem to be available for EXP.

Answer 2

The function to be evaluated must be a string (between double quoted)...

Please, try

rng2 = Evaluate("""=TestEx(" & rng1.Address & ")""")

Edited:

Please, try the next (tested) example:

Function myFuncX(x As Long) As Long
   myFuncX = x + x * 2
End Function

And the testing sub evaluating it. Of course, you must feel "J2:J4" with appropriate long values...

Sub testEvaluateCustFunc()
    Dim rng As Range, rng2 As Range
    Set rng = Range("I2:I4"): Set rng2 = Range("J2:J4")
    rng.Value = Application.Evaluate("""=myFuncX(" & rng2.cells(1, 1).Address(0, 0) & ")""")
End Sub

Edited:

Please, try understanding what will happen in the next testing Sub. The method is able to evaluate formulas, names, ranges, objects and their properties, returning objects, values or arrays:

Sub testEvaluate()
  Dim rng As Range, rng2 As Range, testArr As Variant
  Set rng = Range("I2:I4"): Set rng2 = Range("J2:J4")

  'Application.Evaluate can simple be replaced by using []:
  testArr = [J2:J4]: Debug.Print Join(Application.Transpose(testArr), "||") 'Transpose is necessary to convert in 1D array
                                                                                                 'able to be shown in Immediate Window (IW)...
  testArr = Application.Evaluate("Index(" & rng2.Address(0, 0) & ",)")  'in this way an array of the range values is built
  Debug.Print Join(Application.Transpose(testArr), ",")                       'here the array can be visualized in IW
  testArr = Evaluate("Row(1:5)")                         'A nice way of making an array of numbers using the Row property
  Debug.Print Join(Application.Transpose(testArr), "|")  'See the resulted array in IW
  'the next line evaluates the function for all rng range, incrementing the function argument addresses:
  'I did not see your function and I used this way ONLY TO USE THE FIRST CELL IN THE RANGE TO BE INCREMENTED
  rng.Value = Application.Evaluate("""=myFuncX(" & rng2.cells(1, 1).Address(0, 0) & ")""")
  Debug.Print rng2.Address, rng2.Address(0, 0) ' See here the difference between absolute and relative address
                                                               'Only a relative address/reference can be incremented!
  'trying the absolute reference, will produce a wrong result, the formula using only the first cell of the range (absolute address):
  rng.Value = Application.Evaluate("""=myFuncX(" & rng2.cells(1, 1).Address & ")""")
  testArr = rng.Value
  Debug.Print Join(Application.Transpose(testArr), " ") 'the function is using only the first range cell
  'Now, the same thing using the first cell addres. Because I know what parametes the function uses...
  rng.Value = Application.Evaluate("""=myFuncX(" & Range("J2").Address(0, 0) & ")""")
  testArr = rng.Value
  Debug.Print Join(Application.Transpose(testArr), "|") 'if using the absolute address, the result will allways refer only
                                                                        'the first range cell, not being able to increment an absolute ref...
End Sub

If your function should accept a range of many cells, it could work as I initially suggested. For instance, using the next function:

Function myFunc2(rng As Range) As Long
   myFunc2 = rng.cells.count * 2 + Len(rng.cells(1, 1).Value)
End Function

You could use the next construction in a testing Sub:

Sub testmyFunc2()
    Dim rng3 As Range: Set rng3 = Range("F2:F4")
    Dim rng2 As Range: Set rng2 = Range("J2:J4")
    Debug.Print myFunc2(Range("J2:J4"))
    rng3.Value = Evaluate("""=myFunc2(" & rng2.Address & ")""")
End Sub

I hope it is a little clearer now... If still something foggy, do not hesitate to ask for clarifications. But punctually, on a specific aspect. Evaluate method is very complex regarding the ways to be used...