Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Passing Variables To Subroutine

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 Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Passing Variables Back From A Called Subroutine?
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 Replies!   View Related
Passing A Variable To A Subroutine
My code defines a variable: newrow. Then I call a subroutine using the Call command.
The subroutine does not recognize newrow.

It says it has value zero. How can I pass the value of newrow on to the subroutine.

View Replies!   View Related
Passing Subroutine As A Parameter
I want to pass the name of the routine as a parameter.

View Replies!   View Related
Passing Variables To Other Subroutines In Same Mod
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 Replies!   View Related
Calling A Sub From Another Sub - Passing Variables
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 Replies!   View Related
Passing Variables Between Excel VBA And VB
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 Replies!   View Related
Passing Variables Between Macros
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 Replies!   View Related
Passing Variables From A Userform
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 Replies!   View Related
Passing Variables Between Subs?
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 Replies!   View Related
Passing Date Variables To SQL
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 Replies!   View Related
Passing Variables Between Forms
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 Replies!   View Related
Check Boxes, Variables And Passing Them All
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 Replies!   View Related
Drop Down Change With Passing Variables
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 Replies!   View Related
Passing Variables Within Procedures On A Worksheet...
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 Replies!   View Related
AddFields Method Of The PivotTable Class Failed Passing Variables
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 Replies!   View Related
Passing Variables And Values Between Sheet Code And Module Code
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 Replies!   View Related
Calling A Subroutine
Is calling a subroutine within another different than running it from the play button in the VB editor? I am getting different results.

I have the following code that I want to call from another subroutine that creates a series of command buttons.

Sub DynamicButtons()
Dim ButtonCount As Integer
Dim ctl As OLEObject

' Create the Button objects
ButtonCount = 0
For Each ctl In Sheets("Sheet1").OLEObjects .............

View Replies!   View Related
Writing Subroutine
in writing a subroutine that needs to ask the user

1) tax file number
2) Income
3) tax withheld

It then needs to use a function that i already made called 'incometax' that calculates 'taxpayable' and use that to calculate taxreturn (difference between 'taxpayable' and 'taxwithheld').

I then need it to output like this in a message box

Estimate for tax file number: .....
Created on ......
Total income: ......
Tax witheld: .......
Tax payable: ......Tax return: .......

View Replies!   View Related
VBA Calling Another Subroutine
Im trying to call another subroutine within the same workbook but it doesnt work,


Sub Macro1()

Dim mac1 As Variant
Dim mysub As Variant

Select Case Cells(1, 3)
Case mac1
Call mysub

End Select

End Sub

View Replies!   View Related
Executing The Same Subroutine For All Checkboxes
I have 60 checkboxes on my spreadsheet and basically, when any of the checkboxes are clicked on, the same action should be performed.

The only way I know how to do this is if I were to write the same code 60 times... one for each checkbox_click() method.

View Replies!   View Related
Can A Subroutine Be Called Within A Function
1. Can a Subroutine be called from within a user-defined Public Function? How?

2. Is there a difference between calling a subroutine with a 'Call Sub_Name' statement, vs. calling the subroutine with an 'Application.Run'(?? or similar) statement?

View Replies!   View Related
Keyword Call When Invoking A Subroutine
Am I right to say that "Call" is not mandatory and should be used only to invoke subroutines that do not return a value?

If you use the keyword "Call" to invoke a subroutine that does return something, the returned variable will be discarded.

View Replies!   View Related
Invalid Use Of Property When Calling Subroutine
I've ran into a problem with one of my workbooks which appears to only affect Excel 2007. It works fine in Excel 2003.

Basically I am calling a sub routine contained in a module from worksheet code. The sub is Public in the module and I use the following code to call the sub.. The code Errors immediately here -

Private Sub mybutton_Click()
Worksheet Code -

Private Sub mybutton_Click()
End Sub
Module Code -

Public Sub mysub() code
End Sub

This works fine in Excel 2003?! Is there a different way to call the sub with Excel 2007

View Replies!   View Related
Call A Subroutine From An Xls File On The Network
I'm trying to put some visual basic out on the network drive at work so I created a book and called it Macro.xls. I then saved the code within that book and saved it out on my network. I'm now trying to run that code by calling the sub and don't know how to do that.

