Search code examples
excelvbacompiler-errorsbyref

VBA: my program is throwing compile error ByRef

I'm working with a program that was running. I made a copy from which to test making my code more modular. Below is what used to be one sub run inside a loop changed to two subs run by a call to the first.

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

Here, the "eqn" in the call to MakeEqn is highlighted and it throws the following Compile Error. Compile Error: ByRef

I'm frustrated because I'm passing a string into a function that calls for a string, but the compiler claims there is a type mismatch. What should I be doing here?

Solution

  • In your TrendLine subroutine, you have declared eqn to be a Variant:

    Dim eqn, name As String

    In your MakeEqn function, you are expecting to receive (by reference) a String:

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

    You cannot pass a Variant to a ByRef String. (It will generate a "ByRef argument type mismatch" error.)

    The easiest fix is to declare eqn to be a String in TrendLine, i.e.

    Dim eqn As String, name As String

    Alternatively, you could pass the variable ByVal, which would force a conversion from Variant to String:

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