I'm building a pretty complicated VBA workbook, and one of the issues on running much of the code is performance. I have a built in function that does, more or less, the following
Public Function zzz (xxx as String) as String
if xxx = "apple" then zzz = "orange"
if xxx = "appple2" then zzz = "orange2"
if xxx = "apple3" then zzz = "apple3"
etc. (but with about 30 strings instead). I call this function multiple times. Is there a better way to do this?
You could make your function using combination of arrays and WorksheetFunction.Match. This would go this way:
Public Function zzz(xxx As String) As String
Dim funInput As Variant
Dim funOutput As Variant
funInput = Array("apple", "apple2", "apple3")
'above array- add additional input elements and...
'...match them with resulting items in below array
funOutput = Array("orange", "orange2", "apple3") '
zzz = funOutput(Application.Match(xxx, funInput, 0) - 1)
End Function
Sample call of the function in VBA:
Debug.Print zzz("apple2")
will result with:
orange2
Application.Match to index an array. Innovative. Like it.Hardly. And for 30 strings, this shouldn't be terribly slow.
The performance issues are likely to be in another place, especially where you interact with the workbook directly. Try to measure times of various procedures before randomly trying to update pieces of code.
A lot of lines doesn't mean slow performance. Not every line takes the same amount of time to execute.
You could try using Select Case, if you are only checking the value of one variable. That will be faster, because it will skip the remaining lines once it finds the correct variable.
Select case xxx
case "apple"
zzz = "orange"
case "apple2"
zzz = "orange2"
case "apple3"
zzz = "orange3"
End Select
vlookup()for this.if elseat the least.