1

OK so I found a sort of fix for this problem on here but it wasn't in VBA so I just need the right syntax to pluck info from the array.

I've defined an array from a range on a sheet. I've figured out that this isn't actually a 1D Array as I'd first thought even though the 2nd dimension parameter is just 1.

Now I'm just trying to cycle through the array to help me get my head round how they work and I get a subscript out of range error.

Dim arr1 As Variant
Dim e As Variant

    arr1 = Array(ActiveSheet.Range("A1:A4"))

For e = LBound(arr1) To UBound(arr1)
    MsgBox (arr1(e, 1))
Next e

How can I fix the MsgBox (arr1(e,1)) line?

Improve this question

2 Answers 2

Reset to default
2

There were a couple problems:

  1. You do not need the Array(..) when assigning a range to an array
  2. You should specify the dimension on the Ubound and Lbound

So here:

Dim arr1 As Variant
Dim e As Long

    arr1 = ActiveSheet.Range("A1:A4").Value

For e = LBound(arr1,1) To UBound(arr1,1)
    MsgBox arr1(e, 1)
Next e
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Ok that makes total sense except for the last part, I've been () with MsgBox since I started to learn - not sure WHERE I learned that but it's stuck. Why is this bad practice and does it translate to VB.NET? Oh and for point 2) is that necessary or just good practice.
vba and vb.net are different, so I am not sure. but as I learned it not to use () unless you use = or Call. But it seems to work either way.
I might look in to that. That's interesting to me. I always go for `MsgBox ("Hey!" & var) - and I think I've done that for so long it looks neater to me now but maybe I'll get used to otherwise.
2

being a real 1-D array you could go this way:

Option Explicit

Sub main()
    Dim arr1 As Variant
    Dim e As Long

    arr1 = Application.Transpose(ActiveSheet.Range("A1:A4").value) '<--| transposing a 1-column range you get a 1-row range that fits in an actual 1-D array        
    For e = LBound(arr1) To UBound(arr1) <--| no need to specify the column index
        MsgBox arr1(e)
    Next e
End Sub

as for iterating through the array, you may want to use the For Each syntax:

Option Explicit

Sub main()
    Dim arr1 As Variant, elem As Variant

    arr1 = Application.Transpose(ActiveSheet.Range("A1:A4").value)            
    For Each elem In arr1
        MsgBox elem
    Next elem    
End Sub
Improve this answer

5 Comments

Thanks for the answer - I was using for each at one point but with lbound and ubound I can use the e as a counter which comes in handy rather than elem.position type. As for the first point, if that works then that's great. I'd rather use 1D Arrays where possible.
you are welcome. if you'll use one of my solution you may want to accept my answer. thank you
I would have to pick the other guy's as the correct answer to the question I'm afraid, even though you showed me how to get a real 1D Array he did fix the original code. I did upvote, shame I can't pick both answers :D
Just an FYI: Transpose has its limits. If the range is greater than 65,000 pieces (or there about), Transpose will not work, and you will need to loop to put it in a one dimensional array.
Good to know, I don't think it'll be an issue but I am about to take on some new projects at work and it might crop up.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.