PDA

View Full Version : function variables show on sheet



terdfurg
09-20-2011, 03:39 PM
I am new to VBA and I hope I can explain this properly. I wrote a function: isValidISBN and it will show up on the scroll menu if i select a cell and start typing isVa... in the same way that =sum, and =vlookup come up. I was wondering if there was a way to display the needed variables. For example =sumif(range,criteria,[sum_range]). I want the "range,criteria,[sum_range]" equivalents of my function to show. As of now it just says "isValidISBN("
Is there a way to do this in VBA?

Kenneth Hobs
09-20-2011, 06:18 PM
No. There are ways to give help for your function as if Fx was pressed.

terdfurg
09-21-2011, 08:28 AM
No. There are ways to give help for your function as if Fx was pressed.

Thanks. How is that done?

Kenneth Hobs
09-21-2011, 09:45 AM
There are some involved methods that lets you add more help than the more simple method of Applicaton.MacroOptions.

JKwan
09-21-2011, 09:50 AM
you could try something like this, type your formula then hit (for example "=sum")
1 - Control A
2 - Control Shift A

Take your pick

Kenneth Hobs
09-21-2011, 11:06 AM
As JKwan said, you can use option 2. What you can not do is to add the popup as standard functions like =Sum().

For the help by method 1, add it by Application.MacroOptions.

e.g.
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