How can I use enum identifiers in Excel UDF

Question

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:

enter image description here

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?

score 12 · Accepted Answer · answered Aug 25 '11 at 09:50

12

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"

answered Aug 25 '11 at 09:50

Gaijinhunter

14,587
4
51
57

4

Yeah, it's times like this that it becomes more clear that VBA and Excel are like 2 people living in the same apartment but in different rooms. They have to knock on each other's door to get stuff done. – Gaijinhunter Aug 25 '11 at 10:20

score 5 · Answer 2 · edited Dec 07 '13 at 11:44

5

The only way I see, is by defining Names in your workbook, assigning them the constants of your Enums.

From the menu: Insert ... Name .... Define
Name: v_gas
Refers to: =1

Alternatively, you could create those names by VBA, but that has no interest, since it will be a 1 shot (names are saved with the workbook).

By using such names, the users will be able to use F3 while entering the formula.

edited Dec 07 '13 at 11:44

brettdj

54,857
16
114
177

answered Aug 25 '11 at 09:47

iDevlop

24,841
11
90
149

Aha, we came to the same conclusion. +1 :) – Gaijinhunter Aug 25 '11 at 09:51
Thanks (I guess) this was the very thing I was trying to avoid, but I guess I could create these names in VBA in a `OnOpen` event and destroy them in de `OnClose` event. I want to be able to keep control over my names in the VBA code. – Johan Aug 25 '11 at 09:54

How can I use enum identifiers in Excel UDF

2 Answers2

Linked