vba variant array bug

Question

I am fairly new to excel vba and I can't seem to fix this problem with vbArrays. I created the function cumsum in vba just to make my life easier. However, I want to make the code flexible such that I can pass in both variants from a function and also a range. In my code, when I added the line vec=vec.value if I am passing in a range, it works perfectly fine but it doesn't work if I want it to work if I call the function and pass in a non range type. What I noticed was if I didn't have the line vec=vec.value in my code and I pass in a range, it has dimension 0 and I checked by writing my own function. Can someone please explain to me how I can fix this problem? Thanks.

Public Function cumsum(vec As Variant) As Variant
    Dim temp() As Variant
    MsgBox (getDimension(vec))
    'works if i use vec=vec.value if vec is a range but has 0 if i do not vec = vec.values
    ReDim temp(LBound(vec, 1) To UBound(vec, 1), 1 To 1) As Variant
    Dim intCounter As Integer
    For intCounter = LBound(vec) To UBound(vec)
        If intCounter = LBound(vec) Then
            temp(intCounter, 1) = vec(intCounter, 1)
        Else
            temp(intCounter, 1) = temp(intCounter - 1, 1) + vec(intCounter, 1)
        End If
    Next
    cumsum = temp()
End Function
Function getDimension(var As Variant) As Integer
On Error GoTo Err:
    Dim i As Integer
    Dim tmp As Integer
    i = 0
    Do While True:
        i = i + 1
        tmp = UBound(var, i)
    Loop
Err:
    getDimension = i - 1
End Function

Jake · Answer 1 · 2012-07-20T17:14:23.197

2

Why don't you just check the data type of vec by using VarType and TypeName then perform the necessary manipulation on vec

Public Function cumsum2(vec As Variant) As Variant
    MsgBox TypeName(vec)
    MsgBox VarType(vec)

    cumsum2 = 0
End Function

edited Jul 20 '12 at 17:14

answered Jul 20 '12 at 16:05

Jake

11,273
21
90
147

i tried. when you vartype the range, it still thinks it is an vbarray and same for variants as well – Andrew Jul 20 '12 at 16:35
What happens when you use TypeName? – Jake Jul 20 '12 at 16:38
1

Can you update your question to show how you are passing values into cumsum()? – Jake Jul 20 '12 at 17:00
also tried calling the function from vba dim trial as variant redim trial(1 to 10) as variant dim i as integer for i = 1 to 10 trial(i) = i next cumsum(trial) – Andrew Jul 20 '12 at 17:01
1

Please see my updated answer. Are you checking the types like I did? – Jake Jul 20 '12 at 17:15

score 1 · Accepted Answer · edited May 23 '17 at 11:48

The answers from @Jake and @chris are hints in the right direction, but I don't think they go far enough.

If you are absolutely sure that you'll only ever call this routine as a UDF (i.e. from formulas in your worksheets), then all you really need to do is add this:

If IsObject(vec) Then
    Debug.Assert TypeOf vec Is Range

    vec = vec.Value2
End If

to the start of your function. Called as a UDF, the only object type it should ever get passed is Range. Also, called as a UDF, you can rely on the fact that any arrays it gets passed will be indexed starting from 1.

I could pick out other problems with your routine, but they would be beside the point of your original question. Briefly: this will only work on column vectors, it will fail for single-cell ranges, etc.

Note that the reason your getDimension function is returning zero for Ranges because UBound is choking on the range. Your error handler happily catches an error (type mismatch) you didn't really expect to get and returning zero. (That method of finding "dimension" is assuming the error will be a subscript out range error.)

I wrote an answer a while back describing why, when working with Excel, I don't think the general getDimension approach is a good one:

https://stackoverflow.com/a/6904433/58845

Finally, the issue with VarType is that, when passed an object that has a default property, it will actually return the type of the property. So VarType(<range>) is going to tell you the type of the stuff in the range, not the code for object, because Range has a default property, Range.Value.

score 0 · Answer 3 · answered Jul 20 '12 at 22:11

0

Modify your getDimension to include

If TypeName(var) = "Range" Then
    var = var.Value
End If

answered Jul 20 '12 at 22:11

chris neilsen

52,446
10
84
123

vba variant array bug

3 Answers3