Passing Global Variables / Values To A Procedure

Mar 24, 2014

I understand the use of Global variables in the sense that they can be used throughout the project. However, I also understand (I think) that unless these variables (like any) are released from memory or the values changed by some other means they will retain their values when accessed inside a another procedure regardless of where the calling procedure is Public or Private. This seems to be a convenient way to access the values of these variables without having to make ByVal or ByRef declarations in the procedure calling process.

Firstly, is my understanding of this correct? (That the variables and associated values) can be accessed from within a calling procedure without formally passing them in?

Second, even if this is true is it considered to be bad practice because the variables will store the last known value which may or may not be correct if you aren't paying attention to how the variable was last set.

View 2 Replies


Passing Argument To Procedure From Onaction

Jun 28, 2006

I am calling a procedure from a commandbar button using on action.

With oButton
.Caption = "&Progress Report"
.OnAction = 'this bit please
.BeginGroup = True
.FaceId = 576
End With

Private Sub ProcessingRequest(ByVal shtRequest As String)

End Sub

View 9 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

Global Variables Setting A Date

Jul 28, 2014

i have a variable that will be the same in every module within a workbook. i have tried playing with Global, Public, Conts, etc. but get compile errors.

essentially this is what i want... i just want to set the dte globally so i dont have to set it in each module.


Global dte As Date
If Weekday(Now()) = vbSunday Then
dte = Date - 2
dte = Date - 1
End If

View 3 Replies View Related

Global Static Variables On Workbook_open

Jan 20, 2007

problems with Global Static Variables.

In the Workook_open Sub I want to define 2 global static Variables.
For some reason I can't get it to work.
Whenever I try to access these vars they have the value "0"
I use Excel 2000.

Here is what i did:

Dim i As Integer
Dim ws As Worksheet
Public Static GlobalStartX As Integer
Public Static GlobalStartY As Integer

Private Sub Workbook_Open()

GlobalStartX = 6
GlobalStartY = 21

End Sub

View 4 Replies View Related

Passing Variables Between Subs?

Nov 3, 2009

I think I am confused as to how to declare a variable in one sub and use it as an input into another sub. I have attached some code below that assigns a value to two variables and then calls a sub that uses those values. I am brand new to programming so I guess I am confused how to implement this.

View 3 Replies View Related

Passing Variables To Other Subroutines In Same Mod

Nov 3, 2009

I'm having issue with passing along a variable. I learned this morning how to pass these along. But for this instance something isn't working correctly.

The code is below:

I cut out all the subs in between so you can see the problem.I think it has something to do with "File search"

The problem is after sub New_Pro_Test runs then returns back to Sub RunFolder I get subscript out of range error. It is suppose to open the next workbook in that folder.

View 7 Replies View Related

Passing Variables From A Userform

Dec 21, 2009

I am trying to eliminate a lot of the global variables from my program by passing the variables to my functions and subs as arguments. I am stuck though when it comes to variables created in userforms.

The program starts with a series of userforms that asks the user for information that will be used throughout the rest of the program. Data is assigned to the variables on the click events. Is it possible, without using global variables, to pass those variables to the rest of the program?


View 3 Replies View Related

Passing Variables To Subroutine

Feb 20, 2010

My code passes a string created by a function to procedure. It all works great, but i need to add something so that if the string = false then sub doesn't run. What's the best way to do that. Here is some of the

View 6 Replies View Related

Passing Variables Between Subs

Oct 30, 2013

I have some code I'm running:

Sub Main ()
Dim Value1 as integer
Call firstroutine
Call secondroutine
End Sub


Somehow I need to pass Value1 & Value2 to secondroutine.

View 5 Replies View Related

Passing Variables Between Macros

Jul 20, 2007

I am familiar with the normal method of evaluating a variable and passing it to another macro using Call MacroX (var1, var2).

In this instance, I call macro 1 and within macro 1, I call macro 2. Macro 2 establishes some variables, finishes at Exit Sub and hands control back to macro 1.

I want these variable returned to macro 1. How do I do that?

View 9 Replies View Related

Calling A Sub From Another Sub - Passing Variables

Aug 1, 2007

problems with the syntax involved in calling one subroutine from within another. I want to pass two variables calculated in a 'main' sub into the parentheses of another I am calling (see pseudocode below). When I pass one variable, everything works fine, however, when I try to include another, I get an error message - something about expecting an "=" in the syntax, which is clearly not correct.

