#8 :: Spreadsheet programming basics
Do you have a comment or question about a topic in this session? – Then post a question / comment and share the answer with other participants. All questions will be answered by the Administrator, Ian O’Connor and posted on this page. Follow up questions / comments are welcome.
Topics
- Functions: built-in and custom
- Custom functions (1)
- VBA arrays
- Custom function (2)
Comment and Reply
Please consider the following points before submitting a question or comment.
- When you post a question, the question should be about ONE distinct topic from the session – see points 1 to 4 above. Clarity is important
- Your name will be published when the question is approved. Your email address will NOT be made public
- Because this is a http site
Topic: Custom functions
UDF argument names: Is there a way to display the argument names for an Excel User-Defined function, like those displayed for a WS built in function?
Built in functions
1. Built in WS functions have a Function ScreenTips feature (figure 1)
2. ScreenTips are associated with either the cell or formula bar, depending upon where you start typing the formula
3. ScreenTips can be moved. Left click and drag the ScreenTip box when the mouse pointer is a white pointed cross
4. If ScreenTips are not visible, then change the settings in File > Options > Advanced, Display section, tick Show function ScreenTips
User defined functions
1. Function ScreenTips are not available with UDF on the WS (but are displayed in the VBE)
2. A work around is to use the Ctrl + Shft + A key sequence in the WS (figure 2)
Concerning the colours in figure 2.
The colours are due to the argument names being the same as the defined names in rows 4 to 8. If these defined names did not exist, then the arguments would appear in black!
Topic: Custom functions
In the user defined function part, it is shown that xlfsum3 can be used for non-contiguous ranges through union operator. However, this cannot work on my computer. The use of xlfSum3((P4:R4,T4:T6)) just returns the sum of P4:R4, not the total sum of (P4:R4,T4:T6).
The page has been updated (30 May 2018) xlf-udf.php. The example relating to non-contiguous range objects should be xlfSum5. xlfSum5(C15:E17, F15:F17) now returns 806.