Excel 2010 VBA Tutorial 70 - Global Variables

Sharing buttons:

hello everybody and welcome to my 70th

VBA 2000 attend cheater and this

tutorial is going to show you how to use

global variables so a global variable is

pretty much the same as a normal

variable and the only difference being

that you can use it across any of your

modules or subs so it does have quite a

few uses but it can be a little bit

messy so you need to be careful with

them and I'd only use them if you can't

think of any other way of doing it you

normally better off using by ref to pass

from your variables by reference from

one side to another because then you can

control which subs can can have access

to it but this get started essentially

what we're going to do is I'm going to

start in just a normal module not a

class module and we're just going to

declare our variable say dim and then my

glue there and just make it string we

they can be any type of object so you

can do it with workbooks you can do it

with ranges you can do it with you

wherever you want integers doubles I can

list all the objects but I'm not going

to use and if you do want to see all the

objects you can go to the object browser

by pressing f2 and it will give you a

list of all the objects that you can use

which is useful I know let's carry on

before I get sidetracked by an object


let's go sub and then my first sub and

so notice how our global variable is

being declared outside of our sub but

what we can do is we can still actually

access this variable from inside our sub

so that's per my global var equals

hello world and then that's cool my

second sub which we haven't created yet

but we're gonna leave in a minute say

let's just copy that down here so sub my

second sub and then down here I'm just

going to message box out the global

variables so my valuable bar and so what

this is going to do is we're going to

set the global variable up here we're

then going to cool our second sub it's

going to go into our second sub and then

it's going to have the global variable

so let's press play and you notice it

comes up blank because somewhere I'm

online I must do start I think we Google

my Google for my Hipple bar let's play

hello world okay so one of them must be

touch one say play hello Wells cool so

that's working now so we're assigning

hello world and this sub to the global

variable cool in my second sub and then

in the second sub we're using the same

variable despite the fact that we've set

the value in a different sub normally if

we took this out and put it in there

what we'd get is that blank one because

it's creating the variable and the sub

so this sub down here doesn't have scope

on this variable so it just creates a

side and a sunset blank value but if we

have it outside as we did before and

press play and it's going to say hello

world and one thing you can do which is

useful is if you put our option explicit

then it's still going to run at the

moment but let's say you did what I did

at the start and you typed it wrong say

put an extra one up there press play

it's going to say very

not defined so won't actually do what it

was doing and just run it as a blank new

variable it forces you to have a

variable to find which if we have it as

the global one up there

press play it's going to run because

we've defined it already quite a lot of

people put this in all their case I know

your ID and it just forces you to create

your variables and gets rid of any

issues like that way your you think you

are using one variable but actually it's

creating a new one and that is it for

global variables we're going to use it

in the coming tutorials

when we're going to look at the data

objects to create a global connection to

our database so all our subsequent

connect to the database on the same

connection rather than creating separate

ones or passing it around all over the


so that is it for this tutorial thanks

for listening and I hope to capture in

the next tutorial I'll finally going to

get on to active data objects