By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,084 Members | 1,956 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,084 IT Pros & Developers. It's quick & easy.

Are arrays in VBA stale, or I'm wrong?

P: n/a
Hi.

I have a question on arrays in VBA. I'm a C++/C# programmer and didn't
expected arrays in VBA to be so helpless, or maybe it only seems to me
that they are helpless.

For example in C++ we can easily create a dynamic array using
stl::vector, add elements at every position, remove elements at every
position and it will automatically organize memory for us. How can such
things be done in VBA?

Here is an example:

vector<intv; // Create a vector
for (int i = 1; i <= 10; ++i)
v.push_back(i); // Add the value of 'i' at the end of the vector

v.erase(v.begin()+4); // Remove the 5-th element from the vector

etc, etc...

So, how can these be realized in VBA? Especially how to remove an item
from the middle of the array?

Thanks in advance

Martin

Aug 18 '06 #1
Share this Question
Share on Google+
8 Replies


P: n/a
No can do. Which is why VBA is free and C# is $500.

You can re-read the array into another using a Where clause that
excludes the 5th element, but you can't just make it disappear.

martin-g wrote:
Hi.

I have a question on arrays in VBA. I'm a C++/C# programmer and didn't
expected arrays in VBA to be so helpless, or maybe it only seems to me
that they are helpless.

For example in C++ we can easily create a dynamic array using
stl::vector, add elements at every position, remove elements at every
position and it will automatically organize memory for us. How can such
things be done in VBA?

Here is an example:

vector<intv; // Create a vector
for (int i = 1; i <= 10; ++i)
v.push_back(i); // Add the value of 'i' at the end of the vector

v.erase(v.begin()+4); // Remove the 5-th element from the vector

etc, etc...

So, how can these be realized in VBA? Especially how to remove an item
from the middle of the array?

Thanks in advance

Martin
Aug 18 '06 #2

P: n/a
You've got two ways of doing it if the array is a dynamic array and one if
it's a static one.

Dynamic and static
--------------------
Create another array 1 element smaller
Read through the source array assigning values to the target array skipping
he relevant element.

Dynamic Array
-----------------
Read through the array from skipposition +1 to the end assigning each
element to the one below it
Then use ReDim Preserve to redimension the array to the new size

Ther are other ways of doing it but they involve direct memory manipulation.

--

Terry Kreft
"martin-g" <ma******@mail.ruwrote in message
news:11**********************@74g2000cwt.googlegro ups.com...
Hi.

I have a question on arrays in VBA. I'm a C++/C# programmer and didn't
expected arrays in VBA to be so helpless, or maybe it only seems to me
that they are helpless.

For example in C++ we can easily create a dynamic array using
stl::vector, add elements at every position, remove elements at every
position and it will automatically organize memory for us. How can such
things be done in VBA?

Here is an example:

vector<intv; // Create a vector
for (int i = 1; i <= 10; ++i)
v.push_back(i); // Add the value of 'i' at the end of the vector

v.erase(v.begin()+4); // Remove the 5-th element from the vector

etc, etc...

So, how can these be realized in VBA? Especially how to remove an item
from the middle of the array?

Thanks in advance

Martin

Aug 18 '06 #3

P: n/a
You rarealy will need to build, or use an array in ms-access.
(they really are a thow back to the old days of fortan, and looping code
anway).

Since the data has to come from somewhere..then that is uselaly a table, and
a reocrset object is MUCH better dynainc object then a array. and, if you
modfy the data in the array, how will you get it back into the table?

However, if you do need a handy dandy list of data in memory, consider using
a colleciton...

Dim colData As New Collection
Dim i As Integer

For i = 1 To 10
colData.Add i
Next i

colData.Remove (5)

' diplays data in colleciton

For i = 1 To colData.Count
Debug.Print colData(i)
Next i

If you use an array, then remove a entry in the middle, you have to move
each element down, and then re-dim preserve to re-size the list (and drop
the end element off) with the new dimensions.