View Replies!   View Related
Pass Variable To A Call Subroutine
I have a code below which need some input from user. This input will also be serve as the input of the subroutine which i am going to call. However, i do not know how to go assign this input to the subrountine which i will be calling, can anybody help ?

For example, the "input" variable will also be served as an input in subroutine test2 ...

View Replies!   View Related
Unzip Code - Works Without Variables, Breaks With Variables...
Unzip Code - Works without Variables, Breaks with Variables.... This has been driving me bananas...

I have the

View Replies!   View Related
Error On Button Code Reference To A Subroutine
I'm having trouble calling a subroutine from a command button. It's puzzling because I've set up buttons before and didn't have this trouble.

Here is my button
Private Sub EPConversionButton1_Click(ByVal target As Range)
Convert_Hrs_EP target
End Sub
And here is the subroutine.

Sub Convert_Hrs_EP(target As Range)
End Sub
There's nothing there yet, but I keep getting error messages regarding the transfer from the button code to the subroutine.

The message is: Procedure declaration does not match description of event or procedure having the same name. The Help file says this means that my procedure has the same name as an event, but does not have the same signature. But it's not so.

View Replies!   View Related
Macro Call To Analysis Toolpack Subroutine
I know this problem has been answered before, but my browser is giving me hell when I try to use the search function, so here I am clogging up the post forum.

I would like to be able to access one of the subroutine functions built into the Analyis Toolpack add-in. Using the macro-recorder, here's what I came up with:

Application.Run "ATPVBAEN.XLA!Random", ActiveSheet.Range("$F$23"), 1, _
15600, 7, , ActiveSheet.Range("$A$3:$B$12")

I get a run-time error 1004 when executing stating that "ATPVBAEN.XLA Could Not Be Found". I'm sure this has to do with they improper way in which I'm trying to call this function.

View Replies!   View Related
Create Range Object & Pass To A Subroutine
Create Range Object & Pass To A Subroutine

Sub Test(ByRef objRange As Range)
objRange.Value = "Hi"
End Sub

Sub TestTheTestMethod()
With ThisWorkbook. Sheets("Sheet1")
Set objRange = .Range(.Cells(1, 1), .Cells(i - 1, 3))
objRange.Value = "Hi" 'This works fine !
Test (objRange) 'But here... Getting ERROR 424 -- Object Required
End With
End Sub

View Replies!   View Related
Dynamic Subroutine Generation Creates Compile Error
I'm am running Excel 2003 SP2 on Windows XP SP2.

I have a macro which modifies cells in a spreadsheet. But for brevity sake, I have an empty spreadsheet which has an Auto_open macro and two subroutines in it. The subroutines initially consist of just the Sub and End Sub statements.

The Auto_open procedure reads in lines from two text files (generated by another application), and inserts the lines into each of the empty subroutines. Auto_open then runs the two subroutines.

I have been able to get this dynamic creation of the subroutines to work for one subroutine, but not for the second. I receive the compile error: "Only comments may appear after End Sub, End Function, or End Property". I don't see anything wrong with the inserted code.

The modified subroutines appear fine, and if I save the macros with the modified code, close excel, rename the text files so they are no longer read in, and re-open the spreadsheet, the auto_open procedure and the two subroutines run fine. This tells me the code itself is okay, and yet it won't work during the initial run.

Below is the macro code in its original state:

Sub Auto_open()
Dim fso, f
Dim VBCodeMod As Object
Dim LineNum As Long
Dim StrFileName As String

ShowVisualBasicEditor = True

Set VBCodeMod = ThisWorkbook.VBProject.VBComponents("Module1").CodeModule

End Sub
The contents of the read in file, %TEMP%subA.txt, is just one line:
MsgBox("inside SubA")

The contents of the read in file, %TEMP%subB.txt, is just one line:
MsgBox("inside SubB")

When I open the spreadsheet the first time, it gives me the first message box from Sub A, but then generates the error and highlights the Sub SubB() line.

To duplicate the problem:
1. Insert the macro into a spreadsheet. Save and exit it.

2. Create the files %TEMP%subA.txt and %TEMP%subB.txt containing the single MsgBox lines.

