VBA Functions

1
VBA Functions

• Functions
• Usually accept arguments
• Perform a calculation using the argument
• Return a value or string

2
VBA Functions
Consider Rad(50) Rad( ) is a function that
converts degrees to radians The number 50 is the
argument The function multiplies the argument by
p, then divides by 180. The function returns
the value 0.8727 Used in code MyNum Rad(50)
stores the value 0.8727 in the variable MyNum
3
Custom Functions
You can write your own functions in VBA Function
FuncName(arguments) Code defines how the
arguments are used.
This function accepts text as AnyName It
returns a text string withHi appended to the
stringpassed in the argument. Note the name of
the function must appear in the code
Function SayHi(AnyName) SayHi Hi,
AnyName End Function
4
Calling Functions from Macros

• This macro stores the name Fred in the variable
  mName.

Sub AddHi( ) mName Fred mOut
SayHi(mName) MsgBox mOut End Sub

• It passes the name to SayHi
• Note the passed argumentneed not have the
  samename as the function
• The macro then uses MsgBox to display
  theconverted string

Function SayHi(AnyName) SayHi Hi,
AnyName End Function
5
Finding data with VBA

• Vlookup(Value, Array, Column, Close)
• Searches down the first column of Array for a
  match to Value.
• Returns the value in the cell in Column for the
  same row with the match for Value.
• If Close true, it finds the best matchIf
  Close false, it only accepts an exact match.
• VBA doesnt have this function!

6
Finding Data in VBA

• Application.WorksheetFunction.VLookup( )
• Uses Excel function, but returns data to VBA
• Cell ranges must look like Range(A3A15)
• Range(Array).Find(Value)
• Returns a range variable defining where in Array
  it found that value
• Use .Offset(0,Column).Formula to find