VBA Array Question

[IvenBach]

I have an interesting occurrence. I noticed in the Locals window that when my variable is assigned as an array it changes it to TEST(0) and then lists the position after IE TEST(0)(x). I haven't seen this before and didn't see anything come up with this when I did some browsing. Any reason for this?

Sub oenuthoetnuh()
Dim TEST As Variant

On Error GoTo endd:

    For Each r In Selection
        TEST = Array(Split(r.Value2, ","))
        For k = 0 To 100
            ActiveWorkbook.FollowHyperlink "http://www.website.com/" & TEST(0)(k)
        Next
    Next
endd:
    MsgBox "Verify Time", vbOKOnly, "TITLE"
End Sub

I intended this to be used on a selection that would go through and open a webpage for the information I'm seeking. It works most of the way I want but haven't come across the addition (0) for arrays before. I'm assuming that it may have something to do with Split or the way I have things setup, and we all know what assuming does.

 

[Rick Rothstein]

        TEST = Array(Split(r.Value2, ","))

The Split function already produces an array as its output, so you don't need the Array function call (which is just creating another array and assigning the array from the Split function to the first (and only) element of that unneeded array. Change your line to this...

TEST = Split(r.Value2, ",")

and then you can address the elements in TEST normally, that is, TEST(k) for you loop.

Just for information sake, the extension to what you did originally is equivalent to something like this...

TEST = Array(Array(1, 2, 3), Array(4, 5, 6), Array(7, 8, 9), Array(10, 11, 12))

The outer Array function call has 4 elements each of which is a separate array of elements in and of themselves. So the syntax you use is shaped like this...

TEST(outer array element)(array element of the selected array)

So, to return the 6 from the above example, you would use this...

GetThe6 = TEST(1)(2)

or to get the 10, you would use this...

GetThe10 = TEST(3)(0)

Note that Array function makes use of the Option Base setting which, for the above examples was assumed to be 0.

 

[shg]

Note that Array function makes use of the Option Base setting

If you use VBA.Array, you'll always get a zero-based array.

 

[IvenBach]

I thought it would be something similar. Removed the Split and works as intended.

Thanks for showing me the array(array(...),array(...)) bit of information. That will most certainly come in handy. Didn't realize you were able to do that.

Where is it again that I declare the base with proper syntax? I read on MSDN and it seems like it changes the starting # from 0 to 1, right? Is that just for personal preference or can that have a specific change programatically? Always asking the dumb question so bear with me

 

[shg]

Option Base (1 or 0) at the top of a module.

But it's better practice to declare array bounds explicitly:

Dim myArray(1 to 10, -3 to 12, 0 to 5)

 

[IvenBach]

I'd seen the declaration of variables but hadn't completely understood why. Starting to fit the pieces together now. +1 to shg and Rick both. Thanks for your help.

 

[Rick Rothstein]

If you use VBA.Array, you'll always get a zero-based array.

Well I'll be.... I just learned something new today! I've been programming in VB since VB2 (the compiled version), so that is quite a number of years now, and I never knew specifying the VBA library directly forces the Array function to be zero-based no matter the setting of the Option Base. It never occured to me to try something like that as I simply assumed the Option Base always won out. Thanks for posting that... I really appreciate it.

 

[shg]

I have no idea where I learned that, Rick, or whether it's an example of some reasonably general rule. I don't use Option Base, so the only time I use VBA.Array (when I remember) is in posting code for someone else to use.

 

[Rick Rothstein]

I have no idea where I learned that, Rick, or whether it's an example of some reasonably general rule. I don't use Option Base, so the only time I use VBA.Array (when I remember) is in posting code for someone else to use.

I don't use Option Base either, but what I have done in the past when answering questions is use the Split function for creating arrays as that is always zero-based no matter the Option Base (and without having to prefix the VBA library reference). Now, thanks to you, I can also use VBA.Array if using the Array function would be "cleaner". Oh, and I just tested this out in the compiled version of VB6 and VBA.Array is always zero-based there as well.

I've been a Microsoft MVP (first for VB and more recently for Excel) since 2000 or 2001 (not sure which any more), so I have posted thousands upon thousands of messages in the old newsgroup and various forums... in all that time, and with all the times I have mentioned my Option Base caution, no one ever pointed out your little tidbit to me. I'm thinking you were the only person who knew about it... before now, that is.