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


ADVERTISEMENT

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

Syntax For Passing Arrays To Functions

Apr 21, 2009

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.

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?

Example:

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:

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.

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

Replicate Default Behaviour When Passing Ranges To Functions

Oct 13, 2013

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:

Code:
=ROUND(2 * Semi_Span / Mean_Aerodynamic_Chord, 3)

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.

View 3 Replies View Related

Passing Array To OFFSET - Array Height Parameter

Aug 5, 2014

I want to pass an array to Offset in the "Height" parameter, without having to type the array.

{=MAX(SUBTOTAL(9,(OFFSET(A1,ROW(1:5),,{1,2,3,4,5}))))}

I can't seem to figure out how to build the {1,2,3,4,5}. I've tried another ROW(1:5) and have tried nesting that like N(ROW(1:5)) but nothing works.

How I can get the {1,2,3,4,5} without having to type it out (so that I can expand this to a larger list)??

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

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

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"
FrmStrategy.Show
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

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

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

Passing Array To VBA

Jul 19, 2009

Passing Array to VBA. I have the following

View 2 Replies View Related

Using Variables In Macro Functions?

Jun 27, 2014

My goal is to write an equation that allows me to total all of the numbers in a column until the value = 0.

When I write the following equation using actual cell references, I am able to get the results I am looking for:

=IF(LEFT($E$78,6)=" Expe",SUM($J$78:INDEX($J$78:J1000,MATCH(0,J78:J1000,),0)),0)

When I try to write it in a macro using variables, I get all messed up:

Selection.Offset(1, 0).Select
Selection.Font.Italic = True
ActiveCell.FormulaR1C1 = " Expense"

[Code].....

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

View 1 Replies View Related

Passing Array Of UDT With String To C++ Dll

Feb 17, 2009

I'm trying to pass an array of UDT from Excel to a C++ dll but I have no idea how. My UDT looks like this

Private Type Student
Age As Integer
Name As String
StudentID As String
End Type
And my dll function declaration..

Declare Function RegisterStudent Lib "SimpleDLL.dll" _
(arr() as Student) As String
.. which will return a unique Id in string

View 9 Replies View Related

Passing Array Between Userforms

Mar 31, 2007

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?

View 5 Replies View Related

Populate Multiple Array Variables With Same Code By Dynamically Changing Array Name

Sep 9, 2012

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.

View 6 Replies View Related

Syntax For Using Variables Within VBA Formula Functions?

Dec 3, 2003

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:

ActiveCell.FormulaR1C1 = "=SUM('[Books 2002.xls]Income Summary'!R4C2:R8C2)"

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

ActiveCell.FormulaR1C1 = ""=SUM('[" & BookName & "]Income Summary'!R4C2:R" & RowNum & "C2)""

This doesn't work, and I'd love to learn the correct rules for syntax when inserting variables into situations like this.

View 4 Replies View Related

Mismatch Errors With Variables In Functions

Jul 24, 2013

I have set variable A and B up as integers

When I input the variables into the function

Code:
Rows(A:B).EntireRow.Hidden = True

I get a mistmatch error. I've used a MsgBox to confirm that both variables are indeed returning integers.

View 3 Replies View Related

Passing Multidimensional Array To Function

Jul 15, 2014

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

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved