Is tere a way to reset all public variables in a module. I have a number of sub routines withing the same module and need to carry a variable from one to another so I have decalared tham as public variables.
This works fine except for an instance when I expect the variable to be empty (because nothing has yet been assigned to it) when in fact it contains the value that was assigned to last time another sub was run. So I suppose what I need is a way to reset all the variables in the module when a particular sub ends
What i have at the moment is a module that contains code where i call a different module that i use as a procedure.
Code: If Sheet1.Range("C4").Value < Sheet1.Range("A2").Value Then If Sheet1.Range("K4") = "" Then MsgBox "Please check 06:00 tasks not done yet!" Cell = "Range(" & Chr(34) & "F4" & Chr(34) & ")" If Sheet1.Range("C4") + 0.042 < Sheet1.Range("A2") Then Run "EmailProSheet" End If End If End If
EmailProSheet is what i call but now i want to use the variable "Cell" in the procedure as well?
Code: MsgBox Cell Set OutApp = CreateObject("Outlook.Application") Set OutMail = OutApp.CreateItem(0) On Error Resume Next
As it is now everything is working fine but does not return a value in "Cell" if the procedure is called. Is there another way?
I am trying to use a public variable but it is not holding the value when I call another sub.
Here is basically what I have:
Public AcctName as Long
Sub Statments() There is much more code in here but it is not relevent AcctName = an account name lets say "frank"
If X XX then Call Title End If
It then goes to the title sub and executes the code however it is supposed to write out the value held in acctname but it comes up blank instead of as Frank which is the value it held when it left the statments sub. I am new to this public thing and need any help I can get. Just a note: the spelling is correct and the variable does hold the correct value before title sub is called.
I've recently been experiencing some strange phenomena in relation to public variables. It seems that if I run separate routines in quick enough succession (e.g. press a button twice within a couple of seconds), the memory of the public variable "hangs over" into the second routine.
The routines, however, are completely separate events. And I'm not using userforms or anything like that in order to keep VBA running in the background. In which case it seems that Public variables don't always get set to nothing with the end of the final sub in a routine. Has anyone else experienced anything like this? I was under the impression only Static variables could do this.
I'm programming with multiple workbooks. So public variables in one workbook often reference ranges in another. Anyhow, I have found that, when closing a workbook, unless I nullify all public variables that reference this workbook, it closes in excel but remains visible in the VB Editor. When I open the workbook again, another instance of its VB project appears in the VB Editor..and so on and so on, which is obviously pretty annoying..
I've worked out a way of nullifying (setting equal to nothing) all public variables in a workbook, but it's quite arduous and I haven't tried it yet.. basically, it involves running a code which runs through the procedure which sets these public variables (they are all set in a single procedure), then having the code write a new subroutine setting all variables to nothing, then running this subroutine..
In my VBA project I've declared several public variables as normal (ahead of all procedures) ... Public simolo() As double etc. They are declared in normal modules, and arent declared twice. I set the values in one procedure and then execute a second procedure but, when the variable is encountered in the second procedure, it appears to be empty and I get a "Subscript out of range" error. Clearly, the public variable isn't public since no data is stored in it. What is going on?
I am trying to set public variables from an event handling procedure based in a worksheet so I can use that variable in a userform. Nothing I have tried works no matter where I declare the variable. I am using a msgbox to display the variable (a range) but it shows as blank regardless of whether I place the variable in a module, this workbook object or in the sheet object where the event code is placed.
I am sure there is a simple way to transfer variables from the sheet's code (where it must remain as the variable depends on the target cell's position that triggers the event).
I am having trouble defining a variable in a For loop. When I run the loop the first time everything seems to work fine, but when the loop goes back through the second time it carries its previous value with it. Here in lies the problem if the variable is supposed to be empty (not assigned a value). So, for example: 'Variable' in the code below may or may not be given a value depending on the conditions in the For i loop. If it is given a value then 'Variable' will still have that value on the next pass through the For j loop. And if none of the conditions are met for the If statement in the For i loop the second time through the For j loop, it should be an empty variable, but instead the code is reading it as having a value (from the previous pass through the For j loop).
I have tried using:
After the For j line, and instead of using:
I tried using:
and I've tried:
I get an Object error with either line I use. I just want to be able to reset the variables each time through the For j loop so in the case of an empty variable, it doesn't try to calculate based on the previous value.
I've included the code I'm using to calculate the net weight depending on which option button is selected. I use a flag ("VLAG") to know whether it's the first time the button is pressed. All variables are DIMmed at initialization (different sub).
By using other flags, I can see the code runs perfectly, but the tare weight ("TARRA") is not incremented each time the button is pressed. The formula I'm using to increment is
I have a file with a number of variables and wanted to know if there is any way to print out the list, from say the locals window or somewhere else. Empty values are fine, I'm just trying to compile a listing for our external documentation.
I started plugging away at VB a little over a month a go and i feel i've learnt alot but can see just how limited my knowledge is. how to define all my variables in one module that then will feed into the code on other modules and forms in the workbook where required. For example, my spreadsheet has approx 30 different variables that are called upon by a variety of subs. How do i define all these in one place and then just call on the relevant ones when required rather then having to redefine them for every new sub.
I have some buttons in different sheets in an excel file, each button has its own code, that is the reason I can not move the code related to each object to another location (sheet or module).
And I have one piece of code in Module1 (Auto_load) in order to execute automatically this routine every time file is opened. Inside "auto_load" routine I initialize some values of some check buttons,options buttons and positions of some objects in diferent sheets, but I can not pass the value of variables between Module and Sheet's code even when I declare as public variables and/or function.
I am trying to bring up a form after clicking the "ok" button on another form. Both forms are defined in my public sub, so basically, the module creates both forms, calls the first form, and hides the second form. Then the first form hides itself and shows the second form. However, when second form is defined using "dim", the first form can't find it to show it, and when I make the second form "public" in my public sub, I get the error that it is an invalid attribute or function.
Here is the code from my sub that applies to this error:
Code: Public Sub PutInEngine() Dim InputForm As New FrmInputs InputForm.Show
After all the awesome macros I've obtained with the help of all of you, I now have over 30 macros, each in its own module. I have tried without success to re-name the modules with no luck. How is everyone organizing these?
I have declared one variable outside of Sub. And in a Sub I gave it a value. Then I run a macro, which should take that value and print it to the cell. However, it is printing a blank cell, not even "0" (I run a macro and anything in that cell is removed).
and then my macro:
How to make so macro see my variable declared outside of this macro?
I have a combo box which is on a worksheet and it's always active/visible. I need to pass on the selection data to another project. I thought I had successfully converted it to a public variable but it doesn't work.
I've indicated that the Tr variable is public and it does populate with the results of the case select but to no avail. What do I have to do?
Public Tr As String Public Sub ComboBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
Dim a As String Select Case ComboBox1.Value Case "NYF PT"