+ Reply to Thread
Results 1 to 4 of 4

function w/ dynamic inputs

  1. #1
    Registered User
    Join Date
    06-04-2008
    Posts
    10

    Question function w/ dynamic inputs

    similar to Excel's =max() and =min() functions...

    lets say i wanted to write a function =sqrt(sumsq()), but i wanted the function to allow a dynamic number of inputs to pass to the code.

    how do i accomplish this?

    just so we understand the question. lets call the function =sss(). lets say the first time i use it in excel i type the following;
    "=sss(A1,B2:B10,D2,E9:F20)"

    the second time i type;
    "=sss(B2:B4,C2)"

    how do i establish the dynamic input when writing the function?

    thanks!!!!

  2. #2
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258
    Hello tadwegner,

    First, your macro needs to be a Function, not a Sub, and second, you can use a dynamic list by declaring it as a ParamArray. This declaration has to be the last argument in the function and must be a Variant. Nums holds the array of numbers returned from the cells. There can be none to as many as you list.
    Please Login or Register  to view this content.
    Sincerely,
    Leith Ross

  3. #3
    Registered User
    Join Date
    06-04-2008
    Posts
    10

    almost there

    Thanks! i didnt know about the paramarray. i see in the locals window that all the inputs are located in the nums() variant (sweet!). but, how do i use the nums() variant now?

    i have again hit a brick wall here. i dont know how to 'count' the number of entries in the variant. also i dont know how to access more than the first 'array' (the 0 value entry) of that variant.

    if its not too much trouble can you just lay out how i would SUM all the entries in the nums() variant. i will be able to take it from there.

    again thank you sooo much!

  4. #4
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258
    Hello tadwegner,

    Here is an example macro to sum the values...
    Please Login or Register  to view this content.
    Sincerely,
    Leith Ross

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. How to display help text in the Insert function wizard (Excel)
    By sudhakard in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-26-2009, 11:01 AM
  2. Link together different cells in specific order?
    By Sandman4432 in forum Excel Formulas & Functions
    Replies: 19
    Last Post: 01-03-2008, 05:01 AM
  3. Lookup function in a dynamic report
    By muchado77 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-28-2007, 12:27 AM
  4. Dynamic function creation
    By nougain in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-05-2007, 06:25 PM
  5. Analysis Toolpak Function XIRR and VBA - XL 2007
    By rvExcelNewTip in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-02-2007, 04:35 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1