excel - 在VBA函数中计算二变量函数的值

Question

我有一个函数f(x,y) = e^(x+y)并在 VBA 中像这样运行:

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

有没有办法重写此代码以使用任何函数，如 f(x,y) = x+y ETC。？

Answer 1

我将您的问题解释为如何使数学函数成为函数 test 的输入参数.在VBA中没有很好的方法可以做到这一点，但是你可以做的是在代码模块中定义函数然后使用Application.Run从 test 内部按名称调用函数.
概念证明:

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

请注意，您不需要命名被调用函数 f ，所以 test可以使用多种功能。不幸的是，VBA 缺少匿名函数的概念，因此您仍然需要将函数定义为 VBA 函数(或编写自己的解析器)。