Reset All Public Variables In A Module

Aug 6, 2009

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

View 3 Replies


Call Public Function From Class Module

May 25, 2006

I have written a public function inside a class module. Is there anyway I could call the public function in Excel? What is the exact syntax to do it?

View 5 Replies View Related

Reference Userform Controls In Public Module

Jan 9, 2007

I located combobox on userfom and don't see it in the module.

View 7 Replies View Related

Calling Module In Module With Variables

Oct 25, 2011

What i have at the moment is a module that contains code where i call a different module that i use as a procedure.


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?


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?

View 5 Replies View Related

Public Variables Not Holding The Value

May 14, 2008

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

End Sub

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.

View 9 Replies View Related

Public Variables Duration

Jul 21, 2009

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.

View 9 Replies View Related

Nullifying Public Variables

Oct 20, 2006

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

View 3 Replies View Related

Declaring Public Variables

Apr 19, 2007

how do you declare public variables? In one of my forms I declare the variables but for some reason can not access them in other forms.

Public Rm_new As Integer
Public Angle_new As Integer
Public Pl_new As Integer

Public Sub cal_graph_Click()
'Stating variables
Rm_new = text_rm.Text
Angle_new = text_angle.Text
Pl_new = text_length.Text

View 3 Replies View Related

UserForm Variables In Public Modules

Jan 13, 2008

I make a userform called Option and now i want to call some of the variables of the userform in a module. First i try to copy this variables in a cell with the next

ActiveSheet.Cells(2, 3) = Me.MediaP.Value

and it works. But now, i want to call to MediaP in a module. For do that I try to do this:

With Worksheets("Hoja2")
.Range("B1").Value = Option.MediaP.Value

but it doesn't work. I don't want to use public variables or copy/use the information of the cell(2,3), i only want to know how use the information on the textbox called MediaP in a module.

View 3 Replies View Related

Subscript Out Of Range Using Public Array Variables

Aug 18, 2006

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?

View 6 Replies View Related

Pass Variables From Userform To Public Procedures

Nov 17, 2007

When working in an userform, and you call another sub routine within the userform, will all variables passed automatically be byref (no way to do byval?)?

View 3 Replies View Related

Excel 2010 :: Public Variables From Event Handling Procedure?

Jan 22, 2014

Excel 2010

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

View 2 Replies View Related

Reset Variables For Each Pass Through For Loop

Jun 27, 2014

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

[Code] .....

I have tried using:

[Code] .....

After the For j line, and instead of using:

[Code] ....

I tried using:

[Code] ....

and I've tried:

[Code] .....

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.

View 6 Replies View Related

Reset All Numeric Variables During A Procedure?

Mar 19, 2009

Is there a way to reset all numeric variables to zero during a procedure (e.g. at the end of a loop), without having to manually set them all to zero within the code?

View 3 Replies View Related

Does Clicking A Button Reset Variables?

Jul 3, 2009

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

View 4 Replies View Related

List Of Variables In Module

Mar 19, 2012

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.

View 3 Replies View Related

Defining Variables In Separate Module

Jun 18, 2008

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.

View 2 Replies View Related

Passing Variables And Values Between Sheet Code And Module Code

May 13, 2009

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 have the following structure: ...

View 11 Replies View Related

Call Public Object From Public Sub?

Aug 14, 2012

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:

Public Sub PutInEngine()
Dim InputForm As New FrmInputs


View 3 Replies View Related

Keeping Track Of Which Macro Is In Which Module Since You Can't Rename The Module?

Mar 27, 2009

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?

View 2 Replies View Related

Copy Codes Of Module 1 And Transfer To Module 2?

Jan 24, 2013

Let's say i have 2 Modules on my VBA forms, is it possible to Copy all the Codes in Module 1 and Paste it to Module 2 by using a Command Button?

View 1 Replies View Related

Making Msgbox In One Module With (Yes) Linking To Different Module

Jan 19, 2012

i got a question how i can make a msgbox in one module with 'Yes' linking to a different module.

such as: this is located in module2

If MsgBox("Do you want to activate module1?" & vbCr & _
"" _
, vbYesNo, "Choose") = vbNo Then Exit Sub

i want that vbYesNo different

No as in Exit Sub

yes as in activate Module1

View 5 Replies View Related

Run-time Error '50289' :: Project Protection / Module Protection / Module Visibility

Aug 18, 2004

I recieve an error :

Run-time error '50289':
Can't perform operation since the project is protected.

When i try and run my code.

The code does add parts to modules and workbook events, but I need the code to be protected.

View 9 Replies View Related

Unzip Code - Works Without Variables, Breaks With Variables...

Feb 5, 2009

Unzip Code - Works without Variables, Breaks with Variables.... This has been driving me bananas...

I have the

View 2 Replies View Related

Set Public Variable

Feb 12, 2008

Setting the Public Statement does not work. These are my codes:

Option Explicit
Public Ankis_makro As Boolean
Sub Makro2_Ankis_veckor()
On Error Resume Next
Application. ScreenUpdating = False
Selection. AutoFilter Field:=1
Selection.AutoFilter Field:=2
Selection.AutoFilter Field:=3
Selection.AutoFilter Field:=4
Selection.AutoFilter Field:=5
Selection.AutoFilter Field:=6
Selection.AutoFilter Field:=7
Selection.AutoFilter Field:=8
Selection.AutoFilter Field:=9
Range(Range("J2"), Range("I65536").End(xlUp).Offset(0, 1)).FormulaR1C1 = "=TEXT(RC[-9],""ลลลล"")" ...................

What ever I do I can't get the Ankis_makro set to True.

View 2 Replies View Related

How To Dim Array And Its Values As Public

Apr 3, 2014

I am trying to declare a public (or global) array and it's values so I don't have to keep dimensioning it for each function I create.

I was trying things such as:

Public whatever(10) as string

whatever(0) = "something"
whatever(1) = "somethin else"

And tried creating an initialize macro in "ThisWorkbook".

View 11 Replies View Related

Macro Can't See Public Variable?

May 26, 2014

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?

View 8 Replies View Related

Reseting Public Arrays

Mar 19, 2009

I am using a set of arrays in a module. The arryas are set as public as the module that loads the data is called many times from diffrent other modules that then use the data.

How so I reset the array at the start of the module so each level is "Empty"
so new data can be loaded with no data left over from the last time the array was used.

View 2 Replies View Related

Public Worksheet Declaration

Aug 28, 2009

I'm using a public declaration for a worksheet but I seem to have to initialize it in each routine. Is there a way to set it and leave it? So in a module I have:

View 4 Replies View Related

Making Combobox Value Public

Jun 6, 2014

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"

[Code] .........

View 6 Replies View Related

Copyrights 2005-15, All rights reserved