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.
I have a main macro (m1) that is outputing text to a datafile. When m1 calls a subroutine(say m2), how do I pass the file stream into the m2 as parameters so that m2 can output to the same textfile?
Sub m1() F1 = FreeFile 'Open "C:in.txt" For Input As intInFile Open ".out.txt" For Output As F1 Print #F1, ActiveCell.Value m2(what I Do put here?) End Sub
Sub m2(what Do i put here?) Print#F1, "stuff" End Sub
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.
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?
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
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?
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.
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.
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?
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
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 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 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
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 written a number of macros, and I typically use Application.Screenupdating = False at the start, and set it True at the end of each macro. However, I now have some big macros which call others as subroutines. This results in the screen updating for each pass thru a called macro, as the Application.Screenupdating = True statement is executed. Is there a means to repress this at the top level, or is there a better approach to take in writing code which will stop screen update for a macro, but not restart it each time the macro is called as a subroutine?
I have a very vast code with several subroutines based on selections from a userform.
I believe I have identified a loop that might be slowing down the process in the below:
Code: Dim n As LongFor n = 23 To 65 If ThisWorkbook.Worksheets("record").Cells(ComboBox2.ListIndex + 3, n).Value = "INT" Then UserForm2.ListBox2.AddItem ThisWorkbook.Worksheets("record").Cells(2, n).Value End If If ThisWorkbook.Worksheets("record").Cells(ComboBox2.ListIndex + 3, n).Value = "EXT" Then
[Code] ......
Basically the code will go through each value (that can be only of those four instances) and put the title inside a different listbox.
This works, but it seems to be maybe too "step by step" and direct? Is there a way for it to skip after it found the corresponding value to the next N without checking for a match with the other items?
As you can see below, I've written code that writes random numbers into three columns of a spreadsheet (10 numbers in each column).
What I want to do is create code that will run the random number generator for a period of 1 minute and then stop. I know that I will need to write a timer subroutine to do this but I'm how unsure how to do this.
My problem today, is actually being able to call the routines. I have tried to make a generic setup, so that each subroutine to be called is named "Macro_[number here]". So Macro_1, Macro_2 etc. By doing this I am hoping to be able to call all the subroutines using a For Each code.
Can a Function give two or more output variables. e.g.
Sub a() x = 5 result = Y(x) End Sub
Function Y (x As Integer) As Integer Dim B B = ... * x Y = ... * B
this will give back Y as a result. But if I want to get 2 or more output variables (let's say I need to get also B into sub) from one function, how should I do that? I need this because function works with large matrix and I want to extract some values appeared in between.
How do I pass values between procedures? I have two command buttons in the worksheet.
If I have a procedure A that asks user to input a value "x", when a command button is pressed. Then another command button is pressed that passes the value onto another procedure B, which iterates a msgbox as many times as the number x,
How do I pass that value x from one procedure to another?
I'm trying to loop through a range in excel from access, checking where the titles (in Excel row 1) match with the fields (in a recordset in Access that is passed to the function) - and where they do, I want to dimension a variable to hold the column number - I'm not sure it's possible, but I'd be interested to know either way. The line I'm asking about is at the bottom of the code - the rest of the code is just to give context...
Sub ImportGeneric(rsImported As ADODB.Recordset, rsConfirmed As ADODB.Recordset) Dim fd As FileDialog Dim xl As New Excel.Application Dim wb As Excel.Workbook Dim ws As Worksheet Dim iFilePicked As Integer Dim strFilePath As String fd.Filters.clear fd.Filters.Add "Excel files", "*.xls" fd.ButtonName = "Select" iFilePicked = fd.Show If iFilePicked = -1 Then strFilePath = fd.SelectedItems(1) Else ..................
