none
Array Worksheet Fx Not Returning Array in VBA RRS feed

  • Question

  • Hello

    The following VBA lines should each return an array, but instead they are returning a single value. What am i doing wrong?

    Y=[value({"1", "2", "3"})]

    Y=Application.Evaluate("value({""1"", ""2"", ""3""})")

    Do i need to dimension the size of Y first? Or use MemCopy or something?

    -thx

    Sunday, May 22, 2016 8:32 PM

Answers

  • Try...

    y = [{"1", "2", "3"}+0]
    
    and
    
    y = Application.Evaluate("{""1"", ""2"", ""3""}+0")

    By the way, I think you were probably thinking about using the VBA function Val instead of Value, in which case it still would have failed since Val is a VBA function and not an Excel reference.

    Hope this helps!


    Domenic Tamburino Microsoft MVP - Excel xl-central.com - "For Your Microsoft Excel Solutions"

    • Marked as answer by johny w Monday, May 23, 2016 2:52 AM
    Sunday, May 22, 2016 9:29 PM

All replies

  • Try...

    y = [{"1", "2", "3"}+0]
    
    and
    
    y = Application.Evaluate("{""1"", ""2"", ""3""}+0")

    By the way, I think you were probably thinking about using the VBA function Val instead of Value, in which case it still would have failed since Val is a VBA function and not an Excel reference.

    Hope this helps!


    Domenic Tamburino Microsoft MVP - Excel xl-central.com - "For Your Microsoft Excel Solutions"

    • Marked as answer by johny w Monday, May 23, 2016 2:52 AM
    Sunday, May 22, 2016 9:29 PM
  • Hi johny w,

    do you want to store the values in Array and then want to access the value using index number or just want to print the all data of an Array?

    what do you exactly want to achieve?

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, May 23, 2016 1:17 AM
    Moderator
  • y = [{"1", "2", "3"}+0]

    that looks very intriguing. Some sort of special Excel array syntax?

    Oh, i see! You adding 0 to each string-value to force implicit type conversion to double. Awesome!

    Fyi, i am not trying to use VB Val function. i want to use the Worksheet function Value in VBA, to obtain an array of values of the strings in the input array-- same as the function would behave on a worksheet.

    Yes, i know can loop through the array. But, i'm trying to do this with the Worksheet Value function. 

    Domenic, your method achieves the same result. 

    Thx!
    • Edited by johny w Monday, May 23, 2016 2:52 AM
    Monday, May 23, 2016 2:49 AM
  • >>Oh, i see! You adding 0 to each string-value to force implicit type conversion to double.<<

    Yes, that's exactly right.

    >>Fyi, i am not trying to use VB Val function. i want to use the Worksheet function Value in VBA, to obtain an array of values of the strings in the input array-- same as the function would behave on a worksheet.<<

    Ah yes.  Strange, though, in the worksheet the Value function seems to accept an array.  Interestingly, it looks like we can force it to recognize the array as follows...

    y = [IF({1,1,1},VALUE({"1","2","3"}))]


    Domenic Tamburino Microsoft MVP - Excel xl-central.com - "For Your Microsoft Excel Solutions"

    Monday, May 23, 2016 4:50 AM