How to set an arg list for my VBA functions

[shelfish]

I've written a function that I am using over and over again in the
rest of my code. One of the arguments is one of several acceptable
strings and I don't want to have to keep typing it with opportunity
for error. Ignoring error control practices, my question is, how can I
force VBE to give me a drop-down menu for my function like you would
see using, say, ".horizontalalignment =", where you would see xlLeft,
xlCenter, etc.

Many thanks for any assistance...and happy Memorial day.

S.

 

[Rick Rothstein \(MVP - VB\)]

You can use an Enum. Here is an example set up...

Enum MyDropDownList
Top = 1
AboveCenter
Center
BelowCenter
Bottom
End Enum

Function MyFunction(Arg1 As MyDropDownList)
Debug.Print Arg1
End Function

Now, inside your own subroutine or function, when you type MyFunction and
the opening parentheses, you will be presented with the list from the Enum
above. The way I set it up, Top=1, AboveCenter=2, Center=3, etc. You can,
however, assign any starting value to the first element of the Enum and the
rest, if not explicitly assigned, will take on the next consecutive value.
On the other hand, you can assign the equivalent value (not necessarily in
consecutive order) to the elements of the Enum line by line. For example...

Enum MyDropDownList
Top = 10
AboveCenter = 5
Center = 0
BelowCenter = -5
Bottom = -10
End Enum

Rick

 

[shelfish]

Many thanks. I'm getting a "User defined type not defined..." error.
Here's what I've got:

Public Enum thisLabelArg1
cumChartLabels = 1
monthlyCountChartLabels = 2
top5ChartLabels = 3
End Enum

Public Enum thisLabelArg2
Chart Title = 1
X_Axis_Label = 2
Primary_Axis_Label = 3
Secondary_Axis_Label = 4
End Enum

Function thisLabel(labelSeries As thisLabelArg1, labelType As
thisLabelArg2) As String ## Error on this line ##

If labelSeries = 1 Then Set thisChartLabel = cumChartLabels
ElseIf labelSeries = 2 Then Set thisChartLabel =
monthlyCountChartLabels
ElseIf labelSeries = 3 Then Set thisChartLabel =
top5ChartLabels
End If
If labelType = 1 Then findLabelType = "Chart Title"
ElseIf labelType = 2 Then findLabelType = "X Axis"
ElseIf labelType = 3 Then findLabelType = "Primary"
ElseIf labelType = 4 Then findLabelType = "Secondary"
End If

thisLabel = Range("'" & chartDataSheetName & "'!" &
thisChartLabel.Find(labelType).Offset(1, 0).Address).Value

End Function

thisLabel = Range("'" & chartDataSheetName & "'!" &
thisChartLabel.Find(labelType).Offset(1, 0).Address).Value

End Function

Sub...
....ChartTitle.Characters.Text = thisLabel(cumChartLabels, "Chart
Title")
End Sub

 

[shelfish]

I should note that the values being set for "thisChartLabel" are
predefined ranges. I commented out the IF statements in the function
which should bring be back to the point in which everything was
working fine, but I still get that error. Grr.

S.

 

[Rick Rothstein \(MVP - VB\)]

I see two problems with the code you posted. First, the elements of an Enum
must follow the same rules as for a variable name, so the space in your
first element of the thisLabelArg2 Enum cannot have a space in the middle of
it as you show. The second problem is in the 2nd argument for the thisLabel
function call shown here...

ChartTitle.Characters.Text = thisLabel(cumChartLabels, "Chart Title")

You are trying to pass "ChartTitle" as the 2nd argument; however,
"ChartTitle" is a String value, but your declaration for the 2nd argument is
a thisLabelArg2 Enum, which is not a String value. If you don't want to pick
the value from the drop down list (not sure why you wouldn't as you said in
your initial posting that is what you wanted to be able to do), you could
construct your function call this way...

ChartTitle.Characters.Text = thisLabel(cumChartLabels, _
thisLabelArg2.ChartTitle)

where I am assuming you are correcting your first mistake by simply removing
the internal space. You are also showing two End Function statements, but
I'm assuming that is from some bad Copy/Paste'ing. Also, while I don't think
it is contributing to any errors, I find your If-Then block construction
(especially without better indenting) to be very hard to read... I never use
one-line ElseIf statements like you showed. One final comment for when you
get this all working... inside your code, you do not have to use the 1, 2, 3
etc. equated values of your Enum elements, you can use EnumName.ElementName
instead. For example, instead of this...

If labelSeries = 1 Then

you could use this (more self-documenting) version instead

If labelSeries = thisLabelArg1.cumChartLabels Then

Rick