Results 1 to 9 of 9

Array reference in a cell: difference using or not array formulas

Threaded View

  1. #8
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP, 2007, 2024
    Posts
    16,346

    Re: Array reference in a cell: difference using or not array formulas

    If I target that cell with a VBA function, or with an external plug-in, whatsoever, what I will have as information?
    - the value of F3 in the spreadsheet?
    - the string "=A1:G1"?
    To be sure I understand, you have a cell with the formula =A1:G1 in it. As observed and explained, this formula will return one value to the cell. When you pass that cell to a UDF, what your UDF's variable contains will depend on exactly what data type the argument is.

    a) If the argument is typed as a numeric data type (double, long, etc.), then the variable will contain that single value stored in the cell.
    b) If the argument is typed as string, then the variable will contain that value converted to a text string.
    c) If the argument is typed as range or variant, then the argument will contain a Range object referencing that cell. Your UDF will then be able to read any of the property values that apply to that single cell range object (range object help file with the list of properties: https://docs.microsoft.com/en-us/off....Range(object) ). This obviously includes the cell's value (the .value property), but would also include the formula string (.formula property) and a whole list of other properties.

    The easiest way to see what you would have in different scenarios is to use VBA's debugging tools (specifically the locals window http://www.cpearson.com/Excel/DebuggingVBA.aspx ). As I explain in this tutorial (https://www.excelforum.com/tips-and-...uild-udfs.html ), one of the first things I do when developing a UDF is to add a stop statement to the top of the procedure so that it will enter debug mode. Once you get into debug mode, you can look in the locals window and see exactly what your argument contains. Hope that helps.
    Last edited by MrShorty; 07-05-2019 at 11:29 AM.

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Search For Array Formulas by Cell Reference
    By John Vieren in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 04-04-2015, 04:34 AM
  2. Question about using Evaluate with an array of formulas not an array formula
    By Mr_Bill in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-19-2014, 03:26 PM
  3. Replies: 0
    Last Post: 08-01-2013, 04:26 PM
  4. Array Formulas = Circular Reference Error?
    By tekman in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 12-31-2011, 07:08 PM
  5. Using cell reference in array formula
    By willow2008 in forum Excel General
    Replies: 2
    Last Post: 11-10-2009, 04:12 PM
  6. Replies: 6
    Last Post: 12-30-2008, 06:52 AM
  7. Array of formulas (regional difference)
    By ken4capitola in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 11-19-2005, 08:40 PM

Tags for this Thread

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