CODEWITHSUNDEEP
arraysvbaexcelpass-by-reference
Absinthe
Absinthe

Reputation: 3391

Array argument must be ByRef

What am I doing wrong here?

Sub Main()

Dim patients() As String

' Some code to populate the patients array, works fine

CalculateScores (patients) ' Array argument must be ByRef compile error

End Sub



Sub CalculateScores(patients As String)

End Sub

If I change patients to a variant array in Main and the parameters of CalculateScores it works fine but I can't see the logic of not being able to pass a string. By default it is ByRef so I know I'm missing something.

I can use a variant sure, but it feels hacky.

Upvotes: 2

Views: 2162

Answers (1)

Mathieu Guindon
Mathieu Guindon

Reputation: 71247

When you do this:

DoSomething (expression)

You're forcing expression to be evaluated as a value, and passed ByVal, regardless of whether the parameter explicitly says it's passed ByRef. While that has more or less no impact most of the time, it bites you in the rear end when you try to pass an array or an object reference.

Drop the parentheses.

DoSomething expression

Now, there are other problems with your code: You're passing an array of strings into a String parameter; that can't work. Make the parameter an array or a Variant, and I'd suggest marking the parameter explicitly as ByRef, for clarity.

Upvotes: 3

Related Questions