I have some code I'm running:
Code:
Sub Main ()
Dim Value1 as integer
Value1=5
Call firstroutine
Call secondroutine
End Sub
[code].....
Somehow I need to pass Value1 & Value2 to secondroutine.
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 RelatedI have created a few User Defined Types in a Main subroutine, but when I pass them into another sub the intelisense for the elements doesn't come up. I can type in the elements manually - and it all seems to work okay - but with a lot of types and elements I really want it to save me the effort.
View 2 Replies View RelatedI have 2 sub, and I want to use the same variables that reference values on my spreadsheet.
for instance, I declare and set ws as worksheet("sheet1") in the first sub.
in the second one, I also want to reference to the same ws. Instead of having to declare it twice, being redundant, what would be a good way to do this?
I'm having some trouble getting to grips with using/calling variables and other sub routines.
View 9 Replies View Relatedtrying to get it to put the 2 variables into 2 cells in the worksheet for the moment!
The Forms code is:
Private Sub CmdCancel_Click()
Me.Hide
Unload frmMsgBox
Exit Sub
End Sub
Private Sub CmdOK_Click()
Me.Hide
End Sub
It always fills the cells with False and blank, no matter what buttons are pressed (except cancel), so all I can think is that the information isn't feeding back through properly from the form.
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.
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?
Example:
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 RelatedI 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?
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
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
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.
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 RelatedOkay, 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?
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.
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..
Code:
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
[code].....
I need to pass the Search_Value from the Main Code and also the whole_text back to the Main Code..
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"
FrmStrategy.Show
End Sub
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
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............
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.
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.
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""" .....................
I'm making a Form with multiple pages and on every page there are atleast 36 Textboxes.
[Amount] [Description] [price]
I want to run a small sub updating the price on changing the amount. My code now looks like this:
Code:
Private Sub TextBox1_Change()
Call UpdatePrice
End Sub
Private Sub TextBox2_Change()
Call UpdatePrice
End Sub
Private Sub TextBox3_Change()
Call UpdatePrice
End Sub
I have to do this on 8 pages, 24 to 36 times, which makes the code extremely long. Is there a better way to do this?
Something like:
Code:
Private Sub TextBox1_Change(), TextBox2_Change() etc...
Call UpdatePrice
End 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 have the following structure: ...
I'd like to combine two VBA subs existing on a single sheet. Since they can't duplicate I need some sort of a switch statement...?
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim VRange As Range, cell As Range
Dim Msg As String
Dim ValidateCode As Variant
Set VRange = Range("E4:E100")
For Each cell In Target
[Code]....
I have two subs which I've been trying to combine into one. Essentially all that is changing is the slicer name and range value.
Here's what I have:
Code:
Private Sub Workbook_SheetPivotTableChangeSync(ByVal Sh As Object, ByVal Target As PivotTable)
Dim cache As Excel.SlicerCache
Set cache = ActiveWorkbook.SlicerCaches("Slicer_Project_Type3")
Dim sItem As Excel.SlicerItem
Dim myString As String
For Each sItem In cache.SlicerItems
If sItem.Selected = True Then
myString = myString & "," & sItem.Name
[code].....
But it's erroring out on me.
How can I have a sub repeat itself? I have a code and I want it to ask the user at the end if they would like to do it again. If so the whole sub needs to be repeated. I'm sure this has something to do with looping, but I don't know how to manage this.
View 3 Replies View RelatedVBA - Exit all Subs not just current
View 2 Replies View RelatedCalling a Sub from another Sub is quite common, how about a Sub that does nothing else but call all the subs to execute the entire job?
Will the subs run sequentially after one completes, or will there be the likley hood they will bump into each other causing all sorts of chaos.