Sub main()

row = .....

column = .....

counting(row, column)

End sub()

Sub counting (row, column)


End sub

View 9 Replies View Related

Passing Variables Between Excel VBA And VB

May 28, 2009

I have an Excel workbook with a command button on it. When this button is clicked I wish to pass the filename of the Excel workbook to my Visual Basic program, I have so far got the following
Public Declare Function ShellExecute Lib "Shell32.dll" Alias "ShellExecuteA" _
(ByVal hwnd As Long, _
ByVal lpOperation As String, _
ByVal lpFile As String, _
ByVal lpParameters As String, _
ByVal lpDirectory As String, _
ByVal nShowCmd As Long) As Long

View 9 Replies View Related

Passing Date Variables To SQL

Jun 1, 2006

I am trying to write a query to pull data using Basis ODBC from our accounting system into Excel. I want to limit the results to a specific range of dates. I can hard code the start and end dates into the Critirea in QueryBuilder and get the results I want, but I would like to enable my users to enter variable Start and End dates in cells in the Workbook so they could select ranges at runtime.

I currently have a StartDate named range in cell C2 and and EndDate named range in cell D2. The data columns would contain fields for "Order Date", "Customer Name", "Order Total" and the like. I want to be able to limit by "Order Date" using the StartDate and EndDate variables.

View 9 Replies View Related

Passing Variables Between Forms

Jun 28, 2006

Is it possible to pass a variable between forms? For example, I have a textbox on a form that takes a date from a user. Next to the text box is a button that calls another form that is a selectable calender with each of the dates on the calender as buttons. If a date button is pressed a variable is created which I want to pass back to the first form and place it into the textbox.

View 4 Replies View Related

Passing Array Variables Between Sub Routines

Jul 22, 2014

Okay, I made an epic fail on a previous post that i turned to Solved, a real error on my part of not fully testing before i posted Solved, basically i was so excited that i'd solved it myself, well partially.

I have the following code in the first sub routine that collects which option button has been selected.

[Code] .....

The second sub routine is per following.

[Code] .......

Unfortunately when i tested this with only one variable myRev(1) it worked fine but when i added more myRev variables 2 thru 4 if failed, so is there a trick to passing an array variable between sub routines?

View 2 Replies View Related

Passing Array Variables In Functions?

Sep 15, 2012

What I am trying to do is to automatically build a "tree" diagram representing the links in a huge model which is dynamically configured. What I have a problem with is the following:

The tree consists of layers, I start off with the top layer and for each entry in the top layer I can add all its subsidiary layers and draw links between them, this uses a function which takes as its arguments the node name and its layer number (how far down the tree it is) and the number of items in that layer so far.

So I start at the first item in Layer 1 and there are as yet no layers below it. I start at the first one and add the first item in layer 2 then I kick the function off again and that adds the first item connected to item 1 in layer 2 in the layer below (3) starting at the first one, and so on. When I reach the bottom I go up one layer and add the second item in the bottom layer and so on. When I have added all the connctions to the first item in the next to bottom layer I go up one layer and add the second item connected to the first item in that layer and then add all the items connected to it and so on and so on.

In this way I build up the network exhaustively (to make things more complex more than one item in a layer may connect to the same item in the layer below).

I can do almost all that, the issue I am struggling with is I need to keep track of how many items there are in each layer (as some layer 1 items connect to 1, 2, 3 ...8 layer 2 items and so on). my idea is to keep a running total of these in an array LevelCount(1), LevelCount(2) etc. so when I add a new item to a layer I know where to put it. However I cant workout how to do this final step.

Currently I have a function that draws the nodes below a specified node this and takes the correct value from the array LevelCount(n) by passing Levelcount(n) (where n represents how far down the tree you are) into the function and the function then updates the value of LevelCount(n) (ByRef) so that next time I use it it is correct. That is fine but what I want to do is to is to call the same function from within itself when it adds each node which would make it work automatically - it would keep calling instances of the function until it reached bottom and then go back one step at a time to the top but I cant work out how to reference the right value in the array to pass into the second (and subsequent) instances of the function.

I don't think I can simply pass (n) into the function and in the body of the function set LevelCount(n) = LevelCount(n)+1

I also dont know ahead of time how many layers the model will have, nor can I tell which layer a node sits in as it depends on the links that are dynamically configured.

Beyond this a node can also be subsidiary to nodes in more than one level so it needs to sit at the lowest level - but I suspect if I can work out how to do the first bit i can do this too.

