VBA application-defined error when assigning new value in array

Question

I am trying to write a function that takes in a variant array (2-dimensions), then goes through it and changes all dates to longs.

Private Function mlDateHelper(a As Variant)
    Dim i, j As Integer
    For i = 1 To UBound(a.value, 1)
        For j = 1 To UBound(a.value, 2)
            If IsDate(a(i, j)) Then
                a(i, j) = CLng(a(i, j)) 'This line errors out
            End If
        Next j
    Next i
    mlDateHelper = a
End Function

Basically, I'm just iterating through the array on both dimensions, and whenever there's a date I'm trying to reset that date to a long.

When I'm debugging it, I can tell that it correctly computes CLng(a(i, j)), but then it crashes when it's trying to assign that value to a(i, j).

It's giving me an "application-defined or object-defined error". Thoughts? Thanks for your help!

Did you try adding a break point and seeing if the value you're trying to convert to a long is convertible to a long? Can you verify the program is executing up to that point? — Pete Garafano
– Pete Garafano, Commented Apr 4, 2013 at 18:43
If you don't get an error on UBound(a.value, 2) then a is probably still a Range and so you need to say a(i, j).Value = CLng(a(i, j)). It depends whether you pass a Range to the function or assign the range to a variant first. — grahamj42
– grahamj42, Commented Apr 4, 2013 at 18:59

Floris · Accepted Answer · 2013-04-04 19:07:57Z

1

If a is always a range, you should probably take advantage of that fact:

Private Function mlDateHelper(a As Range)
Dim c As Cell
For each c in a.Cells
    c.Value = CLng(c.Value)
Next
End Function

answered Apr 4, 2013 at 19:07

Floris

46.6k7 gold badges73 silver badges128 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

ron tornambe · Accepted Answer · 2013-04-04 18:57:16Z

0

I used MS-Access 2007 VBA to test this by removing the .value from the UBound statements as it would not compile in Access. I hope this works for you as it does for me (I had no problem with the assignment passing an array defined as Dim arr(1 To 5, 1 To 7) to the following function:

Private Function mlDateHelper(a As Variant)
    Dim i, j As Integer
    For i = 1 To UBound(a, 1)
        For j = 1 To UBound(a, 2)
            If IsDate(a(i, j)) Then
                a(i, j) = CLng(a(i, j)) 'This line errors out
            End If
        Next j
    Next i
    mlDateHelper = a
End Function

answered Apr 4, 2013 at 18:57

ron tornambe

10.8k8 gold badges38 silver badges61 bronze badges

Comments

Alec · Accepted Answer · 2013-04-04 19:02:57Z

0

Thanks to whoever was looking into my question.

I actually discovered that I don't need this function because I can simply use the a.Value2 property for the Variant, which does not attempt to convert dates into dates.

According to documentation I found: "The only difference between this property and the Value property is that the Value2 property doesn’t use the Currency and Date data types. You can return values formatted with these data types as floating-point numbers by using the Double data type."

Thanks for helping though!

answered Apr 4, 2013 at 19:02

Alec

6635 gold badges9 silver badges22 bronze badges

Collectives™ on Stack Overflow

VBA application-defined error when assigning new value in array

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related