VBA Global variable reset to 0 on start of subroutine

Question

I am writing a project which uses global variables. The global variables are declared at the start of the module, and all of the code is within the same module. The global variables (should) pass between each subroutine and, due to the nature of the worksheet, their values are declared in different subroutines or functions, depending on what is required.

My problem is that every time I call a subroutine, my Global variables are reset to 0, which obviously defeats the purpose of having a global variable.

Is there anything obvious that may be causing this? My code is rather large (1200+ lines) and it's not practical to post here.

The spreadsheet has shapes which are deleted and redrawn. Could this be re-compiling the sheet and causing all global variables to reset?

Thanks

Dan

EDIT: Variable Declaration

Public Type Blockwall
Asd As Single           'max area of reinforcement allowed
Ast As Single           'Area of reinforcement in design tension zone
Bar As Integer          'bar size
Capacity As Single      'Calculated capacity of wall usign Ast
cover As Single         'cover to reinforcement
d As Single             'Depth to centre of tension steel
Depth As Single         'Thickness of wall/footing
DesignMoment As Single  'Design Moment in base of wall
DL As Load              'Dead Load force
LL As Load              'Live Load Force
fc As Single            'Compressive strength of concrete/grout
fm  As Single           'compressive strength of masonry
fsy As Single           'Design stress of steel
Height As Single        'Total height of wall/Length of Footing (sorry it is confusing)
Height190 As Single     'Height of 190 blockwork
Height290 As Single     'Height of 290 blockwork
Moment25 As Single      'moment 25% from the top
Moment50 As Single      'Moment 50% from the top
Moment75 As Single      'Moment 75% from the top
Phi As Single           'Capacity reduction factor
Spacing As Single       'Bar Spacing
X As Single             'Distance of resultant vertical force (Rotation Check)
End Type

Dim Wall As Blockwall
Dim Footing As Blockwall

and snippet of subroutine where variable Footing.Depth is given a value (note that this is only one location where it is assigned a value):

Public Sub DrawWall(fLength As Single, fHeight As Single, kLength As Single, kHeight As Single, _
wHeight As Single, distToKey As Single, distToWall As Single, fBeta As Single, fPhi As Single, _
fDensity As Single, nBeta As Single, nPhi As Single, nDensity As Single, LL As Single, Height290 As Single)

'***---ASSIGN VALUES TO GLOBAL VARIABLES---***
Footing.Depth = fHeight
Footing.Height = fLength

The sub DrawWall is called by other subs to draw the required shapes. It doesn't seem to reset the values when DrawWall is called, only when I click on a button which calls a subroutine (or i start the subroutine from the code editing window.)

Your code is indeed large, but do you have any snippets that you can show? About 10 lines or 12. And your global variable. — Cyval
You have 1200 lines in a single VBA module using global variables and it has bugs. There are no suprises there. Have you considered rewriting in classes or without using global variables? This may not seem like a helpful comment but you are only going to have more and more issues if you don't rewrite that soon. You global variables are reset because somewhere in your code they are explicitly reset. Firstly.. do you have Option Explicit at the start? That would be a good start. — Nick.McDermaid
I suggest you look into a class this is a way of having logic and data defined in one piece of code that can be 'instantiated' as many times as you want. — Nick.McDermaid
When the routine (or caller) that creates those is finished your project will reset and your variables will lose state. — Rory
All variables will be reset by state loss. You need a routine to reload them. One of many reasons to avoid public variables as much as possible. — Rory

Dan W Dan W · Accepted Answer · 2016-01-04T22:28:52

It turns out that the creation and deletion of OLEObjects (used as input boxes) was causing the global variables to reset, once the subroutine containing those commands was complete. (A big thanks to @Rory for finding that one.) Unfortunately the watch window does not update the value until you start the next subroutine (I have no idea why). I will probably look into using classes instead of types so that the variables are stored regardless.

Thanks to everyone for your help!

Dan

VBA Global variable reset to 0 on start of subroutine

1 Answers