MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Storing an array in memory?

  Asked By: Dora    Date: Aug 17    Category: MS Office    Views: 2218

Suppose I want to write a complicated VBA program with many
subroutines. I want to use a 10x10 array of integers. I want to be
able to access this array from any subroutine. What is the best way
to define this array? It seems like a global would work, but I have
been told this is bad form.

Should I define my array in my top level routine and then pass it to
every routine that may use it? In this case, how do I pass it back up
to the calling routine, once I have changed elements in the array?



6 Answers Found

Answer #1    Answered By: Freda Lane     Answered On: Aug 17

Not sure why it would be bad  form, since it sounds like "global" is
the very definition of what you want the array  to be...

Answer #2    Answered By: Hooriya Khan     Answered On: Aug 17

Although globals are a bad  idea as a general point, they are sometimes still

If your 10x10 array  is central to your processing, then by all means make it
global  array.

If you prefer, you can pass  a reference to it (ByRef) to subroutines. This
means there is still only one copy of the array for the subroutines to
change. However, having all routines work  on the same chunk of memory  via
parameters is no more structured than having them work on a global array.

If you are making it an array because it is indeed a 10x10 structured piece
of data, then that's fine. If you are arbitrarily grouping unrelated data
into an array, then you should consider a different way of holding the
information. (You haven't indicated why 10x10, so we don't know.)

It is unusual to want to access  all of your data from all of your
subroutines. Usually each subroutine  will only access a portion of your
information, to do its job. That information would normally then be held
local to the module that holds the subroutine.

Answer #3    Answered By: Adalia Fischer     Answered On: Aug 17

Over the last 2-3 years, I have written several very, very complex
Excel workbooks that do serial I/O to control my ham radios. Each
has 2-4 sheets, 10-12 modules holding 1-15 procedures / functions /
I ONLY USE GLOBAL (PUBLIC) VARIABLES [well, 98%]. I know what each
is and never get confused what's what as I would if I used locals. I
have many, many variables that do a lot of traveling (used in many
procedures). I don't have to figure out which is which in regard to
passing by reference or value. I understand the concepts, but linking
the term to the type escapes me and using them is another thing. If
it works for you, don't worry -- if you understand truly how it works..
The reading I did on-line about scope and lifetime helped GREATLY.
Then I did some test routines to try this and that...and
GET THIS. Because serial I/O has some complex consequences when
the code crashes from an error, I've even resorted to using a cell in
a spreadsheet to hold a variable quantity so I know absolutely that I
can get to it if the code errors and I recover. [[a variable for the
serial port handle gets destroyed by a system call and I need it to
close an open port or I have to quit Excel and re-launch.]] I do other
things for this as well.

My mind works this way and I keep out of trouble.

*** IF *** You really want to do what you talk about below, you need
to understanding passing variables By Reference and By Value. One
will actually change the values as you want and the other only sends a
copy to the routine. Also getting vars into, then back  out of a
procedure will be easy to understand. I'm sure there is an MSDN or
support.microsoft topic describing this. I found it and should have a
printout in my 14 inch high stack of VBA tutorials.

Answer #4    Answered By: Tracy Myers     Answered On: Aug 17

' That should be

Application.FlameretardantEnabled = True

Answer #5    Answered By: Vonda Ramirez     Answered On: Aug 17

Global variables are not a good thing. If you do some work  on proper
structuring of modules within Excel you would find that your code becomes
SIGNIFICANTLY easier to debug and maintain.

HOWEVER ... don't try to convert existing code that works. That's seldom a
good idea, as it is just work expended with no gain. Try proper structuring
with the next complex spreadsheet you create.

Passing by reference or by value is really a totally separate question.
Your global  variables can be changed  by any piece of code anywhere in your
program. This is somewhat akin to passing all of them by reference to each
sub and function.

I'm not sure how you could ever find local variables more confusing than
global variables. Two reasons spring to mind: (1) the live right next to
the code the belong to, so no long-term memory  is needed to remember what
they're used for; (2) you KNOW that they aren't being used by any other part
of your code, they're just not visible there.

I firmly recommend a read of some simple Object-Oriented programming
introductions. Although Excel VBA is not object oriented as such, it offers
a compartmentalising structure (i.e. the modules with public/private
procedures and variables) and the ability to create complex objects (the
Class modules) that make it easy to write  complex - but stable and trusted -

Now, there is a difference between amateur and professional programming. A
complex spreadsheet that I am working on at the moment has 89 user forms and
25 other modules, and accesses a Sybase database via more than 130 stored
procedures. It has literally thousands of variables, of which only about 30
are global.

Answer #6    Answered By: Bach-yen Nguyen     Answered On: Aug 17

I won't go into a long justification (yea, right), but it works
for me. There may be advantages for locals, but for me, having one
variable name which has a one-to-one correspondence with same value
makes it easy for me to keep everything in mind. If I am debugging
and wonder what a variable is at any point, I know its name/symbol,
and a quick Debug.Print lets me know. Of course I can use the same
symbol for the local, but that seems to only complicate it for me.

I don't think I have a problem with unplanned variable changes.
To be honest, my biggest problem is not yielding to the lure of going
straight to the keyboard.
My standard [espoused] technique is to flow-chart (one page) the
routine first and get that working for the universe of inputs
expected, and unexpected. The coding then becomes only an exercise in
typo correction. The flow chart also makes debugging, enhancements
and maintenance a snap. Once the lower level  routines are
bullet-proof, I get closer to a "main" program  which looks more like a
list of subroutines (and the flowchart).

Most time, for me, is spent designing the error handling -
deciding what the bad  inputs can be, which I want to handle and what
the user can do that will mess things up. { "You can't make it idiot
proof, because idiots are too damned ingenious."} I also develop a
test regime and run it through the ringer.
I believe the biggest area time-wise, should be spent addressing
the "User Hostility Factor".

I get OO basics, but my mind still thinks in linear programming.
Coming from the old school [assembler, Basic], I still have a hard
time with VB / OO *in practice*. The object model is significantly
different and VBA has it's own confusion factors. Planning is the key.

I *was* trying to steer the neophyte OP to reading up on the basics on
scope and variables.

Didn't find what you were looking for? Find more on Storing an array in memory? Or get search suggestion and latest updates.