+ Reply to Thread
Results 1 to 4 of 4

myArray() failing with cell method and works with Variant type only

  1. #1
    Forum Contributor
    Join Date
    06-20-2021
    Location
    UK
    MS-Off Ver
    2016
    Posts
    126

    Question myArray() failing with cell method and works with Variant type only

    My questions are in code comments.

    Please Login or Register  to view this content.
    I created a 10x10 table using randarray() and assigned integer data type in Excel; also tried copy pasting as values in A13. Don't know if VBA picks up functions differently to raw data.
    However, when I assign myArray() As Long/Integer, it doesn't seem to work.

    Lastly, for a 10x10 table, why won't Dim myArray(1 to 10, 1 to 10) work?
    Attached Files Attached Files
    Last edited by immigrated4urjob; 01-20-2022 at 11:55 AM.

  2. #2
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: myArray() failing with cell method and works with Variant type only

    First, you need to assign a variable and then give a value to it. Array could be any type of data, but if you want to assign the value from a range, the type should be variant

    Dim myArray

    Dim myArray(1 To 10, 1 To 10) ' first need to assign a variable

    Please Login or Register  to view this content.
    Last edited by AB33; 01-20-2022 at 12:00 PM.

  3. #3
    Forum Expert WideBoyDixon's Avatar
    Join Date
    10-03-2016
    Location
    Sheffield, UK
    MS-Off Ver
    365
    Posts
    2,182

    Re: myArray() failing with cell method and works with Variant type only

    Cell values are returned as Variant type so strongly-typed arrays won't work for retrieving the values; it must be a Variant array.

    Secondly, since the size of the region could vary, I don't think you can pre-allocate the size of the array. Ideally, you should also be using .Value:

    Please Login or Register  to view this content.
    WBD
    Office 365 on Windows 11, looking for rep!

  4. #4
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: myArray() failing with cell method and works with Variant type only

    You can only assign one array to another if the destination one is dynamic and of the same type.
    Rory

+ 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] Failing FIND method?
    By AxiAngel in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 12-06-2017, 08:33 AM
  2. Workbooks.Open method failing to run
    By JasonLeisemann in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 10-21-2013, 02:49 PM
  3. Select Method failing
    By j_Southern in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-18-2011, 05:34 AM
  4. copy method of object failing
    By vj2india in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-19-2006, 09:25 PM
  5. Select Method Failing
    By cmk18 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-11-2005, 07:05 PM
  6. [SOLVED] Dim MyArray() as Long, Dim MyArray() as String, etc.
    By quartz in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-28-2005, 12:06 PM
  7. add method of validation failing
    By mark kubicki in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-19-2005, 04:06 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