xlf DISCUSSION board #8

5 Replies

#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

  1. Functions: built-in and custom
  2. Custom functions (1)
  3. VBA arrays
  4. 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

5 thoughts on “xlf DISCUSSION board #8”

  1. Guiding the way

    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?

    Reply ↓
    1. xlf ian Post author

      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

      function-argument-names
      Fig 1: WS function ScreenTips are independent of the formula. The current argument is shown in bold. Optional arguments have [] brackets

      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)

      xlf-udf-ctrl-shft-a
      Fig 2: UDF Ctrl + Shft + A ScreenTips. The ScreenTips are part of the formula. Compulsory are coloured, and the user must double click the parameter to update. Optional arguments are black, and must be deleted if the default value is required
      Reply ↓
      1. Guiding the way

        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!

        Reply ↓
  2. Wenming

    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).

    Reply ↓
    1. xlf ian Post author

      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.

      Reply ↓

Leave a Reply

Your email address will not be published. Required fields are marked *