Calculating value of two-variable function in VBA function

Question

I have a function f(x,y) = e^(x+y) and function in VBA like this:

Function Test(n As Integer, x0 As Double, xk As Double, y0 As Double, yk As Double) As Double()

Dim i As Integer
Dim j As Integer
Dim Tablica() As Double
ReDim Tablica(0 To n, 0 To n)
For i = 0 To n
    For j = 0 To n
        Tablica(j, i) = Exp(x0 + i * (xk - x0) / n + y0 + j * (yk - y0) / n)
    Next j
Next i
Test = Tablica

End Function

Is there any way to rewrite this code to work with any function like f(x,y) = x+y etc.?

Not quite clear. Are you asking how to use a function as an input to another function? — John Coleman
– John Coleman, Commented Dec 10, 2020 at 12:52

John Coleman · Accepted Answer · 2020-12-10 13:19:01Z

I interpret your question as how to make the mathematical function an input parameter to the function test. There is no very good way to do this in VBA, but what you can do is to define the function in the code module and then use Application.Run to invoke the function by name from inside test.

Proof of concept:

Function test(f As String, n As Integer, x0 As Double, xk As Double, y0 As Double, yk As Double) As Double()
    Dim i As Long, j As Long
    Dim Tablica() As Double
    ReDim Tablica(0 To n, 0 To n)
    
    For i = 0 To n
        For j = 0 To n
            Tablica(j, i) = Application.Run(f, x0 + i * (xk - x0) / n, y0 + j * (yk - y0) / n)
        Next j
    Next i
    test = Tablica
End Function

'used like:

Function g(x As Double, y As Double) As Double
    g = x - y
End Function

Sub test2()
    Range("A1:D4").Value = test("g", 3, 0, 1, 3, 4)
End Sub

Note that you don't need to name the called function f, so that test can work with multiple functions. Unfortunately, VBA lacks a notion of anonymous functions, so you still need to defined your functions as VBA functions (or write your own parser).

Collectives™ on Stack Overflow

Calculating value of two-variable function in VBA function

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related