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 -
code.

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.