3. Open up the spreadsheet. SubA will run and a message box will appear. Then the compilation error will occur.

4. Save and exit the spreadsheet.

SubA will now contain:

Sub SubA()
MsgBox("inside SubA")

End Sub

SubB will now contain:
Sub SubB()
MsgBox("inside SubA")

End Sub

5. Rename the two text files, so that the next time you open the spreadsheet it won't try to insert the lines from the files into the subroutines.

6. Re-open the spreadsheet. Two message boxes will now appear, one from SubA and one from SubB.

View Replies!   View Related
Event-handler Subroutine To Transfer Checkbox Text To Cell
to save typing the same things over and over I have created a dialog box with checkboxes, named with several common terms we use when writing an invoice. i.e. dig a hole, paint a fence etc. I have assigned the dialog box to a button on the worksheet.

When I check the checkboxes, I want the text to go to a blank section of the invoice one underneath the other. The reading I have done suggests this is an event-handler subroutine, I just don't know enough about VBA yet to be able to write the code.

View Replies!   View Related
Subroutine :: That Accepts A Date From The User And Then Displays A Summary Of The Data
write a vb subroutine that accepts a date from the user and then displays a summary of the data (which i have) for that day in a message box

View Replies!   View Related
Code Breaks Sometimes/sometimes Not: Subroutine Executed When The User Presses A Command Button
I have inherited support for the an Excel 'program' that seems to be fairly unstable. I am currently having problems with the following code in a subroutine (executed when the user presses a command button):

With ActiveSheet.PageSetup
.PrintTitleRows = "$1:$7"
.PrintTitleColumns = ""
.PrintArea = ActiveSheet.UsedRange
End With

Sometimes it works and sometimes I get the following error: Run-time error '1004'
Unable to set the PrintArea property of the PageSetup class.

on the .PrintArea = ActiveSheet.UsedRange

I am having a hard time figuring out what is causing its wishy-washiness. Running the following code (launched from worksheet named 'consumer' with a command button):

Sub subConsumerFacesheet()...................

View Replies!   View Related
Function Back Variables: Function Give Two Or More Output Variables
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.

View Replies!   View Related
Defining Variables Using Variables
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.Add "Excel files", "*.xls"
fd.ButtonName = "Select"
iFilePicked = fd.Show
If iFilePicked = -1 Then
strFilePath = fd.SelectedItems(1)
Else ..................

View Replies!   View Related
Passing Value
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?

View Replies!   View Related
Passing Different Value From Combobox
I'd like to know how to send a different value to a cell based on what is selected in the combobox. Like a vlookup, but from within the box.
Something like :

