excel - VBA:我的程序抛出编译错误 ByRef

Question

我正在使用一个正在运行的程序。我制作了一个副本来测试使我的代码更加模块化。下面是循环内的一个子运行，通过调用第一个变为两个子运行。

Sub Trendline()
Dim eqn, name As String
Dim cht As ChartObject
Dim i As Integer
For Each cht in Worksheets(1).ChartObjects
    If cht.Chart.SeriesCollection(1).Trendlines.Count > 0 Then
        cht.Activate
        name = Split(ActiveChart.name)(1)
        i = Worksheets(name).Range("Z2").Value 'indicates what kind of trendline
        eqn = ActiveChart.SeriesCollection(1).Trendlines(1).DataLabel.Text
        'the trendline has both R-square and Equation displayed
        eqn = Split(eqn, Chr(10))(0)
        Worksheets(name).Range("AA1").Value = MakeEqn(i, eqn)
    End If
Next cht
End Sub

Function MakeEqn(i As Integer, eqn As String) As String
'1 is linear, 2 polynomial, 3 polynomial order 3
'4 is power, 5 exponential, 6 logarithmic
    eqn = Replace(eqn, "y = ", "")
If i = 6 Then ' removes 6 from options
    eqn = Replace(eqn, "ln", "*LN")
    'Break
Else
    eqn = Replace(eqn, "x", "*x")
    If i = 1 Then ' removes 1 from options
        'Break
    ElseIf i = 5 Then ' removes 5 from options
        eqn = Replace(eqn, "e", "*EXP(")
        eqn = eqn & ")" ' add ")" to end of string
        ' Break
    ElseIf i = 4 Then ' removes 4 from options
        eqn = Replace(eqn, "x", "x^")
        'Break
    Else ' for both 2 and 3
        eqn = Replace(eqn, "x2", "x^2") ' 2 is now done
        If i = 3 Then
            eqn = Replace(eqn, "x3", "x^3")
        End If
    End If
End If
MakeEqn = eqn
End Function

此处，对 MakeEqn 的调用中的“eqn”被突出显示，并引发以下编译错误。

我很沮丧，因为我将一个字符串传递给一个调用字符串的函数，但编译器声称存在类型不匹配。我应该在这里做什么？

Answer 1

在您的 TrendLine子程序，您已声明 eqn成为 Variant :

Dim eqn, name As String

在您的 MakeEqn函数，您期望收到(通过引用) String :

Function MakeEqn(i As Integer, eqn As String) As String

你不能通过 Variant到 ByRef String . (它将生成“ByRef 参数类型不匹配”错误。)

最简单的解决方法是声明 eqn成为 String在 TrendLine ， IE。

Dim eqn As String, name As String

或者，您可以传递变量 ByVal ，这将强制从 Variant 进行转换至 String :

Function MakeEqn(i As Integer, ByVal eqn As String) As String