Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using (what goes in here) for sub routine
Was reading the Excel Help on the Sub Statement and this is the example that
is shown: ' Sub procedure definition. ' Sub procedure with two arguments. Sub SubComputeArea(Length, TheWidth) Dim Area As Double ' Declare local variable. If Length = 0 Or TheWidth = 0 Then ' If either argument = 0. Exit Sub ' Exit Sub immediately. End If Area = Length * TheWidth ' Calculate area of rectangle. Debug.Print Area ' Print Area to Debug window. End Sub I notice that the there is Lenght and TheWidth in the brackets after the sub name. Am I correct in assuming that these have to be given values somewhere? I notice in the sub routine they are not defined so I am assuming they are definned elsewhere. I am trying to learn about the arguments that can go in that set of brackets after the sub name to further my knowledge. I see things like Sub Name(ByVal etc...) and was trying to figure out the whens and whys of using that and where is that stuff defined as I cannot find it in the help when I do a search for ByVal etc.... Thanks in Advance and have a Happy New Year everyone Wally Steadman |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using (what goes in here) for sub routine
It is defined in the help for SUB
Sum Main() Dim dbllLen as double, dblWidth as double dblLen = 6 dblWidth = 8 SubComputeArea dblLen, dblWidth End Sub Sub SubComputeArea(Length, TheWidth) Dim Area As Double ' Declare local variable. If Length = 0 Or TheWidth = 0 Then ' If either argument = 0. Exit Sub ' Exit Sub immediately. End If Area = Length * TheWidth ' Calculate area of rectangle. Debug.Print Area ' Print Area to Debug window. End Sub If you know you are going to pass variables defined as double you can do Sub SubComputeArea(Length as double, TheWidth as double) Length and TheWidth are declared along with the Sub in the sub declaration (the line above) They are place holders for the variables actually passed to the sub. by default they are passes a references so if the sub does anything to the passed variables the values held by those variables is changed (you can explicitly do Sub SubComputeArea(byRef Length as double, byRef TheWidth as double) If you use ByVal, then the value held be the variables are passed and the function uses the placeholder variables (so to speak) to hold the passed values and those placeholder/dummy variables are manipulated in teh sub. Unlike the byRef variables, the original variable values are not affected by actions performed in the sub. -- Regards, Tom Ogilvy "Wally Steadman" wrote in message ... Was reading the Excel Help on the Sub Statement and this is the example that is shown: ' Sub procedure definition. ' Sub procedure with two arguments. Sub SubComputeArea(Length, TheWidth) Dim Area As Double ' Declare local variable. If Length = 0 Or TheWidth = 0 Then ' If either argument = 0. Exit Sub ' Exit Sub immediately. End If Area = Length * TheWidth ' Calculate area of rectangle. Debug.Print Area ' Print Area to Debug window. End Sub I notice that the there is Lenght and TheWidth in the brackets after the sub name. Am I correct in assuming that these have to be given values somewhere? I notice in the sub routine they are not defined so I am assuming they are definned elsewhere. I am trying to learn about the arguments that can go in that set of brackets after the sub name to further my knowledge. I see things like Sub Name(ByVal etc...) and was trying to figure out the whens and whys of using that and where is that stuff defined as I cannot find it in the help when I do a search for ByVal etc.... Thanks in Advance and have a Happy New Year everyone Wally Steadman |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using (what goes in here) for sub routine
ByVal literaly passes a copy of the variable instead of the variable itself.
As such there is some overhead involved in pass ByVal as a copy has to be made. That having been said unless I need to modify the value of the incoming variable I alway use by val. It is a lot safer and makes debugging easier. As strings can take a lot of processing to copy if you have a performance issue you may want to use byRef if you need to pass a lot of strings... HTH "Tom Ogilvy" wrote: It is defined in the help for SUB Sum Main() Dim dbllLen as double, dblWidth as double dblLen = 6 dblWidth = 8 SubComputeArea dblLen, dblWidth End Sub Sub SubComputeArea(Length, TheWidth) Dim Area As Double ' Declare local variable. If Length = 0 Or TheWidth = 0 Then ' If either argument = 0. Exit Sub ' Exit Sub immediately. End If Area = Length * TheWidth ' Calculate area of rectangle. Debug.Print Area ' Print Area to Debug window. End Sub If you know you are going to pass variables defined as double you can do Sub SubComputeArea(Length as double, TheWidth as double) Length and TheWidth are declared along with the Sub in the sub declaration (the line above) They are place holders for the variables actually passed to the sub. by default they are passes a references so if the sub does anything to the passed variables the values held by those variables is changed (you can explicitly do Sub SubComputeArea(byRef Length as double, byRef TheWidth as double) If you use ByVal, then the value held be the variables are passed and the function uses the placeholder variables (so to speak) to hold the passed values and those placeholder/dummy variables are manipulated in teh sub. Unlike the byRef variables, the original variable values are not affected by actions performed in the sub. -- Regards, Tom Ogilvy "Wally Steadman" wrote in message ... Was reading the Excel Help on the Sub Statement and this is the example that is shown: ' Sub procedure definition. ' Sub procedure with two arguments. Sub SubComputeArea(Length, TheWidth) Dim Area As Double ' Declare local variable. If Length = 0 Or TheWidth = 0 Then ' If either argument = 0. Exit Sub ' Exit Sub immediately. End If Area = Length * TheWidth ' Calculate area of rectangle. Debug.Print Area ' Print Area to Debug window. End Sub I notice that the there is Lenght and TheWidth in the brackets after the sub name. Am I correct in assuming that these have to be given values somewhere? I notice in the sub routine they are not defined so I am assuming they are definned elsewhere. I am trying to learn about the arguments that can go in that set of brackets after the sub name to further my knowledge. I see things like Sub Name(ByVal etc...) and was trying to figure out the whens and whys of using that and where is that stuff defined as I cannot find it in the help when I do a search for ByVal etc.... Thanks in Advance and have a Happy New Year everyone Wally Steadman |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sub routine | Excel Discussion (Misc queries) | |||
Sort Routine VBA | Excel Programming | |||
SUB ROUTINE | Excel Programming | |||
Routine?? | Excel Programming | |||
Need VBA Routine | Excel Programming |