View Replies!   View Related
Passing Old Value's To Comment
I am building a tool that uses Pivot Tables (so I can't use track changes) and I have found cool code that will insert timestamp and username in the comment when a change occurs. But I need to modify the code to also take the old value and put it into the comment as well. Here is the code to register the change into the comment:

View Replies!   View Related
Passing A Range From One Sub To Another
I think this is a relatively easy issue, but I don't know how to do it. I call the Sub SortRange in another Sub SortDeliver. A range (ran5) is defined in Sub SortRange that I also need to use in Sub SortDeliver. How do I pass the range or get the second sub to recognize the range?

View Replies!   View Related
Passing Value From One TextBox To Another
I've a userform (UserForm1) that on closing will open one of 5 other userforms. Which of the userforms that opens will be determined at runtime, depending on an option a user has previously selected. The name of the userform is held in a cell in a workbook and is opened as follows:


where the value in Sheets("Sheet1").Range("A1") is "UserForm3"

UserForm1 contains TextBox1. How can I pass the value in TextBox1 to a textbox in UserForm3 when the name of UserForm3 is held as a variable in a cell in a worksheet?

View Replies!   View Related
Passing Array To VBA
Passing Array to VBA. I have the following

View Replies!   View Related
Passing Arrays
Option Base 1
Dim Covariance(1 To 5, 1 To 5) As Double
Dim Mean(1 To 5) As Double
Dim Portfolio(1 To 10, 1 To 2, 1 To 5) As Double
Dim PortfolioValues(1 To 10, 1 To 2) As Double

And my main function:

Function Generate_Portfolio_Values()

Dim a As Integer, b As Integer, c As Integer
Dim m As Integer


View Replies!   View Related
Passing Worksheetname
I have following code which identifies two worksheets, a source (wbSRC) and a destination (wbDEST). In the following code I am trying to get the values for wbsrc and wbdest to be written to the merged spreadhseet. But I am not sure how to go about this in VBA. The commented out approach does not work.

For Each rcell In wbList.Sheets(1). Range("A1"). CurrentRegion.Columns(1).Cells
Set wbDest = Workbooks.Open(rcell.Value)
Set wbSrc = Workbooks.Open(rcell(1, 2).Value)
wbSrc.Sheets(1).Range(copyAddress).Copy _ Destination:=wbDest.Sheets(1).Range(destAddress)
'wbDest.Sheets(1).Range("q3").Formula = "Workbooks.Open(rcell.Value)"
'wbDest.Sheets(1).Range("q4").Formula = "Workbooks.Open(rcell(1, 2).Value)"

View Replies!   View Related
Passing Parameters In Macro
I'm trying to figure out how to pass parameters to a date field in a macro to control the display in a pivot table.
The macro currently uses a With statement to set the properties of 177 of the 180 dates to false, leaving the remaining three dates true and visible.

View Replies!   View Related
Passing Value From Userform To Macro
I have used my Excel Bible as well as and to write the current code I have... but it doesnt work! I think I am just missing a small piece, but I dont see what it is.

I have a macro that calls a userform - from the userform, a Customer is chosen. When the "extract" button is clicked on in the userform, it should pass the customer name to the next macro to do a bunch of stuff - including filtering the data by the customer chosen in the userform.

Here is the code - take a look and see if you can figure out why it doesnt work.

Code for "extract" button (I have tried several differnt variations - this is the current one):

Code: ....

View Replies!   View Related
Passing Values Between Procedures
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 Replies!   View Related
Passing Formatting Through - A String
I have an excel sheet, in that excel sheet is a command button, once clicked the command button creates a Word document.

Im trying to get my VBA script to bold certain text that will be in the word document. But all my attempts to bold certain cell contents have failed.
Im not getting any errors from the editor, its just not bolding certain text Ive told it too when the word document gets created.

I have included my script below (And Ive tried the following methods already)

1. ActiveCell.Font.Bold & "This Is Function One, This Text Will Appear Bold" & wks.Cells(ActiveCell.Row, 2) & _

2. "This is Function Two, This Text Will Appear Bold" & wks.Cells(ActiveCell.Row, 2).Font.Bold

3. "This is Function Three, This Text Will Appear Bold" & wks.Cells(ActiveCell.Row, 2) & (ActiveCell.Font.Bold) & _

Sub TransferData()
'This macro transfers the data range to a Word Document
'docFullName = The full name of an already existing Word document

View Replies!   View Related
Passing Array Of UDT With String To C++ Dll
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 Replies!   View Related
Passing 3D SUM Formula In Macro
I'm trying to set up a journal-type workbook. I have two sheets named Start and End-the first sheet has a cell that contains a formula, =SUM(Start:End!B4) B4 is where you put the number of hours worked in a day. I then run a macro that copies the sheet called Start for each day of the year. What I was expecting was that the cell with the formula would provide a running total of the hours worked through the year on each sheet, but when the sheets are copied the formula changes to =SUM(#REF!B4). My macro to create a copy sheet for the whole year is as follows:

Sub MakeYear()
Dim SH As Worksheet
Dim D As Date, Y As Long
Set SH = ActiveSheet
Y = Val(InputBox("Year:"))
If Y < 2000 Then Exit Sub
If Y > 2100 Then Exit Sub
Application.ScreenUpdating = False
For D = DateSerial(Y, 1, 1) To DateSerial(Y, 12, 31)
Application.StatusBar = D
SH.Copy after:=Sheets("Start")
ActiveSheet.Range("B2").Value = D
ActiveSheet.Name = Format(D, "mmm dd")
Application.StatusBar = False
Application.ScreenUpdating = True
End Sub

View Replies!   View Related
Passing Column As Parameter
How can I pass a column as a parameter when I execute the sub? ex:

View Replies!   View Related
Copyright 2005-08, All rights reserved