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.
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 is the syntax for passing arrays to functions? In other words, for the following simple test example, what would work? It is highlighting the asValue parameter and saying, "Type mismatch: array or user-defined type expected". This occurs whether or not I follow it with the empty parentheses.
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'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?
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.
I've got this relatively simple file I use to calculate some values for a game mod. Its currently using ad-hoc excel functions, but its maths related to aerodynamics and they get unreadable quickly. I'd like to move to VBA functions for ease of maintenance, and programming the maths part of the code has been no issue so far.
However, I don't understand how to replicate the functionality of the default maths functions regarding range inputs.
Simple example, one of the intermediate values is the Aspect Ratio of the wing:
This works fine with both numeric inputs, cell references, or, as shown here, defined range names.
However, my VBA version:
Code: Function AspectRatio(b_2 As Variant, mac As Variant, Optional round As Integer = 3) As Variant AspectRatio = Application.round(2 * b_2 / mac, round) End Function Only works with single-cell references or numeric inputs.
When called as:
Code: =AspectRatio(Semi_Span, Mean_Aerodynamic_Chord) It fails.
From my limited understanding - I've only started looking into VBA about 2 hours ago - I need to do a IsNumeric test on the inputs in question, and then use the row from Application.Caller.Address to select the right cell if the input is a range? Am I on the right track?
How to craft a Sub I can call in the 15+ math functions I made.
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
(Possibly unnecessary information: This is for a budget spreadsheet I am creating. Ideally when I am finished with the macro it will allow me to add a line item under a specific program and update the total amount spent. The budget is broken out by month, rather than program. When a new program begins the amount in column J will always be zero, hence the Sum until 0 is reached.)
I'm trying to pass an array from one userform to another. When I run the script, it says: "Constants, fixed-length strings, arrays, user-defined types, and Declare statements not allowed as Public members of an object module". It explains further that you cant make an array public. How can I pass my array between forms? It suggests using an varient that contains an array, I tried doing this, heres my
Dim multiAry(15) As Range Public vmultiAry As Variant
'... and later:
vmultiAry = multiAry
'... and on the next userform:
Dim multiAry(15) As Range multiAry = UserForm1.vmultiAry
With this, I get an error saying "Compiler error: Cant assign to array" with "multiAry =" selected. Am I doing it totally wrong? How can I pass an array between forms?
I am trying to populate many arrays with the same code using something like this. For this test, assume the following data in A1.
1 2 3 4 5
6 7 8 9 10
11 12 13 14 15
16 17 18 19 20
21 22 23 24 25
Code: Sub populate() Dim firstArr(5), secondArr(5), thirdArr(5), fourthArr(5), fifthArr(5) As Integer Dim r, c, num As Integer
[Code]....
The above code does not work of course and falls over. I am unsure whether I should try and concatenate with something like this eg "" & arrName(i) = Cells (r,c) or go down a different route.
correctly using variables within functions used in formulas specified by VBA. Here's an example:
I want to place a formula within a workbook file called "Books 2003.xls" that goes to the version of the file for the previous year (or whatever year is specified) and does a Sum of a particular range (where that range is also specified as a variable) so it sums up the Total for the previous year for the same number of months that have data in them to date for the current year. Once VBA places the formula where it belongs, it should be able to always provide a running comparisson with the current year to date total and the totals for the same period for the previous year. Here's an example of the intent of this simple formula:
The workbook files will all be named the same way: Books 2002.xls, Books 2003.xls etc.... The range to be summed will always start at R4C2, but could then end anywhere from R4C2 (same as B4) up to R15C2 (same as B15).
I already have a routine that captures the value for the variable CurrentYear in the form "2003" and thus have another variable for PrevYear (= CurrentYear -1), and so then have a way to correctly specify the variable "BookName" to get the correct filename needed. I also have a variable for the CurrentMonth in the form of "1 through 12", and can use that to specifiy the correct RowNum needed to determine the end of the range (RowNum = CurrentMonth + 3).
I've tried to substitute the variables "BookName" and "RowNum" into the above formula with various combinations such as what follows, but I'm not getting the correct syntax with the right number of quotes etc...:
I'm trying to pass a multidimensional array to a function that I have defined, but I receive an error about an object mismatch. Here is what I have in regards to the array and function. What should I change?
Code: Dim diffArray() As Integer Dim countArray() As Integer Redim countArray(count,2)
diffArray = getRunningSum(countArray) End Sub
Public Function getRunningSum(ByRef countArray() As Integer) As Integer() 'Code here...... End Function
