|
Calling SpeedTools functions from VBA
The easiest method of calling the SpeedTools functions from VBA is to use Application.Run.
It will convert your function parameters to an appropriate type as required.
The only major drawback to Application.Run is that the parameters are handled By Value as opposed to By Reference, which means that each parameter is copied before being passed to the function. This is fine for scalar values and objects such as a Range object but is slow for arrays (objects get passed as pointers so the function has to know how to handle whatever data structure or object the pointer points to).
You can pass Application.Run either the name of the function/sub as a string or as a Register ID if its an XLL function. And you can use Evaluate to convert the name of the XLL function to its Register ID, by calling Evaluate(“FunctionName”) rather than Evaluate(“FunctionName()”).
If you are repeatedly calling the function using Application.Run its much faster to use the Register ID rather than the function name.
Sub TestRun()
Dim var As Variant
Dim rng As Range
Dim dArray(1 To 10) As Double
Dim j As Long
''' using a string
var = Application.Run("Reverse.Text", "Charles")
Set rng = ActiveSheet.Range("B9:B20") ''' using a range
var = Application.Run("Reverse.array", rng)
''' using a double array
For j = 1 To 10
dArray(j) = j
Next j
var = Application.Run("Reverse.array", dArray)
''' multiple parameter types
Dim str1 As Variant
str1 = Application.Run("Rgx.Substitute", "123456ABC", "[0-9]", "Z", 0, False)
MsgBox str1(1) ''' Returns a 1-dimensional variant
''' using a register ID for the function: faster if large number of calls to the function
Dim jFunc As Long
jFunc = Evaluate("Rgx.Substitute")
str1 = Application.Run(jFunc, "123456ABC", "[0-9]", "Z", 0, False)
MsgBox str1(1)
End Sub
|
