none
VBA - Select Case Statement - Array RRS feed

  • Question


  • Hi friends,

     

    I am trying to formulate a Select Case statement that will group word and numbers togther like an array maybe?

     

    I am working to make a ribbon so it has buttons , but there are a lot of them

     

    Case "aButton01": Size = Large

    Case "aButton02": Size = Large

    Case "aButton03": Size = Large

    Case "aButton04": Size = Large

    Case "aButton05": Size = Large

     

     

    Case "bButton01": Size = Large

    Case "bButton02": Size = Large

    Case "bButton03": Size = Large

    Case "bButton04": Size = Large

    Case "bButton05": Size = Large

     

     

    Case "aButton11": Size = Large

    Case "aButton12": Size = Large

    Case "aButton13": Size = Large

    Case "aButton14": Size = Large

    Case "aButton15": Size = Large

     

     

    Case "bButton11": Size = Large

    Case "bButton12": Size = Large

    Case "bButton13": Size = Large

    Case "bButton14": Size = Large

    Case "bButton15": Size = Large

     

     

     

    So I may reduce the statements above and have maybe

     

     

    Case "a" & Button & "Number": Size = Large

     

     

     

    Dim Number Select Case Number  

     

    Select Case control.ID

     

    Case 1 To 5    AND letters "a,b"

     

      Case 11 to 15    AND letters "a,b"  

     

    End Select

     

     

    I have researched and cant seem to formulate what the logic maybe if indeed anything

     

    Is this at all possible?


    Cheers Dan :)

    Monday, January 9, 2017 5:41 PM

Answers

  • and it said  - cannot convert the getSize attribute which is on the xml

    The Code below works.

    BTW, you can either use the Size attribute or the getSize callback in the XML code, you can not use both.

    BTW2, if your ribbon is within an AddIn, I recommend to use unique names for your callbacks.

    Otherwise you can have a conflict with another AddIn that uses the same callback names and Excel can call the wrong callback. I use GUIDs for my callback names.

    Andreas.

    Sub GetSize(Control As IRibbonControl, ByRef Size)
      Const Normal = 0
      Const Large = 1
      Dim Number As Integer
      'Match this mask?
      If Control.ID Like "[ab]Button##" Then
        'Get the number from the end
        Number = Right(Control.ID, 2)
        Select Case Number
          Case 1 To 5, 11 To 15
            Size = Large
          Case Else
            Size = Normal
        End Select
      End If
    End Sub


    • Marked as answer by Dan_CS Tuesday, January 10, 2017 12:37 PM
    Tuesday, January 10, 2017 11:02 AM

All replies

  • Sub Test()
      Dim ButtonName As String
      Dim Number As Integer
      Dim Size
      
      ButtonName = "aButton05"
      
      'Normal as default
      Size = "Normal"
      'Match this mask?
      If ButtonName Like "[ab]Button##" Then
        'Get the number
        Number = Right(ButtonName, 2)
        'From 1 to 5 and 11 to 15...
        Select Case Number
          Case 1 To 5, 11 To 15
            '...size is Large
            Size = "Large"
        End Select
      End If
    End Sub
    

    Monday, January 9, 2017 6:17 PM
  • Hello Andreas,

     

    Thank you for helping,

     

    Now before I make a mess of it  - is this on the right track

     

    The ribbon has a call back

    Sub GetSize(control As IRibbonControl, ByRef Size)
    
    
    'Const Large As Integer = 1
    'Const Small As Integer = 0
    
     Dim ButtonName As String
      Dim Number As Integer
      'Dim Size
    
      'Select Case control.ID
    
      ButtonName = "aButton05"
      Size = "Normal"
      
      If ButtonName Like "[ab]Button##" Then     'Match this mask?
    
        Number = Right(ButtonName, 2)
        
        Select Case Number
        Case 1 To 3, 7 To 10
           Size = "Large"   '...size is Large
        End Select
      End If
    End Sub

    and it said  - cannot convert the getSize attribute which is on the xml


    Cheers Dan :)

    Monday, January 9, 2017 6:55 PM
  • and it said  - cannot convert the getSize attribute which is on the xml

    The Code below works.

    BTW, you can either use the Size attribute or the getSize callback in the XML code, you can not use both.

    BTW2, if your ribbon is within an AddIn, I recommend to use unique names for your callbacks.

    Otherwise you can have a conflict with another AddIn that uses the same callback names and Excel can call the wrong callback. I use GUIDs for my callback names.

    Andreas.

    Sub GetSize(Control As IRibbonControl, ByRef Size)
      Const Normal = 0
      Const Large = 1
      Dim Number As Integer
      'Match this mask?
      If Control.ID Like "[ab]Button##" Then
        'Get the number from the end
        Number = Right(Control.ID, 2)
        Select Case Number
          Case 1 To 5, 11 To 15
            Size = Large
          Case Else
            Size = Normal
        End Select
      End If
    End Sub


    • Marked as answer by Dan_CS Tuesday, January 10, 2017 12:37 PM
    Tuesday, January 10, 2017 11:02 AM
  •  

    Andreas,

     

    Thank you so much!

     

    You are a hero :)

     

    I tried so many things yesterday, but my code kept getting rejected as I kept changing the xml, then the GetSize oh my well it was hard work but still nothing worked.

     

    This is awesome!

     

    I can cut down so much repetitive code

     

    Thank you and have a great day!!!


    Cheers Dan :)

    Tuesday, January 10, 2017 12:37 PM