+ Reply to Thread
Results 1 to 12 of 12

A variable defined as variant or variant array

  1. #1
    Forum Contributor
    Join Date
    01-15-2013
    Location
    London
    MS-Off Ver
    Excel 2003, 2007, 2010
    Posts
    422

    A variable defined as variant or variant array

    Dear all,

    I have a simple UDF below. arr() is defined as a variant type array. This causes some problem: if I type the formula as =CustomPct(A1:A10,0.1), then it will become #VALUE!. I need to change the formula to array =CustomPct(IF(ISNUMBER(A1:A10),A1:A10,""),0.1), then this will work. Alternatively, if I just define arr As Variant, then it works in both cases. It is really confusing.

    The Stop debug tool showed that a() does have valid values. But why it still became #VALUE! error?

    Please Login or Register  to view this content.

  2. #2
    Forum Guru
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,701

    Re: A variable defined as variant or variant array

    Change :
    Please Login or Register  to view this content.
    to
    Please Login or Register  to view this content.

  3. #3
    Forum Contributor
    Join Date
    01-15-2013
    Location
    London
    MS-Off Ver
    Excel 2003, 2007, 2010
    Posts
    422

    Re: A variable defined as variant or variant array

    If rData is specified as a range, then arr = rData is wrong. But arr is variant, why it cannot be assigned with range? I thought variant can hold all kinds of types.

  4. #4
    Forum Guru
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,701

    Re: A variable defined as variant or variant array

    Try this, can you spot the differences ?
    Please Login or Register  to view this content.

  5. #5
    Forum Guru
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,701

    Re: A variable defined as variant or variant array

    You are right, variant can hold all kinds of types, including basic datatypes (long, boolean, etc), objects (range, sheet, workbook, etc).
    In this case, you pass a range to rData, and that's why "Debug.Print rData.Rows.Count, rData.Columns.Count" is worked, rData is still a range, not array.

    If CustomPct() function is always called from cell, then "arr = rData.Value" can be used safely, but if there is possibility this function is called from a sub, then you can add this line:
    Please Login or Register  to view this content.

  6. #6
    Forum Contributor
    Join Date
    01-15-2013
    Location
    London
    MS-Off Ver
    Excel 2003, 2007, 2010
    Posts
    422

    Re: A variable defined as variant or variant array

    Thank you, karedog.

    So arr() As Variant means that it is an array, and it is waiting for values. If rData is entered as a range such as A1:A20, then it cannot be passed to arr() directly because the type mismatch?

  7. #7
    Forum Guru
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,701

    Re: A variable defined as variant or variant array

    You are welcome.
    Yes, very correct.

  8. #8
    Forum Contributor
    Join Date
    01-15-2013
    Location
    London
    MS-Off Ver
    Excel 2003, 2007, 2010
    Posts
    422

    Re: A variable defined as variant or variant array

    the Stop statement allows me to debug the code. For some reason, a() has all the valid values (run until the last Stop statement), but the final results is just #VALUE! error. I don't know why?

    If there is indeed an error due to type mismatch for the statement arr=rData, then I don't expect a() to have valid values

    Please Login or Register  to view this content.

  9. #9
    Forum Guru
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,701

    Re: A variable defined as variant or variant array

    How do you check the a() value ?

    Using the code below, macro will stop at very first line, then continue by pressing F8 key.
    The last line that macro still worked is "MsgBox "Check Point 2"", after that macro is stopped completely because of the error on this line : "arr = rData", it never reached the "MsgBox "Check Point 3"" line. If you open the watch window, you will also notice that a() is never changed (empty).

    Please Login or Register  to view this content.
    Do you have another UDF that uses a() variable too ?

  10. #10
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    12,504

    Re: A variable defined as variant or variant array

    I tried the function in post #8, but it didn't get very far. When I tried it, it errored on the first arr=rdata line, so I am not recreating what you are seeing. In this respect, I am seeing the same thing karedog is seeing.

    A declaration like dim arr() as variant looks to me like a dynamic array of variants. You never declare how many elements to declare for arr(), which, I think causes it to error when it tries to assign something to the incompletely defined array.

    If I add a ReDim statement before the assignment statement
    Please Login or Register  to view this content.
    it successfully assigns the range of values to an array arr(0)(i,j) -- now we have an array of arrays. Later syntax does not account for this, so there are errors later in the code.

    Removing the array part of the declaration
    Please Login or Register  to view this content.
    works just fine, and arr becomes a variant containing an array.

    I think what I see causing confusion here is something that I incompletely understand. You can have a variant that contains an array. You can have an array of variants, and each element in the array of variants may be a variant containing an array (creating the possibility of an array of arrays or a jagged array). Keeping those possibilities straight sometimes challenges me, and I find I must think very carefully through exactly what rData will be and what I want arr to be and the different scenarios I expect to see.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  11. #11
    Forum Contributor
    Join Date
    01-15-2013
    Location
    London
    MS-Off Ver
    Excel 2003, 2007, 2010
    Posts
    422

    Re: A variable defined as variant or variant array

    Hi MrShorty, many thanks for the detailed explanation. It shows me where the error is for my code. It is useful, thank you.

  12. #12
    Forum Contributor
    Join Date
    01-15-2013
    Location
    London
    MS-Off Ver
    Excel 2003, 2007, 2010
    Posts
    422

    Re: A variable defined as variant or variant array

    Karedog, thanks a lot for your code and explanation

+ 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] Defined SheetValue as Variant, but it still says object required?
    By malleat1 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-03-2015, 10:09 AM
  2. Does setting a variant acting as an array equal to "" accomplish same as "Erase" variant?
    By dmasters4919 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-21-2014, 05:59 PM
  3. Array Variant
    By jakc in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 10-07-2014, 11:16 AM
  4. Variant vs Array
    By Kyle123 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-26-2012, 09:45 AM
  5. Integer defined array vs variant defined array ..error making assignment to integer
    By welchs101 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-07-2011, 01:48 PM
  6. Refer to array elements defined as Variant
    By hareleph in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-23-2011, 07:15 AM
  7. assigning a Variant variable to an integer
    By jartzh in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-02-2009, 05:06 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