Microsoft Office Tutorials and References
In Depth Information
Function Examples
The following example calls the User function and uses the return value as an argument for the
MsgBox statement. The concatenation operator ( & ) joins the literal string with the result of the
User function.
Sub ShowUser()
MsgBox “Your name is “ & User()
End Sub
The User function demonstrates how you can create a wrapper function that simply returns a
property or the result of a VBA function. Following are three additional wrapper functions that
take no argument:
Function ExcelDir() As String
‘ Returns the directory in which Excel is installed
ExcelDir = Application.Path
End Function
Function SheetCount()
‘ Returns the number of sheets in the workbook
SheetCount = Application.Caller.Parent.Parent.Sheets.Count
End Function
Function SheetName()
‘ Returns the name of the worksheet
SheetName = Application.Caller.Parent.Name
End Function
Here’s another example of a function that doesn’t take an argument. I used to use Excel’s RAND
function to quickly fill a range of cells with values. But I didn’t like the fact that the random
numbers changed whenever the worksheet was recalculated. So I remedied this issue by converting
the formulas to values.
Then I realized that I could create a custom function that returned random numbers that didn’t
change. I used the VBA built-in Rnd function, which returns a random number between 0 and 1.
The custom function is as follows:
Function StaticRand()
‘ Returns a random number that doesn’t
‘ change when recalculated
StaticRand = Rnd()
End Function
If you want to generate a series of random integers between 0 and 1,000, you can use a formula
such as this:
=INT(StaticRand()*1000)
 
Search JabSto ::




Custom Search