As I mentioned, I am HARD pressed to remember the last time I used a array.

You have go through a bit of a mind set change, but arrays for the most part
are not much use in ms-access anyway.
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl*****************@msn.com
Aug 18 '06 #4

P: n/a
martin-g wrote:
Hi.

I have a question on arrays in VBA. I'm a C++/C# programmer and didn't
expected arrays in VBA to be so helpless, or maybe it only seems to me
that they are helpless.

For example in C++ we can easily create a dynamic array using
stl::vector, add elements at every position, remove elements at every
position and it will automatically organize memory for us. How can such
things be done in VBA?

Here is an example:

vector<intv; // Create a vector
for (int i = 1; i <= 10; ++i)
v.push_back(i); // Add the value of 'i' at the end of the vector

v.erase(v.begin()+4); // Remove the 5-th element from the vector

etc, etc...

So, how can these be realized in VBA? Especially how to remove an item
from the middle of the array?
Arrays perform faster in VBA than Collections. I'd recommend create a Class to wrap the
array object and to add the methods you require. I'm sure a Google will turn up code you
can use as a base.

Check out the Redim and Redim Preserve statements in VB(A).

--
'---------------
'John Mishefske
'---------------
Aug 19 '06 #5

P: n/a
Again...with spell check!!1

You rarely will need to build, or use an array in ms-access.
(they really are a throw back to the old days of Fortran, and looping code
anyway).

Since the data has to come from somewhere..then that is usually a table, and
a reocrset object is MUCH better dynamic object then a array. and, if you
modify the data in the array, how will you get it back into the table?

However, if you do need a handy dandy list of data in memory, consider using
a collection...

Dim colData As New Collection
Dim i As Integer

For i = 1 To 10
colData.Add i
Next i

colData.Remove (5)

' diplays data in colleciton

For i = 1 To colData.Count
Debug.Print colData(i)
Next i

If you use an array, then remove a entry in the middle, you have to move
each element down, and then re-dim preserve to re-size the list (and drop
the end element off) with the new dimensions.

As I mentioned, I am HARD pressed to remember the last time I used a array.

You have go through a bit of a mind set change, but arrays for the most part
are not much use in ms-access anyway.
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl*****************@msn.com

Aug 19 '06 #6

P: n/a
So arrays in VBA are really helpless as I supposed. Thanks everybody
for help. I think I'll use 'Collection', I liked the way it's working.
Thank you, Albert.

Martin

Aug 19 '06 #7

P: n/a
Baz

"martin-g" <ma******@mail.ruwrote in message
news:11**********************@m73g2000cwd.googlegr oups.com...
So arrays in VBA are really helpless as I supposed. Thanks everybody
for help. I think I'll use 'Collection', I liked the way it's working.
Thank you, Albert.

Martin
They're not helpless, they do have their uses, particularly in conjunction
with the very useful Split function. They are a bit lame, though. As has
been mentioned elsewhere, collections are a bit on the slow side but, with
the horsepower we all have on our desktops, who really cares about that?
Aug 19 '06 #8

P: n/a
"martin-g" <ma******@mail.ruwrote
So arrays in VBA are really helpless as I supposed.
Thanks everybody for help. I think I'll use 'Collection',
I liked the way it's working.
With few exceptions, I have found other ways to handle most needs in Access
than using arrays. When I have used them, not expecting them to be something
they aren't (like identical to the ones you are used to in C#), I have
certainly not found them "helpless."

I have found that it is very easy to create useful business database
applications (single user, multi-user, and client-server) in Access: create
a reasonably-relational database design, point and click your way to a
user-friendly interface, and sprinkle just enough VBA code behind it to make
it work as I want, and work smoothly.

I can have an application solving business problems while I'd still be
organizing my UseCases in "more professional" development environments like
..NET.

"You pays your money and takes your choices."

Larry Linson
Microsoft Access MVP
Aug 21 '06 #9

This discussion thread is closed

Replies have been disabled for this discussion.