How do I send a string of numbers to subroutines in a macro?

[Zenwood]

In my code I have six different subroutines.
I want to say run #2 , then run #4 , then run #4 , then run#6 etc.
2,4,4,6,1,2,2,3,4,4,5,5,6,6,1,1,2,2 is the sequence

What code should I use?

Sub Direction_6()


Dim Direction As String
Dim DirectionName As String




Direction = 2
' How to feed a string of numbers?
' (2,4,4,6,1,2,2,3,4,4,5,5,6,6,1,1,2,2)


'-------------------------------------


If Direction = 1 Then
DirectionName = "North East"
MsgBox (DirectionName)
End If


If Direction = 2 Then
DirectionName = "South East"
MsgBox (DirectionName)
End If


If Direction = 3 Then
DirectionName = "South"
MsgBox (DirectionName)
End If


If Direction = 4 Then
DirectionName = "South West"
MsgBox (DirectionName)
End If


If Direction = 5 Then
DirectionName = "North West"
MsgBox (DirectionName)
End If


If Direction = 6 Then
DirectionName = "North"
MsgBox (DirectionName)
End If


End Sub

 

[Joe4]

You can store your sequence in an array, and then iterate/loop through that list.

Here is a little example:

Sub test()

    Dim myList As Variant
    Dim i As Long
    
    myList = Array(2, 4, 4, 6, 1, 2, 2, 3, 4, 4, 5, 5, 6, 6, 1, 1, 2, 2)
    
    For i = LBound(myList) To UBound(myList)
        MsgBox myList(i)
    Next i

End Sub

 

[Zenwood]

Thanks, this helps but how do I get the answers to be the directions in stead of the numbers? (instead of 2 I want "South East")

 

[Joe4]

Is this what you are looking for?

Sub test()

    Dim myList As Variant
    Dim i As Long
    Dim DirectionName As String
    Dim myOutput As String
    
    myList = Array(2, 4, 4, 6, 1, 2, 2, 3, 4, 4, 5, 5, 6, 6, 1, 1, 2, 2)
    
    myOutput = "Run "
    
    For i = LBound(myList) To UBound(myList)
        Select Case myList(i)
            Case 1
                DirectionName = "North East"
            Case 2
                DirectionName = "South East"
            Case 3
                DirectionName = "South"
            Case 4
                DirectionName = "South West"
            Case 5
                DirectionName = "North West"
            Case 6
                DirectionName = "North"
         End Select
         myOutput = myOutput & DirectionName & ", then run "
    Next i

    MsgBox Left(myOutput, Len(myOutput) - 11)

End Sub

 

[Zenwood]

Yes. That works the way I had hoped. Thanks for your help.

 

[Joe4]

You are welcome.

I hope it all makes sense what I did. Let me know if you have any questions about it,

 

[Zenwood]

Yes it makes sense. Thanks again.

This is my update code so far.

Sub Direction_760()


Dim StartAcross As Integer
Dim StartDown As Integer
Dim Identity As Integer


Dim myList As Variant
Dim Looper As Long


Dim Red As Integer
Dim Green As Integer
Dim Blue As Integer


StartAcross = 400
StartDown = 400


Identity = 1


Red = 128
Green = 128
Blue = 128


'    Seed
ActiveSheet.Shapes.AddShape(msoShapeHexagon, StartAcross, StartDown, 100, 100).Select
Selection.ShapeRange.Fill.ForeColor.RGB = RGB(Red, Green, Blue)
Selection.ShapeRange(1).TextFrame2.TextRange.Characters.Text = Identity
'Selection.ShapeRange(1).TextFrame2.TextRange.Characters.Text = Format(Red, "000") & " " & Format(Green, "000") & " " & Format(Blue, "000")
         