View 7 Replies View Related

Passing Variables From A Macro To Sub-Routine?

Nov 1, 2012

In my code I am using several variables to create a concatenated string and this is making my code quite lengthy..Now I have manged to use it in the SELECT CASE but it still remains big..

SO I was thinking if it were possible to break it into smaller parts and create a Sub-Routine which can be called in the Main code in the select case for the specific condition this way I can use it in Multiple places without hassles..

find the code which I need passing the variables..

Sub Condition_I()
Dimm Search_Value as string
Dim INo As Long
Dim Whole_Text As String
Dim Lookup_No1 As String
Dim Lookup_No2 As String
Dim Condition_I_Text As String
Dim WksI As Worksheet


I need to pass the Search_Value from the Main Code and also the whole_text back to the Main Code..

View 9 Replies View Related

Check Boxes, Variables And Passing Them All

Mar 29, 2008

I have a worksheet with a command button, this button opens a userform with check boxes. the user has 4 options and can select them all if they choose, they click the command button on the userform and then the original sheet gets populated with only specific data based on the variablesbut nothing happens.

Private Sub CmdStructStrategy_Click()
FrmStrategy.Lbl1.Caption = "2007 Roll"
FrmStrategy.Lbl2.Caption = "Flat Price"
FrmStrategy.Lbl3.Caption = "Options"
FrmStrategy.Lbl4.Caption = "Time Spreads"
End Sub

View 9 Replies View Related

Passing Variables Within Procedures On A Worksheet...

Apr 5, 2007

passing variables within procedures on a worksheet. i have:

Private Sub lblGoToMaterials_Click()
'email to:
'grab current position
myRow = ActiveCell.Row
mycol = ActiveCell.Column

and i would like to pass the values gotten in myRow and mycol to:

Private Sub cmdGoBackTo_Click()
Application.Goto Reference:="R" & myRow & "C" & mycol

View 3 Replies View Related

Drop Down Change With Passing Variables

Aug 2, 2007

I have 18 drop down lists that are identical and named DropDown1, DropDown2, DropDown3, etc. Each one references cells (cell link) E1, E2, E3, etc respectively in another worksheet Lookup that is hidden. I am trying to write up some code that when the user selects any drop down, depending on which one it is, it passes certain values to another Sub which has cases. I got my first ideas from an old thread Drop Down List Box but there were no variables being passed.

My code originally worked with one variable being passed (x), but now I am trying to pass 2 (x and nrow) and I get an error in the Sub DropDown1_Change(): ByRef argument type mismatch. Below is my coding, I only included a few drop downs for this example. The Cases are based on the cell link (ie Case 2 is when the cell link shows '2' in the Lookup sheet) I only used a message box for testing purposes. I will have to do some other stuff but I need these variables passed first before I can continue

Public x, nrow As Integer
Sub DropDown1_Change()
x = 1
nrow = 2
Call get_surcharge(x, nrow) 'this is where I get the error
End Sub
Sub DropDown2_Change()
x = 2
nrow = 3
Call get_surcharge(x, nrow)
End Sub............

View 3 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

Passing Variables Back From A Called Subroutine?

Nov 3, 2009

I have a question. Can this be done. Pass a couple of varibles back from a called sub routine?

The calling sub calls the called sub does which has code plus a couple of varibles needing to come back.

Scope: The called sub is a series of case statements with code and sets varibles which are needed back in the calling sub.

View 6 Replies View Related

AddFields Method Of The PivotTable Class Failed Passing Variables

Aug 20, 2007

I am trying to pass variables to a sub which creates a pivottable.

The following line of code works:

pt.AddFields RowFields:=Array("BG_DETECTION_DATE", "BG_SEVERITY"), ColumnFields:=Array("BG_PROJECT_DB", "BG_USER_01")

I want to create multiple pivottables so I created a seperate sub and intend to pass information to them. I have tried doing it as follows but it doesn't work:

Dim row_fields As String
Dim column_fields As String

Row_Fields = """BG_DETECTION_DATE"", ""BG_SEVERITY""" .....................

View 72 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

"set..with" As Global Variables

Mar 6, 2007

I have "set .. with" blocks in some procedure. I want to make it global. How I do it? I have following:

Public cb As CommandBar
Public ctrlClear As CommandBarControl
Public ctrlInit As CommandBarControl
Public ctrlGUI As CommandBarControl

