PDA

View Full Version : Solved: Question about User Defined Functions



nameuser321
03-22-2012, 05:57 AM
This is really just a question of convenience. I've noticed that with user defined functions, when working in an excel spreadsheet, excel will not show you it's argument variable names. Is there a way to get excel to do this behaviour?

Another question that is kind of related to the question above is, how do I get the vba editor and actually in excel, when typing a function inside of vba to pop up user defined constants, similar to excels xldown, xltoleft and also to pop up the a user defined formula's variables similar to match's ([match_type], 0 - equal to, 1 -more than....), respectively.

Any help would be greatly appreciated.

JKwan
03-22-2012, 07:17 AM
Type in your function up to "(", then hit Control a OR control Shift a, take your pick

nameuser321
03-22-2012, 07:21 AM
Sorry, I don't understand your answer. Is this in reference to user defined formulas on a worksheet cell or in vba. I tried in both and it didn't seem to do anything.

JKwan
03-22-2012, 07:25 AM
That was related to your first inquiry - User Define Functions. So, if you created a function called "ABC", then in your cell, you typed "=ABC(", then hit the series of keys that I described will give you the arguements.

nameuser321
03-22-2012, 07:36 AM
Thanks for your quick reply jkwan. Unfortunately this method doesn't seem to be working for me. I might add that I am working in Excel 2007. Here is some code I'm trying to get to work.

This is my function.


Function TestThisCode(ParamArray Strings() As Variant) As Variant
Dim index As Integer
Dim interString As String

For index = LBound(Strings) To UBound(Strings)
interString = interString & " " & Strings(index)
Next index

TestThisCode = interString
End Function


I have no constants defined, which would relate to my second question. With any other formulas in the excel spreadsheet, excel will have a little popup underneath my the formula bar that will list the variables required for this function to work. For a UDF it will not, leaving the end-user confused as to what is required, and for complex formulas which argument he/she is currently on. I just want excel to popup with a note that tells the end user this additional information.

nameuser321
03-22-2012, 07:42 AM
Along with this. I want excel in the vbeditor to prompt me when i'm writing a function of constants defined in a module or something. I'm not quite sure how this works, but i see it when you type in a module name, the editor will list all functions inside that method. I want something similar to this but with arguments both in the editor and the spreadsheet. some functions, the user in the editor will be prompted of some suggestions such as xldown, xltoright, etc... Also in the spreadsheets such as with the match formula ([match_type], 0 - equal to, 1 -more than....). Hopefully this clears up confusion in the way that I may have written my first post. Thanks.

Aflatoon
03-22-2012, 07:44 AM
There is no way to get the tooltips when using a UDF in a worksheet cell, regrettably. You can however get the arguments added to the Function Wizard if that is of any use.

JKwan
03-22-2012, 08:13 AM
This is what you get, when you hit Control A. This is done by Excel 2003.

nameuser321
03-22-2012, 09:15 AM
So I guess what I was looking for was the tooltips, but JKwan's solution is pretty good. Anything on the other part?

Kenneth Hobs
03-22-2012, 09:39 AM
I don't know what the other part was about. The constants are part of the popup so no, a custom UDF can not do that.

Method 1 uses Application.MacroOptions. It is limited to about 3 lines or 255 characters. Type =DisRangeCountIf( and press Shift+F3 to get the help once a routine like that below is added. Run the AddDesc routine to get it listed. Of course you can type = and the UDF name and Ctrl+A to open the help as JKwan said. In a Module:
Sub AddDesc_DisRangeCountIf()
Application.MacroOptions Macro:="DisRangeCountIf", _
Description:="Used like built-in function,=CountIf" & vbCrLf _
& "DisRange: Contiguous or Discontiguous range." _
& vbCrLf & "sCriteria: A string for the criteria.", _
Category:="Kens Functions"
End Sub

Function DisRangeCountIf(ByVal DisRange As Range, ByVal sCriteria As String)
Dim dblCount As Double
dblCount = 0
For Each cell In DisRange.Cells
dblCount = dblCount + WorksheetFunction.CountIf(cell, sCriteria)
Next
DisRangeCountIf = dblCount
End Function

Method 2 is much more robust but takes much more effort to understand and use. Google to find the RegisterUDF6.xls example or CudfHelper. This method gives you the extended help like that above but with more lines and lets you define each parameter as Excel's help displays with standard functions using Shift+F3 or Ctrl+A.

nameuser321
03-23-2012, 07:40 AM
Thank you guys for all the assistance. The combination of all of your suggestions have gotten me pretty close to what I wanted. My last question has to do with the VBA editor. Here's some code.


Option Explicit
Public Const UDFirstName As Integer = 1
Public Const UDLastName As Integer = 2
Function GetNamePart(wholename As String, aOption As Integer)
Dim interstring() As String
interstring = Split(wholename, " ")

Select Case aOption
Case UDFirstName
GetNamePart = interstring(0)
Case UDLastName
GetNamePart = interstring(1)
Case Default
MsgBox "Invalid Option"
End Select
End Function
Sub test()
MsgBox GetNamePart("Bill Paxton", UDFirstName)
End Sub


Question: Is there a way to have the editor prompt me of my options when i get to the aOption variable in the test sub of the getnamepart function? Sorry for being so persistent. I'm thinking not, but it would be nice.. Thanks

Kenneth Hobs
03-23-2012, 07:51 AM
No, the VBE does not prompt for things like that.

If you want to code a Sub to use a prompt of some sort like an InputBox() or Application.InputBox() or one of several other methods, that is easy enough.

Aflatoon
03-23-2012, 08:09 AM
It will prompt you if you create your own enumeration:


Public Enum NameType
UDFirstName = 1
UDLastName = 2
End Enum
Function GetNamePart(wholename As String, aOption As NameType)
Dim interstring() As String
interstring = Split(wholename, " ")

Select Case aOption
Case UDFirstName
GetNamePart = interstring(0)
Case UDLastName
GetNamePart = interstring(1)
Case Default
MsgBox "Invalid Option"
End Select
End Function
Sub test()
MsgBox GetNamePart("Bill Paxton", UDFirstName)
End Sub

nameuser321
03-23-2012, 01:39 PM
Thanks for answering all my questions guys. Ya'll have been a great help.