CODEWITHSUNDEEP
vbaexcel
Dan W
Dan W

Reputation: 131

VBA Global variable reset to 0 on start of subroutine

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

Upvotes: 3

Views: 2145

Answers (1)

Dan W
Dan W

Reputation: 131

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

Upvotes: 2

Related Questions