Microsoft Office Tutorials and References
In Depth Information
Figure 25-1: Displaying a list of VBA functions in the VB Editor.
Here's a statement that calculates the square root of a variable by using VBA's Sqr function and then assigns the
result to a variable named x :
x = Sqr(MyValue)
Having knowledge of VBA functions can save you lots of work. For example, consider the REMOVESPACES
Function procedure presented at the beginning of this chapter. That function uses a For-Next loop to examine
each character in a string and builds a new string. A much simpler (and more efficient) version of that Function
procedure uses the VBA Replace function. The following is a rewritten version of the Function procedure:
Function REMOVESPACES2(cell) As String
‘ Removes all spaces from cell
REMOVESPACES2 = Replace(cell, “ “, “”)
You can use many (but not all) of Excel's worksheet functions in your VBA code. To use a worksheet function
in a VBA statement, just precede the function name with WorksheetFunction and a period.
The following code demonstrates how to use an Excel worksheet function in a VBA statement. The code snip-
pet uses the ENCODEURL function (which is new to Excel 2013) to encode a URL.
URL = “http://spreadsheetpage.com”
Encoded = WorksheetFunction.ENCODEURL(URL)
For some reason, you can't use worksheet functions that have an equivalent VBA function. For example, VBA
can't access Excel's SQRT worksheet function because VBA has its own version of that function: Sqr. There-
fore, the following statement generates an error: