VBA if statement with quotient serial logic condition

Question

The following code is okey.

Dim myNumber As Long
If myNumber = 4 Or myNumber = 7 Or myNumber = 10 Or myNumber = 13 Or myNumber = 16 Or myNumber = 19 Or myNumber = 22 Then
    MsgBox "Hello"
End If

How to shorten the above code based on this serial logic?

4, 4+3, 4+3+3, 4+3+3+3, 4+3+3+3+3, 4+3+3+3+3+3, so on and so forth.

Answer 1

You can use the Mod-operator to see if your number is divisible by 3 with a rest of 0 if you subtract 4 first.

Sub test()
    Dim myNumber As Long
    myNumber = 4 'or however you want to fill your variable
    If (myNumber - 4) Mod 3 = 0 Then
        MsgBox "Hello"
    End If
End Sub

If you are expecting numbers smaller than 4 as well, you can make a small adjustment as sarh suggested:

Sub test()
    Dim myNumber As Long
    myNumber = 4 'or however you want to fill your variable
    If (IsMyNumber(myNumber) - 4) Mod 3 = 0 Then
        MsgBox "Hello"
    End If
End Sub

Function IsMyNumber(myNumb As Long) As Long
    IsMyNumber = IIf(myNumb < 4, 5, myNumb)
End Function

The function makes any number below 4 incorrect by setting it to 5 (5-4 = 1 mod 3 <> 0)

If you'd want to use it in different occasions as well, here's a more flexible function:

Sub test()
    Dim myNumb As Long
    myNumb = 9
    If IsMyNumber(myNumb, 4, 3) Then
        MsgBox "Hello"
    End If
End Sub

Function IsMyNumber(myNumb As Long, base As Long, addRep As Long) As Boolean
    IsMyNumber = myNumb >= base And (myNumb - base) Mod addRep = 0
End Function