VBA Function argument list select

by

This is what is known as an enumeration. Here is a quick example:

Public Enum DayOfWeek
    Monday = 1
    Tuesday = 2
    Wednesday = 3
    Thursday = 4
    Friday = 5
    Saturday = 6
    Sunday = 7
End Enum

Public Function GetDrinkSpecial(day As DayOfWeek) As String

    Select Case day
        Case DayOfWeek.Monday
            GetDrinkSpecial = "$1 Tap Domestics"
        Case DayOfWeek.Tuesday
            GetDrinkSpecial = "2 for 1 Rail Mixers"
        Case DayOfWeek.Wednesday
            GetDrinkSpecial = "$2 You-Call-Its"
        Case DayOfWeek.Thursday
            GetDrinkSpecial = "$1 Bush Bottles"
        Case DayOfWeek.Friday
            GetDrinkSpecial = "$3 Greenies"
        Case DayOfWeek.Saturday
            GetDrinkSpecial = "No Specials, Doh!"
        Case DayOfWeek.Sunday
            GetDrinkSpecial = "No Specials, Doh!"
        Case Else
            GetDrinkSpecial = "No Specials, Doh!"
    End Select
End Function

Public Sub TestIt()

    MsgBox GetDrinkSpecial(Monday)
    MsgBox GetDrinkSpecial(Tuesday)
    MsgBox GetDrinkSpecial(Wednesday)
    MsgBox GetDrinkSpecial(Thursday)
    MsgBox GetDrinkSpecial(Friday)
    MsgBox GetDrinkSpecial(Saturday)
    MsgBox GetDrinkSpecial(Sunday)
End Sub

This will get the desired ‘Drop Down’ Effect you are looking for when calling the function within the VBA editor. However, if you were to call ‘GetDrinkSpecial’ from within an excel cell formula, you will not have access to the enumeration, and would need to specifically pass it the long value of the enumeration.

More Related Contents:

  1. Unprotect VBProject from VB code
  2. Assign event handlers to controls on user form created dynamically in VBA
  3. VBA: How to delete filtered rows in Excel?
  4. Round function in Excel, worksheet function vs VBA
  5. Excel VBA Userform – Execute Sub when something changes
  6. simple vba code gives me run time error 91 object variable or with block not set
  7. Excel VBA wildcard search
  8. Reading Web Pages using Excel VBA
  9. delete Autofilter Field resullt using the header Name
  10. VBA macro that search for file in multiple subfolders
  11. So, I have 6 “master” files to then divide into 40 separate files
  12. Getting ScriptControl to work with Excel 2010 x64
  13. Application.Match gives type mismatch
  14. Count and Highlight keywords within phrases
  15. Transpose a range in VBA
  16. Detect merged cells in VBA Excel with MergeArea
  17. Using VBA code, how to export Excel worksheets as image in Excel 2003?
  18. Closing Excel Application using VBA
  19. Parse HTML content in VBA
  20. How to add a DocumentProperty to CustomDocumentProperties in Excel?
  21. VBA EXCEL To Prompt User Response to Select Folder and Return the Path as String Variable [duplicate]
  22. Should I turn .CutCopyMode back on before exiting my sub procedure?
  23. How to use VBA SaveAs without closing calling workbook?
  24. Removing the password from a VBA project
  25. Removing special characters VBA Excel
  26. Excel VBA Formula German/French/Italian/Russian/Dutch/Foreign Function
  27. Return a range from A1 to the true last used cell
  28. How to add items to a combobox in a form in excel VBA?
  29. How do I avoid run-time error when a worksheet is protected in MS-Excel?
  30. VBA inheritance, analog of super

Leave a Comment