'myList = Array(1, 3, 4, 5, 6, 1)'    7 Hexes
'myList = Array(1, 3, 4, 5, 6, 1, 1, 2, 3, 3, 4, 4, 5, 5, 6, 6, 1, 1) '    19 Hexes
'myList = Array(1, 3, 4, 5, 6, 1, 1, 2, 3, 3, 4, 4, 5, 5, 6, 6, 1, 1, 1, 2, 2, 3, 3, 3, 4, 4, 4, 5, 5, 5, 6, 6, 6, 1, 1, 1) '    37 Hexes
myList = Array(1, 3, 4, 5, 6, 1, 1, 2, 3, 3, 4, 4, 5, 5, 6, 6, 1, 1, 1, 2, 2, 3, 3, 3, 4, 4, 4, 5, 5, 5, 6, 6, 6, 1, 1, 1, 1, 2, 2, 2, 3, 3, 3, 3, 4, 4, 4, 4, 5, 5, 5, 5, 6, 6, 6, 6, 1, 1, 1, 1) '     Hexes


For Looper = LBound(myList) To UBound(myList)
Select Case myList(Looper)


Case 1
'    NE
StartAcross = StartAcross + 75
StartDown = StartDown - 50
Identity = Identity + 1
ActiveSheet.Shapes.AddShape(msoShapeHexagon, StartAcross, StartDown, 100, 100).Select
Selection.ShapeRange.Fill.ForeColor.RGB = RGB(100, 100, 100)
Selection.ShapeRange(1).TextFrame2.TextRange.Characters.Text = Identity


Case 2
'    SE
StartAcross = StartAcross + 75
StartDown = StartDown + 50
Identity = Identity + 1
ActiveSheet.Shapes.AddShape(msoShapeHexagon, StartAcross, StartDown, 100, 100).Select
Selection.ShapeRange.Fill.ForeColor.RGB = RGB(125, 125, 125) ' Added color
Selection.ShapeRange(1).TextFrame2.TextRange.Characters.Text = Identity


Case 3
'    SS
StartAcross = StartAcross + 0
StartDown = StartDown + 100
Identity = Identity + 1
ActiveSheet.Shapes.AddShape(msoShapeHexagon, StartAcross, StartDown, 100, 100).Select
Selection.ShapeRange.Fill.ForeColor.RGB = RGB(150, 150, 150)
Selection.ShapeRange(1).TextFrame2.TextRange.Characters.Text = Identity


Case 4
'    SW
StartAcross = StartAcross - 75
StartDown = StartDown + 50
Identity = Identity + 1
ActiveSheet.Shapes.AddShape(msoShapeHexagon, StartAcross, StartDown, 100, 100).Select
Selection.ShapeRange.Fill.ForeColor.RGB = RGB(175, 175, 175)
Selection.ShapeRange(1).TextFrame2.TextRange.Characters.Text = Identity


Case 5
'    NW
StartAcross = StartAcross - 75
StartDown = StartDown - 50
Identity = Identity + 1
ActiveSheet.Shapes.AddShape(msoShapeHexagon, StartAcross, StartDown, 100, 100).Select
Selection.ShapeRange.Fill.ForeColor.RGB = RGB(200, 200, 200)
Selection.ShapeRange(1).TextFrame2.TextRange.Characters.Text = Identity


Case 6
'    NN
StartAcross = StartAcross + 0
StartDown = StartDown - 100
Identity = Identity + 1
ActiveSheet.Shapes.AddShape(msoShapeHexagon, StartAcross, StartDown, 100, 100).Select
Selection.ShapeRange.Fill.ForeColor.RGB = RGB(225, 225, 225)
Selection.ShapeRange(1).TextFrame2.TextRange.Characters.Text = Identity


End Select


Next Looper


ActiveSheet.Shapes.SelectAll


Selection.ShapeRange.TextFrame2.TextRange.ParagraphFormat.Alignment = msoAlignCenter
Selection.ShapeRange.TextFrame2.VerticalAnchor = msoAnchorMiddle
Selection.ShapeRange.TextFrame2.TextRange.Font.Name = "'+mn-lt"
Selection.ShapeRange.TextFrame2.TextRange.Font.Size = 15


With Selection.ShapeRange.TextFrame2.TextRange.Font.Fill
.ForeColor.RGB = RGB(80, 80, 80)
End With


    Range("A1").Select


End Sub