How can I use enum identifiers in Excel UDF
enums, excel, vba
Solution
If you add the enums to the workbook as defined names, you can pass them into functions and the value that is passed is the actual value you set the enum for. Do this manually or via VBA if you prefer.
Example:
ActiveWorkbook.Names.Add Name:="v_gas", RefersToR1C1:="=1"
ActiveWorkbook.Names.Add Name:="v_fixed", RefersToR1C1:="=2"
Problem
I've created a function called EnergyPrice with the following code: Here's the pricetable I'm looking up from ``` Prijstabel fixed variable Startdate Einddate gas electra gas electra €/a €/a ct/KWh ct/KWh 1-1-2010 1-7-2010 181,00 235,00 0,11 0,33 1-7-2010 1-1-2011 362,00 470,00 0,33 1,30 1-1-2011 1-7-2011 191,00 245,00 0,22 0,65 1-7-2011 1-1-2012 162,35 208,25 0,19 0,55 1-1-2012 1-7-2012 324,70 416,50 0,37 1,11 ``` And here's the relevant code ``` Public Enum Energietype v_gas = 1 v_electricity = 2 End Enum Public Enum FixedOrVariable v_fixed = 1 v_variable = 2 End Enum Public Function EnergyPrice(PriceDate As Date, E_type As Energietype, variabel As FixedOrVariable) As Variant Dim PrijsTable As Range Dim RowNr As Integer Dim Found As Boolean Dim KolomNr As Integer Set PrijsTable = Range("EnergyPriceTable") If PrijsTable.Columns.Count <> 6 Then Err.Raise Number:=vbObjectError + 1000, Description:="No valid valid pricetable defined" RowNr = 1 Found = False While Not (Found) And (RowNr <= PriceTable.Rows.Count) Found = (PriceTable.Cells(RowNr, 1).Value <= PriceDate) And (PriceTable.Cells(RowNr, 2) > PriceDate) If Not (Found) Then RowNr = RowNr + 1 Wend If Found Then If E_type = v_gas Then KolomNr = 1 If E_type = v_elektra Then KolomNr = 2 If variabel = v_variabel Then KolomNr = KolomNr * 2 KolomNr = KolomNr + 2 EnergyPrice = PriceTable.Cells(RowNr, KolomNr).Value Else EnergyPrice = Empty End If End Function ``` The question is how do I use the above enums in an Excel spreadsheet? So I can enter a formula like: If I use the numbers 1,2 the functions works fine, but I want to use the enum names. Can this be done using only Excel VBA?