General VB Question 1

[stephenj789]

Although I am not new to Visual Basic, there are a few basics that I don't yet understand, specifically passing off variables (arguments) to other functions/subroutines. I wondered if someone could post some simple Excel VBA code that I could plunk into the vb editor, that would show me (I could change values, etc.) the following:

1.The difference between a sub and function.
2.The difference between ByVal and ByRef
3.The concept of passing an argument (variable) to another routine.
4.The difference between an argument or variable (if any).

I have tried creating the VBA code myself, but it always comes back with run time errors, hence my confusion on the subject. The sheet object I would like to be named 'Sheet1' and the workbook object to be 'book1'. Thanks for your help.

 

[ETID]

A Function passes the result of your code to the function name: a sub does not

paste this into a module:

function my_value(incell_1,incell_2)
my_value = incell_1 * incell_2
end function

my_value is the function name.

then in a spreadsheet type = my_value(A1,b1)
assuming a1 and b1 have numbers in them

---------------------

The way I pass a variable to another sub or function is to declare it public outside the sub (at the top of the module)

ie:

public my_value as long

then you reference that variable from any sub or function.

..it's like the dim statement but outside the sub

----------------------


Arguments are the whole string of variables and operators...

like

x=(my_value * 2)^2

....1 variable (my_value), 2 constants "2" and 2 operators " * " & " ^ " equals the argument for x
*********************************************************

I'm sure someone will post more elegant ways of the same stuff

 

[Zathras]

All of what you ask is in the help file. Please take time and get familiar with it. Here is a brief set of answers to your questions. There are many more keywords and variations that I have glossed over.

1.The difference between a sub and function.
2.The difference between ByVal and ByRef
3.The concept of passing an argument (variable) to another routine.
4.The difference between an argument or variable (if any).

1., 2., and 3. See code samples below.

4. An argument is usually a variable, although it may be a constant in the calling routine. Even when a constant is passed in an argument list, the called routine can use it the same as a variable. A variable is a named memory location that contains data that can be accessed and can be changed.
========================================================

Option Explicit

Sub ThisIsASub()

  MsgBox "This is a Sub" + vbNewLine _
  + "You can see it in the list of Macros" + vbNewLine _
  + "because it has no parameters." + vbNewLine _
  + "(Tools/Macro/Macros...)" + vbNewLine _
  + "It has no ""Return Value"""
    
End Sub

Sub ThisIsASubWithAParameter(InputParameter As String)

  MsgBox "This is a Sub" + vbNewLine _
  + "You can NOT see it in the list of Macros" + vbNewLine _
  + "because it has a parameter." + vbNewLine _
  + "(Tools/Macro/Macros...)" + vbNewLine _
  + "It has no ""Return Value""" + vbNewLine _
  + "The parameter is: " & InputParameter

End Sub

Sub TestASub()

  MsgBox "This is also a Sub" + vbNewLine _
  + "You can see it in the list of Macros" + vbNewLine _
  + "because it has no parameters." + vbNewLine _
  + "(Tools/Macro/Macros...)" + vbNewLine _
  + "It has no ""Return Value""" + vbNewLine _
  + "Calling Sub ""ThisIsASubWithAParameter""..."
  ThisIsASubWithAParameter "Sheet333"
End Sub

Function DoubleANumber(ANumber As Integer) As Integer
  
  DoubleANumber = 2 * ANumber
  MsgBox "This is a Function" + vbNewLine _
  + "You can see it in the list of User-Defined" + vbNewLine _
  + "functions." + vbNewLine _
  + "(Insert/Function/User Defined)" + vbNewLine _
  + "It has a ""Return Value""" + vbNewLine _
  + "Use like any Excel function." + vbNewLine _
  + "e.g., in C1: ""=A1+DoubleANumber(B1)-1" + vbNewLine _
  + "It can also be used in VBA."
  
End Function


Sub DemoByValAndByRef()
Dim nPrice As Double
Dim nQuantity As Integer
Dim nCost As Double
   
   nPrice = 4.52
   nQuantity = 10
   
   MsgBox "Price = " & nPrice & vbNewLine _
      + "Quantity = " & nQuantity
      
   nCost = CalculateCost(nPrice, nQuantity)
   
    MsgBox "Price = " & nPrice & vbNewLine _
      + "Quantity = " & nQuantity & vbNewLine _
      + "Cost = " & nCost & " (used a different Quantity)"
  
End Sub

Function CalculateCost(Price As Double, ByVal Quantity As Integer) As Double
' Try to change both Price and Quantity.
' We can change both, but only the new Price will "take"
' ByVal gives this routine a copy of the variable
' ByRef gives this routine a pointer to the variable
' (ByRef is the default)
  Price = Price * 1.1
  Quantity = Quantity * 2
  CalculateCost = Price * Quantity
End Function