CODEWITHSUNDEEP
excelvba
Clauric
Clauric

Reputation: 1896

Faster way to use If/Else conditions

I have up to 40 If/ElseIf conditions, where the code is:

   If b = 1 Then
       d = XXXX
    ElseIf b = 2 Then
       d = YYYY
    ElseIf b = 3 Then
       d = AAAA
    ElseIf b = 40 Then
       d = ZZZZ
    End If

Is there a faster way of doing this, so that I don't have all the If/ElseIf conditions?

Upvotes: 0

Views: 111

Answers (3)

A.S.H
A.S.H

Reputation: 29362

The best approach is to index through some array. In order to avoid creating the array each time you need it, you can declare it with the keyword Static. This way, indexing is very fast (especially for larger arrays). It's kind of a space-time tradeoff: the array keeps present in memory but indexing it is extremely fast.

In the following, I create a function that uses a static array to convert an index to a String. Note, however, that you might be interested in making it more dynamic by putting the strings in some hidden worksheet and load them from there.

Function myString(ByVal index As Long) As String
    Static ar As Variant ' <-- static: filled once and keeps valid between function calls
    If IsEmpty(ar) Then ar = Array("XXXX", "YYYY", "AAAA", "ZZZZ")
    myString = ar(index - 1)
End Function

Sub TestIt()
  Debug.Print myString(1)
  Debug.Print myString(4)
End Sub

Upvotes: 1

user3005775
user3005775

Reputation: 306

use an array

remember that arrays are by default 0 indexed, although you can override that and use any indexes you want when you redim the array. 

Dim MyArray()
Dim ind As Long
MyArray = Array("XXXX","YYYY","AAAA","ZZZZ")
ind = 2
MsgBox MyArray(ind)

this will return the 3rd element or AAAA

just change ind to whatever element you want to return

Upvotes: 1

Andreas
Andreas

Reputation: 23968

Maybe use an array?

So arr(1) = XXXX and arr(2) = YYYY

That way you can 

d = arr(b)

Upvotes: 1

Related Questions