Array handling questions

[James McMurray]

Hi all, I'm using arrays in VBA for my first time, and I can't find a lot of info about them in the help files. I even had to accidentally stumble across UBound to find out the array's size. Does VBA have built in functions for:

push: add data to end of array
pop: remove data from end
unshift: data to front
shift: remove data from front
insert: add data in middle and push the rest out to fit
inArray: return the index where the data was found, if it's in the array

Also, is it possible to create a const array?

Thanks!

[Leith Ross]

Hello James McMurray,

Have a look at this site for a very thorough description of VBA arrays...

Excel VBA Basic Tutorial 3

To answer your questions, there is a Push and Pop function, though it isn't called that. You can expand the last dimension of a Dynamic array using the Redim statement, but only the last dimension. This doesn't apply to static (fixed) arrays.

Sincerely,
Leith Ross

[James McMurray]

Thanks! Looks like I'll have to make my own shift and unshift.

[James McMurray]

I also found this. No pushes, pops, or shifting, but some useful stuff.

Followup question: what am I doing wrong that the compiler won't let me treat an array created via split(string) as an array?

I've got:

sub main
dim arr
arr = split(somestring)
handleArray(arr)
end sub

function handlearray(inArray)
inArray(0) = something
end function

When I try to run it like that, I get a ByRef argument type mismatch at inArray(0). When I define inArray as an array instead, I get a "array or user defined type expected" error.

Is there a workaround that will let me pass the runtime-generated array through? Or perhaps a better of doing this?

Thanks!

[shg]

Please read our Forum Rules and then wrap your code with Code Tags.

[James McMurray]

Sorry about that. I couldn't find an edit button, so I'm reposting it.

Please Login or Register  to view this content.

[shg]

It's a little subtlety about passing variables by value versus by reference.

Run this and scratch your head:

Please Login or Register  to view this content.

Chip Pearson gives his usual lucid explanation at http://www.cpearson.com/excel/byrefbyval.aspx.

If you're just learning VBA, now would be a good time to start declaring all variables and function signatures explicitly.