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.
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.
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.
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.
[vba]
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
[/vba]
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.
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.
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.
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:
[vba]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
[/vba]
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.
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.
[VBA]
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
[/VBA]
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
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.
It will prompt you if you create your own enumeration:
[vba]
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
[/vba]
Reply With Quote
