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 /
UserForms.
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.