Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Passing Variables to another macro
I am trying to modularize my code, but I'm having trouble with one aspect.
How do I make a variable such as a counter available within a macro that I just called? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Passing Variables to another macro
Option Explicit
Sub Main() Dim icntr As Long Dim adjNum As Long For icntr = 1 To 10 MySub icntr adjNum = MyFunc(icntr) MsgBox "Adjusted Counter is " & adjNum Next End Sub Sub MySub(num As Long) If num < 5 Then MsgBox "num: " & num & ", is less than 5" Else MsgBox "num: " & num & ", is greater" & _ " than or equal to 5" End If End Sub Function MyFunc(myNum As Long) Dim MyAdjustedNum As Long MyAdjustedNum = myNum * myNum MyFunc = MyAdjustedNum End Function -- Regards, Tom Ogilvy "Jeff" wrote in message ... I am trying to modularize my code, but I'm having trouble with one aspect. How do I make a variable such as a counter available within a macro that I just called? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Passing Variables to another macro
Here is an example:
Sub FirstProcedure() Dim lngCounter As Long lngCounter = 0 SecondProcedure lngCounter MsgBox lngCounter SecondProcedure lngCounter SecondProcedure lngCounter SecondProcedure lngCounter MsgBox lngCounter End Sub Sub SecondProcedure(Counter As Long) Counter = Counter + 1 End Sub -- Rob van Gelder - http://www.vangelder.co.nz/excel "Jeff" wrote in message ... I am trying to modularize my code, but I'm having trouble with one aspect. How do I make a variable such as a counter available within a macro that I just called? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Passing Variables to another macro
To retrieve the value of a variable from another routine you'll need to do two simple things: 1) Ensure the routine which has to retrun the variable value is a Function not a Sub. The primary difference between the two is that Functions are capable of returning a value to a calling routine while Subs are not. 2) At the end of the function assign the value of the variable to the name of the function. Here's an example: Sub Test() MsgBox LoopAndCount() End Sub Function LoopAndCount() As Long Dim i As Long Dim Total As Long For i = 1 To 100 Total = Total + i Next i LoopAndCount = Total End Function Note a couple of things. The function has a return type of Long which is the same data type as the variable I want to get the value of. The moral here is: whatever data type the variable is that you want to get the value of, make the function return the same data type. Let me know if you need a more specific example. Good Coding! OfficeHacker "Jeff" wrote: I am trying to modularize my code, but I'm having trouble with one aspect. How do I make a variable such as a counter available within a macro that I just called? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Passing Variables | Excel Discussion (Misc queries) | |||
Passing variables between a form and macro | New Users to Excel | |||
Passing variables between a form and macro | New Users to Excel | |||
Passing variables from Outlook Macro to Excel Macro | Excel Programming | |||
passing variables from an excel macro to a powerpoint macro | Excel Programming |