+ Reply to Thread
Results 1 to 12 of 12

Excel IsNumber Function treats array elements differently from range elements

  1. #1
    Registered User
    Join Date
    08-27-2017
    Location
    Modesto, CA
    MS-Off Ver
    Microsoft Office Excel 2003
    Posts
    91

    Excel IsNumber Function treats array elements differently from range elements

    In Sheet1, Range A1:E5 I have

    smith $2.00 $3.50 $4.00 $5.50
    jones 6 7 8 9
    roe 10 11.5 12 13
    doe 14 [empty string] [blank] 15
    jones 16 17 18 19

    Here's the code:

    Please Login or Register  to view this content.
    The message box displays 15 13

    In the rng1 loop IsNumber treats $2.00 and $4.00 as numbers, but not in the array loop.

    What's up?

  2. #2
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: Excel IsNumber Function treats array elements differently from range elements

    Instead of Application.IsNumber (which is equivalent to WorksheetFounction.IsNumber), use IsNumeric().

    As well, when populating array from range. You should be careful to qualify what type of value is read from cells/range.

    Ex: If cell has datetime value but formatted to show only TIME. You will get unexpected result filling array without qualifying. Or when it's qualified as "value" and not "text". In general it's safer to use value2, especially when dealing with datetime values.

    In general I'd recommend something like following and when necessary use Format and/or CStr, CDbl or other type of conversion functions.
    Please Login or Register  to view this content.
    ?Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.?
    ― Robert A. Heinlein

  3. #3
    Registered User
    Join Date
    08-27-2017
    Location
    Modesto, CA
    MS-Off Ver
    Microsoft Office Excel 2003
    Posts
    91

    Re: Excel IsNumber Function treats array elements differently from range elements

    Thanks for replying, CK. You've kind of sidestepped the issue by loading your array with Value2. Then, of course, it doesn't have the same elements as the range from which it is loaded. My rng1 and arr1 both have the same values, including currency in the positions corresponding to B1:E1; your array doesn't.

    I'm writing a function for counting different features of ranges and arrays such as size, number of numbers, number of integers, number of blanks, number of currency values, and others. And although IsNumeric doesn't have the problem of not recognizing currency values as numbers, it does have the problem, for me, of treating blank strings differently from true blanks, i.e., empty cells. So, the IsNumber function, which doesn't have that problem with blanks, is still a hurdle.

    Thanks again for replying.

  4. #4
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Excel IsNumber Function treats array elements differently from range elements

    Why not use IsNumeric and test for a cell whose value length is 0 first?
    Don
    Please remember to mark your thread 'Solved' when appropriate.

  5. #5
    Registered User
    Join Date
    08-27-2017
    Location
    Modesto, CA
    MS-Off Ver
    Microsoft Office Excel 2003
    Posts
    91

    Re: Excel IsNumber Function treats array elements differently from range elements

    Thanks for the suggestion, xlnitwit. I was just starting to revisit the IsNumeric/IsNumber issue with respect to blanks, since using
    IsNumber, which solved that problem, has raised this new one with respect to currency.
    Thanks again.

  6. #6
    Registered User
    Join Date
    08-27-2017
    Location
    Modesto, CA
    MS-Off Ver
    Microsoft Office Excel 2003
    Posts
    91

    Re: Excel IsNumber Function treats array elements differently from range elements

    I'm marking the thread "Solved" because, although I haven't shed any light on the puzzling difference of IsNumber depending on whether range elements or array elements are being examined, I did make progress on my underlying issue with respect to counting
    features like number of numbers, number of integers, number of blanks, number of currency values, etc.

    And the IsNumber puzzle is not the first unsolved puzzle I have in connection with Excel/VBA.

  7. #7
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: Excel IsNumber Function treats array elements differently from range elements

    Currency and date time are really stored as number in cell.
    But when you don't specify type of data loaded into array...
    It can just read formatted data type rather than underlying value.

    Hence, my recommendation to load array as value2.

    Application.IsNumber interpret cell within it's context. But it cannot for currency data type stored within array.

    If you use IsNumeric vba function, it will be interpreted as true.

    Edit: You can use TypeName function to see that it's Currency data type stored in your array for those values.
    Last edited by CK76; 01-30-2018 at 06:39 PM.

  8. #8
    Registered User
    Join Date
    08-27-2017
    Location
    Modesto, CA
    MS-Off Ver
    Microsoft Office Excel 2003
    Posts
    91

    Re: Excel IsNumber Function treats array elements differently from range elements

    Understood, CK, but as I mentioned, IsNumeric raises a problem with respect to its treatment of an empty cell as
    as 0, hence as a number, rather than a blank.

    xlnitwit suggested testing for 0 length; I didn't fully understand exactly what he meant, but I'm tinkering
    with it.

  9. #9
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: Excel IsNumber Function treats array elements differently from range elements

    If len(array element) = 0 then must be blank

  10. #10
    Registered User
    Join Date
    08-27-2017
    Location
    Modesto, CA
    MS-Off Ver
    Microsoft Office Excel 2003
    Posts
    91

    Re: Excel IsNumber Function treats array elements differently from range elements

    Yes. The actual code, more in context, is

    Please Login or Register  to view this content.
    In essence, if the code has identified a blank as an integer thus including it in the integer count,
    then the counting variable is docked 1.

    Thanks again to xlnitwit, and to CK76 for his constructive contributions.
    leelnich might also be interested in this thread, but I don't know how to call it to his attention.

  11. #11
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Excel IsNumber Function treats array elements differently from range elements

    It would be more efficient to test for an empty value first
    Please Login or Register  to view this content.

  12. #12
    Registered User
    Join Date
    08-27-2017
    Location
    Modesto, CA
    MS-Off Ver
    Microsoft Office Excel 2003
    Posts
    91

    Re: Excel IsNumber Function treats array elements differently from range elements

    Yes, thanks. That needs to execute fewer lines of code.

+ 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. [SOLVED] Writing elements of array to a range
    By YasserKhalil in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-23-2015, 12:42 PM
  2. Please help with input form, arrays and a function
    By jbaich in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-20-2014, 01:50 PM
  3. VBA Subordinate and Function That Replace Specific Elements in An Array
    By HanadiS in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-28-2014, 05:34 PM
  4. [SOLVED] Removing specific elements from an Array based on matching elements in a different Array
    By jamesdeuce in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 01-24-2014, 10:35 PM
  5. Resources for Using VBA to Manipulate Silverlight Elements like HTML Elements
    By linear_db in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-16-2013, 04:43 PM
  6. Excel VBA Last Empty Row VBA paste Array elements
    By [email protected] in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-06-2006, 12:45 PM
  7. Transpose Function not Working with Long Array Elements
    By Ngan in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-09-2005, 08:05 PM

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