3

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

Dan W
  • 133
  • 1
  • 13
  • 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 Jan 04 '16 at 05:36
  • Is it in a standard code module or is it in a sheet module? – John Coleman Jan 04 '16 at 05:41
  • Cyval - original post edited. John C. - it is in a standard module. – Dan W Jan 04 '16 at 05:50
  • 3
    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.Mc Jan 04 '16 at 06:02
  • [This](http://stackoverflow.com/questions/7041138/what-is-the-lifetime-of-a-global-variable-in-excel-vba) and [this](http://stackoverflow.com/questions/19618421/excel-vba-global-variables-lifetime) might help. Also [this](http://stackoverflow.com/questions/23917977/alternatives-to-public-variables-in-vba) – Ioannis Jan 04 '16 at 06:03
  • Thanks @Nick. I have considered rewriting in separate modules, however I want to create additional modules (about 5) which will be of similar size and I find it easier having each Calc set in one module, which I can then navigate using the drop down menus at the top of the window. It will also allow me to isolate some variables to that module, which may be of future use to me. – Dan W Jan 04 '16 at 06:08
  • You could use the Watch Window to monitor the global variables – John Coleman Jan 04 '16 at 06:08
  • 2
    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.Mc Jan 04 '16 at 06:11
  • Looking at your code it is almost out of a textbook example for using classes. If you have on global variable and a multitude of subs that can arbitrarily change it you're going to run into trouble. Or you could follow other advice her and use the watch window to see when variables change – Nick.Mc Jan 04 '16 at 06:13
  • Thanks @Ioannis. I found one of them in my search however it didn't help. I will look at the others. @Nick, I will look further into classes - I use them to generate shapes at the moment, but not to generate/store variable values. I like the `Type` variables as they AutoComplete as I write my code. Also, I have Option Explicit at the start of my code. – Dan W Jan 04 '16 at 06:24
  • @John, I have tried using the Watch Window, and it shows the variables resetting to 0 when I call a subroutine. – Dan W Jan 04 '16 at 06:26
  • Yes - Types are great. Classes are just types that have methods (functions) in them as well, so not only can you set all the parameters just like a type, you can call a method (function) to calculate or display it based on what you previously set – Nick.Mc Jan 04 '16 at 06:27
  • Is the 'setting to zero' a new issue or has it never worked? I suspect that's just the way modules work. – Nick.Mc Jan 04 '16 at 06:27
  • Try to create the smallest complete example you can which mimics your current code and use, but involves maybe only a single global and maybe a single Sub. Do you still see the same problem? If no then what is different in that example? If Yes, then post the full example here so others can review it. – Tim Williams Jan 04 '16 at 06:42
  • Are you deleting and creating ActiveX objects? That would cause what you describe. – Rory Jan 04 '16 at 07:00
  • Thanks @Tim. I have built smaller examples but they all work. They are _much_ smaller though, without all the shapes. I have tried to trace it but watching the variable doesn't show where in the code it changes value - even setting the watch to break when variable changes doesn't pinpoint it. – Dan W Jan 04 '16 at 22:00
  • If you can share a workbook with non-working code then that might help - otherwise it's difficult to guess what the problem might be. – Tim Williams Jan 04 '16 at 22:04
  • @Nick, I find the Get/Let functions of classes less intuitive than using a Type (as well as liking the AutoComplete ability of Types). That said, I do have some functions that could be inserted into a class module, with a bit of editing, so I may end up doing it that way. – Dan W Jan 04 '16 at 22:06
  • @Rory, I am not creating or deleting ActiveX objects, but I am creating and deleting OLEObjects (input boxes). The subroutine that those processes are in does not have the issue of resetting global variables though, so I don;t think it is that (could be related, though I'm not sure how). – Dan W Jan 04 '16 at 22:06
  • 1
    When the routine (or caller) that creates those is finished your project will reset and your variables will lose state. – Rory Jan 04 '16 at 22:08
  • Thanks @Rory, in the watch window the values remain until I start a new subroutine. I will comment out those subs/functions and see if the problem still occurs. – Dan W Jan 04 '16 at 22:19
  • @Rory, Commenting out that routine seems to fix it. I need to create those shapes so I will declare the variables globally (so I don't have to declare them again each routine) or rewrite it using classes. Thanks for your help! – Dan W Jan 04 '16 at 22:25
  • You'll lose all globals so you'll need to create the objects then use Ontime to run a routine that resets any public variables you need. Or rewrite to use functions rather than variables. – Rory Jan 04 '16 at 22:27
  • @Rory, could I create local variables that store the variables then re-assign the values once the objects have been re-created? – Dan W Jan 04 '16 at 22:30
  • 1
    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 Jan 04 '16 at 22:49

1 Answers1

2

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

Dan W
  • 133
  • 1
  • 13
  • If Global variables are getting reset, then *all* variables (of all types) will get reset, global or non-global. Switching to Classes/Objects will not help here. – Tim Williams Jan 05 '16 at 00:00
  • Thanks @Tim. Any suggestions around this problem? I am not stuck with using OLEObjects (I could use text boxes or something) however OLEObjects are the only ones I know of which don't have little handles pop up every time you click on them. I could try a userform with a similar intent, however I want the OLEO's to be easily editable and viewable without extra clicks every time you wish to change something. Also, it would require me to rewrite pretty much everything :-( – Dan W Jan 05 '16 at 00:32
  • Hard to say what a good work-around might be without knowing more about exactly what you need those text boxes for. For example, how many do you need and could you have them already populated on the sheet but not visible until they're needed? Do they need to raise events? Etc. – Tim Williams Jan 05 '16 at 01:04
  • Here's a picture http://i57.photobucket.com/albums/g239/lawolski/wall_zpsv3ccchms.jpg it shows the boxes as inputs. The VBA code reads those values and either redraws the pictures, or calculates things relating to the wall. – Dan W Jan 05 '16 at 01:12
  • Why do you need to add/remove the text boxes at run-time? Do you have multiple sheets you need to create? – Tim Williams Jan 05 '16 at 01:22
  • When the picture redraws the boxes move - their location changes, so I could make it so that they just move to the new location, though that is a fair bit of extra coding. Thinking about it more I might write a function that reads all the values off the sheet and just call it in each routine. That will hopefully avoid any other variable 'resets'. – Dan W Jan 05 '16 at 01:54
  • Moving the text boxes will still be easier than trying to work around the problem with having your whole VB project get reset when you add/remove them... – Tim Williams Jan 05 '16 at 01:56
  • As the values remain in the boxes it might be easier to just call the values back on the start of a subroutine. I know it's not the best solution, but it might be the easiest, as the drawing part of the code works well (and I don't want to stuff it up). – Dan W Jan 05 '16 at 02:03