CODEWITHSUNDEEP
vbaexcel
Eladriol
Eladriol

Reputation: 99

Passing variants/arrays as arguments for a function

Example code;

Sub functiontester()

Dim testdata As Variant
Dim answer As Long
Dim result1 As Long

testdata = Sheets("worksheet1").Range("E1:E2").Value

result1 = testfunction(testdata)

result2 = testfunction2(testdata(2, 1))

End Sub

Function testfunction(stuff As Variant) As Long

    testfunction = stuff(2, 1)

End Function

Function testfunction2(num As Long) As Long

    testfunction2 = num

End Function

So from my days in python I'd expect result1 & result2 to both run fine, however this is not the case in VBA and if you try to run this you get

"compile error: Byref argument type mismatch" from result2; which I assume has something to do with limits of calculating values inside arguments of functions

So my question is: is there an easy way to make result2 work so that the variant reference just resolves to the specified element?

Upvotes: 1

Views: 1611

Answers (2)

RBarryYoung
RBarryYoung

Reputation: 56725

"compile error: Byref argument type mismatch" Actually refers to the fact it won't implicitly convert the datatype for you, because ByRef arguments (the default) are expected to be writeable. If converted arguments are written to , it gets lost when you return from the function/subroutine because the converted values are only temporary, they're not in any variable outside of the called function.

You can get around this complaint by making the receiving parameter ByValwhich means that it shouldn't be writable anyway:

Function testfunction2(ByVal num As Long) As Long

Upvotes: 2

Tim Williams
Tim Williams

Reputation: 166146

testdata(2, 1) likely will be of type Double, not Long.

You can use 

CLng(testdata(2, 1))

to cast it to a Long.

So:

result2 = testfunction2(CLng(testdata(2, 1)))

should be fine

Upvotes: 2

Related Questions