Sub CreateToolbar()
Set cb = Application.CommandBars.Add( Name:="auto", temporary:=True)
With cb
Set ctrlClr = .Controls.Add(Type:=msoControlButton, temporary:=True)
Set ctrlInit = .Controls.Add(Type:=msoControlButton, temporary:=True)
Set ctrlGUI = .Controls.Add(Type:=msoControlButton, temporary:=True)
End With
End Function

Sub ButStatus()
ctrlClear.Enabled = True
ctrlGUI.Enabled = False
ctrlInit.Enabled = True
End Sub

I get "Object Variable.. not set."

View 2 Replies View Related

Passing Values Between Procedures

Feb 6, 2010

I have a workbook with no data sheets showing with all actions being taken by the use of Userforms.

To make the workbook look consistent I would like to use a Userform rather than the standard MsgBox, the problem I cant seem to work around is passing the result of a Command Button back to the original proceedure.

I am trying to work along the lines of this but I am not sure where I am going wrong.

This procedure would call the Userform and then provide the next action dependant on the users choice:

Public Sub Testing()
Call TestMe
If Ans = Yes Then
Sheets("Sheet1").Range("A1").Value = "Yes"
Exit Sub
End If
If Ans = No Then
Sheets("Sheet1").Range("A1").Value = "No"
Exit Sub
End If
If Ans = Cancel Then........................

View 9 Replies View Related

Procedure To Copy Values From Different Worksheets To New One - Given A Value

Apr 19, 2014

Basically, I have six worksheets where I have, on two different columns each,

the code, the quantity, the shelf

Where it is stored.

What I have been asked for is: given the shelf, to have got back all the codes and quantity that there are in this shelf.

My Idea is: in a sheet called <<initial>>, create a cell where I write the value of the shelf (this value in a text).

Then, with a VB procedure, start activating first worksheet, start reading the first column.

if the shelf value corresponds to the required shelf, then activate a sheets called "Results" and write there all the value corresponding to code and quantity.

Then, when ended the column, I would move to the second column. Once ended the second column I would activate the second worksheet.

But I am having some problems since the beginning, since I have problems in reading the value of the shelf column.

I am attaching how I have started to wrote the procedure : [Code] .....

Why I can read the value of the cell ?

View 7 Replies View Related

Keep Variable Values AFTER Procedure Ends

Mar 19, 2007

I have a User Form with 2 groups of 3 radio buttons let's say. The user must choose one button from each group, then press "GO" which runs specific code depending on what buttons they pressed.

How do I make the variables public or static so that after both radio buttons were selected, another seperate procedure in the same module (The GO procedure) can know what selections were made so it can run code based on those selections? In this example, there are only 2 groups of three radio buttons, but in reality I have 30 buttons and it keeps growing so I need the most direct/simple way to solve this.

For Example:

Radio Button Group 1:

Button 1
Button 2
Button 3

Same UserForm1
but now here is Radio Button Group 2:

Button 4
Button 5
Button 6

I need to know which buttons the user clicked from Group 1 and Group 2 after they finished both their selections from each group, and then run code depending on what combination of buttons have been selected. I'm good to go with the If/Then code to decide which code to run, I just cant keep the variables from reading as 'Empty' when I try to see what the user selected when pressing GO and running that procedure.

View 9 Replies View Related

Calculation Error After Passing Values To Variable

Jan 30, 2008

I currently have a userform which inputs data into two cells, the third then multiplies these two cells together, which is all honky dory. It works, 2 x 2 = 4.
But when i put 0.6 for example it really doesnt work. 0.6 x 25 = 25. WRONG. My code in the macro is fairly long winded but will paste anyway.

If ComboBox2.Text = "Pounds ()" Then
Sheets("Claims").Range("G16") = TextBox2.Text
Sheets("Claims").Range("F16") = TextBox2.Text
End If

Sheets("Claims").Range("B16") = ComboBox1.Text
Sheets("Claims").Range("C16") = TextBox1.Text
Sheets("Claims").Range("B16") = TextBox3.Text
Sheets("Claims").Range("D16") = TextBox4.Text
Sheets("Claims").Range("E16") = TextBox5.Text

Dim a As Long
Dim b As Long
Dim answer As Long
a = Sheets("Claims").[E16]
b = Sheets("Claims").[F16]
answer = (a * b)
Sheets("Claims").[G16] = answer

View 3 Replies View Related

Copyrights 2005-15, All rights reserved