CODEWITHSUNDEEP
excelvba
novawaly
novawaly

Reputation: 1251

VBA Code Question: Can I make GoTo be something variable?

I was just wondering if I could have my macro skip to a label that is variable? So something like the below (which doesn't work). The idea would be to generate a code based on the time and skip to that label in my code. So if the code is 900, i'd like it to skip to 900: and set x=1.

I hope this is clear enough. 

Sub test()

Dim skipcode As String

'generate the label name
    If Minute(Now) > 28 And Minute(Now) < 58 Then
        If Hour(Now) = 0 Then
           skipcode = "0030"
        Else
            skipcode = Hour(Now) & "30"
        End If
    Else
        If Hour(Now) = 0 Then
           skipcode = "000"
        Else
            skipcode = Hour(Now) & "00"
        End If
    End If

'attempting to get the macro to skip to the label
    GoTo skipcode


' desired skip point
900:
x = 1

1000:
x = 2

End Sub

Upvotes: 0

Views: 1646

Answers (1)

Maciej Los
Maciej Los

Reputation: 8591

You can do something like this:

Sub test()

    Dim skipcode As String
    Dim dt As Date

    dt = Now
    'i'd simplify your set of `if` - this way:
    skipcode = IIf(Minute(dt)>28 And Minute(dt)<58, Hour(dt) & "30", Hour(dt) & "00")

    'attempting to get the macro to skip to the label
    GoTo SkipPoint


' desired skip point
SkipPoint: 

    Select Case skipcode 
        Case "900" 'needs double quotes due to type of variable
            x = 1
        Case "1000"
            x = 2
    End Select


End Sub

[EDIT]

Accordingly to @Zack suggestion, a finall version of your procedure might look like:

Sub test()

    Dim skipcode As String
    Dim dt As Date

    dt = Now
    skipcode = IIf(Minute(dt)>28 And Minute(dt)<58, Hour(dt) & "30", Hour(dt) & "00")

    Select Case skipcode 
        Case "900" 'needs double quotes due to type of variable
            x = 1
        Case "1000"
            x = 2
    End Select

End Sub

Upvotes: 3

Related Questions