Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to set an arg list for my VBA functions
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to set an arg list for my VBA functions
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" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to set an arg list for my VBA functions
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to set an arg list for my VBA functions
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to set an arg list for my VBA functions
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 "shelfish" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
List Functions | Excel Worksheet Functions | |||
Identify & List unique values from a list using functions/formulas | Excel Worksheet Functions | |||
Combining the IF and List functions | Excel Programming | |||
List of VBA Functions | Excel Programming | |||
List all Functions | Excel Programming |