+ Reply to Thread
Results 1 to 50 of 50

Store Array created by VBA macro “Internally” for use in same VBA macro by next run..or..-

  1. #1
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Store Array created by VBA macro “Internally” for use in same VBA macro by next run..or..-

    Store Array created by VBA macro “Internally” for use in same VBA macro by next run... or............

    ... or........more fully.....
    Store Array and values in variables created by VBA macro “Internally” for use in same VBA macro by next run, or by other VBA macros, after the macro creating the Array or Variables has stopped.

    . I expect the answer to this may be
    . a ) No
    . b ) why bother – simply export the Array contents or variable values to an excel sheet and reference them from there!

    . But: I was hoping it may be possible and give speed advantages....

    ... (... The basic requirement came from a code I am developing here:
    http://www.excelforum.com/excel-prog...user-form.html
    . In this code a Worksheets_Change Sub routine accesses a very large spreadsheet range, and creates various large Arrays from which totals calculations are made based on an entry in column C in the spreadsheet...
    . )
    .
    .
    . So I was wondering if it is possible to create some of those Arrays for example in another Sub routine, such as a in a Worksheets Open Code, and have those already available to the Worksheets Change Sub routine immediately when it runs.


    . I was expecting if this could be done it would have speed advantages as:
    . a ) I have learnt from my Forum participation the advantages of minimising the interaction with the spreadsheet.
    . b) Not all the calculations and Array making need to be done every time, so if the “internal accessing” of these “internally stored” Arrays worked quickly then this would be quicker than re-doing the calculations to produce them.

    Does Anyone

    . (i) Know if this is Possible

    And if it is possible

    . ( ii ) Can anyone get me started on how

    . Note:
    . I am primarily interested in storing .value2 numbers up to the accuracy level of about 7 decimal places in “normal” format, for example 345.0965401, to a maximum of about 9999.00 and a minimum of about 0.0000001

    . This is a long term personal project for me, so speed is not of essence. If anyone comes up with an idea in the future please post here.

    . Thanks
    Alan.

    P.s. I do have some experience with the Microsoft Scripting Runtime Directory, but I believe, as its name suggest, that Dictionary “vanishes” when the program stops )
    P.p.s. My knowledge generally of computing is restricted to a couple of years with Excel VBA. If there is a method to achieve what I want, could someone further help me by

    . a) give for example the VBA code lines that would export some Array, say arr1(), to the “internal storage”
    And
    . b ) give the code lines that would retrieve this Array.
    . c ) give me any other code lines needed, such as the accessing of any external Libraries necessary.
    Last edited by Doc.AElstein; 08-09-2015 at 08:43 AM.
    '_- Google first, like this _ site:ExcelForum.com Gamut
    Use Code Tags: Highlight code; click on the # icon above,
    Post screenshots COPYABLE to a Spredsheet; NOT IMAGES PLEASE
    http://www.excelforum.com/the-water-...ml#post4109080
    https://app.box.com/s/gjpa8mk8ko4vkwcke3ig2w8z2wkfvrtv
    http://excelmatters.com/excel-forums/ ( Scrolll down to bottom )

  2. #2
    Valued Forum Contributor
    Join Date
    09-17-2012
    Location
    Johannesburg, South Africa
    MS-Off Ver
    Excel 2007
    Posts
    454

    Re: Store Array created by VBA macro “Internally” for use in same VBA macro by next run..o

    If I understand you correctly, we'd like to devise a function (or rather a strategy) that 'caches', say the tabular result of a lengthy calculation, so that in later references to this data, we avoid recalculating and 'look up' results from a previous run instead, without having to store this data in a sheet.

    Yes, this can be done, but we have to manage the cases where Excel will lose your internal storage (this happens when debugging, for instance), so our functions have to handle reinitialising the internal array if required, AND do the expected fast return of a result from the array results.

    In the function:
    - Check if internal array is initialised:
    - If not initialised then initialise array
    - Return function value from initialised array

    So the first time the function is called, it calculates and stores a 100x100 array of numbers
    In subsequent calls, the function finds the array is already populated, and so just looks up a return value

    Please Login or Register  to view this content.
    https://en.wikipedia.org/wiki/Singleton_pattern

  3. #3
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Store Array created by VBA macro “Internally” for use in same VBA macro by next run..o

    Hi Doc,

    I've wondered the same thing a few times, if a global variable could be stored between closing and opening a file.
    The obvious answer is to use a wayward cell(s) somewhere in a sheet that can be accessed later, but you don't want that.
    Here are some places to look.

    1. There is a "Tag" field in most controls that might be used for such a constant. Using Code you can store things in the Tag field an it will be saved when the file is closed.
    2. Using the Names Manager you can create Array Constants which will be saved when you close and reopen a file.

    Look at the two objects above and see if they satisfy your constraints. For me, I'll pick some distant spot on a worksheet and simply save my values there. When the file is closed and opened again, there they are.
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  4. #4
    Valued Forum Contributor
    Join Date
    09-17-2012
    Location
    Johannesburg, South Africa
    MS-Off Ver
    Excel 2007
    Posts
    454

    Re: Store Array created by VBA macro “Internally” for use in same VBA macro by next run..o

    I almost always have a last sheet in my workbooks called 'Settings'.

  5. #5
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: Store Array created by VBA macro “Internally” for use in same VBA macro by next run..o

    @ MarvinP,
    Quote Originally Posted by MarvinP View Post
    ......Look at the two objects above and see if they satisfy your constraints. ......

    . Hi MarvinP,
    . Thanks for the reply.
    . I am unfamiliar with those 2 objects, so am not sure how to use them for my requirement.
    . ( I have jumped into the deep end a bit – getting into VBA before I had more than a few months experience with Excel – that catches me out sometimes!! )
    . But thanks for pointing me in that direction.
    . I shall try to "Google" and read up on those.
    . (The idea you mentioned of doing that with code sounds attractive, but as mentioned , for now, I do not know how to start on that.)

    .........................

    Quote Originally Posted by MarvinP View Post
    ... The obvious answer is to use a wayward cell(s) somewhere in a sheet that can be accessed later, but you don't want that…..... For me, I'll pick some distant spot on a worksheet and simply save my values there. When the file is closed and opened again, there they are.
    . That is sort of what I do currently but as the Ranges ( and subsequent “Captured” thereof Arrays, and calculations based on the Arrays ) are very large, ( Currently about 9000 rows x 3500 columns ) I am wanting to investigate any speed advantages with the “Internal memory” idea. That is the reason why I do not want that. As Mentioned I shall report back my findings on that once I investigate more fully the implementation of cyiangou’s Function on larger files, comparing speeds etc. With my current codes
    ( ....
    http://www.excelforum.com/excel-prog...user-form.html
    ...)
    .
    Thanks again for your input
    Alan
    P.s – A third solution, just for curiosities sake... along the line of what you said ( for a very limited amount of data ) could be to use the status Bar – If I wrote to it and sometimes forget the line
    application.statusbar = false
    at the end of the code, - Then i noted that it stayed there... but I have not worked out if I can retrieve that data yet ?? ( but i guess if so that would be just like interacting with a spreadsheet range and very slow.. But i wonder how much data it holds.. I know you only see so much, but maybe there is more there??

  6. #6
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: Store Array created by VBA macro “Internally” for use in same VBA macro by next run..o

    @ cyiangou,

    . Hi ,
    . Thank you very much for your contribution. I am extremely grateful: It appears to be exactly what I had been searching for for some considerable time now. I had been getting the impression that it was not possible
    . I was immediately able to get the results I was looking for, and was about to reply to say thanks,. But I got ( and am still slightly ) bogged down for several hours in trying to understand exactly how it is working.. and in an attempt to make the Function more general for me..
    . I was however able to get exactly what I wanted, that is to say my original request has been well answerd...
    .
    . Just to clarify that.
    . I take any spreadsheet range, say for example this


    Using Excel 2007
    Row\Col
    B
    C
    D
    E
    4
    4
    407D
    5
    5
    408Q
    6
    6
    405P
    7
    7
    403A
    Sheet1

    . I run this modified version of the code from you given in Post #2 , ( that is to say i run code
    Sub Test_cyiangouFunction1()
    .
    Please Login or Register  to view this content.
    . Stepping throught the code in F8 one gets exactly as wanted: on the first run the an Array
    CatchArray()
    . is created.
    . On subsequent runs the function finds the array is already populated and just accesses it
    .
    Brilliant!!!

    Thanks cyiangou,

    P.s.
    .
    . Just for fun, after the first run of the
    Sub Test_cyiangouFunction1()

    . one can run this
    Please Login or Register  to view this content.
    .. and one achieves this
    Using Excel 2007
    Row\Col
    B
    C
    D
    E
    4
    4
    407D
    4
    407D
    5
    5
    408Q
    5
    408Q
    6
    6
    405P
    6
    405P
    7
    7
    403A
    7
    403A
    Sheet1
    .. again demonstrating ... “ Store Array and values in variables created by VBA macro “Internally” for use in same VBA macro by next run, or by other VBA macros, after the macro creating the Array or Variables has stopped. „…

    Many Thanks again for all the help
    Alan

    ……

    P.P.s . Just in case anyone can help with the couple of things still niggling me…
    . 1 ) I do not understand why this does not work as an alternative form of the function

    Please Login or Register  to view this content.
    . is this maybe telling me that my Privates ! must be an object, variant or the such and cannot be an Array?
    .........

    . 2) Just for fun, I thught I would generalise the function for so as to work for a single cell also ( The original Fuction falls down there as a value is returned rather than an Array. )
    .. I spent a couple of hours with code variations of the example below.. But they always fall down at the point of lines such as 110 below.. I can’t seem to force
    GetCachedValue2sAlsoFor1Cell
    Into becoming an Array of one element


    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    .. I will leave this Thread as unanswered for a few days in case anyone else has some input. Otherwise i will mark it as solved as the original request has been nicely satisfied
    Last edited by Doc.AElstein; 08-09-2015 at 09:14 PM.

  7. #7
    Valued Forum Contributor
    Join Date
    09-17-2012
    Location
    Johannesburg, South Africa
    MS-Off Ver
    Excel 2007
    Posts
    454

    Re: Store Array created by VBA macro “Internally” for use in same VBA macro by next run..o

    Hi Doc

    I'm glad that works for you, and thanks for the rep. Just one point, usually when I use Variants like this, I declare them as undimensioned variants, ie. without the (). Part of the trick is detecting whether the variant has been initialised as an array, and repopulating if not. Variants take some getting used to, but they are very, very powerful.

  8. #8
    Valued Forum Contributor
    Join Date
    09-17-2012
    Location
    Johannesburg, South Africa
    MS-Off Ver
    Excel 2007
    Posts
    454

    Re: Store Array created by VBA macro “Internally” for use in same VBA macro by next run..o

    Usually this 'singleton pattern' approach is applied to objects, not variants/arrays, but is usually for the same reason; to minimise 'setup time'.

    For example, if I need to access the web from my app, but launching an instance of Internet Explorer to use is a heavy-duty call and one I want to avoid making repeatedly.

    So this is a more common implementation of this approach, using the Is Nothing test instead of IsArray:

    Please Login or Register  to view this content.
    Edit: You can have a whole library of functions that return different things from the cached object, but each of them must also call the cache-if-not-cached routine. And once you have families of related functions like this, then it's worth wrapping them in class modules.
    Last edited by cyiangou; 08-10-2015 at 02:08 AM.

  9. #9
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: Store Array created by VBA macro “Internally” for use in same VBA macro by next run..o

    since you're using this like a singleton, wouldn't it be a better solution to have the cached array as static inside the function rather than a public variable?

    I'd have thought that in this scenario the possibility of external routines mutating state is not desirable
    Last edited by Kyle123; 08-10-2015 at 02:12 AM.

  10. #10
    Valued Forum Contributor
    Join Date
    09-17-2012
    Location
    Johannesburg, South Africa
    MS-Off Ver
    Excel 2007
    Posts
    454

    Re: Store Array created by VBA macro “Internally” for use in same VBA macro by next run..o

    BTW, it's a private, not a public variable; it's only accessible from functions inside the module. This is the usual structure when making classes.

    I often use Statics too (usually when I'm trying to achieve self-containing functions), but then you have to do different things (read: unconventional/'non-standard') with optional parameters when you need to force a re-initialisation.

  11. #11
    Valued Forum Contributor
    Join Date
    09-17-2012
    Location
    Johannesburg, South Africa
    MS-Off Ver
    Excel 2007
    Posts
    454

    Re: Store Array created by VBA macro “Internally” for use in same VBA macro by next run..o

    I'd have thought that in this scenario the possibility of external routines mutating state is not desirable
    But yes, you're talking about the bad practice of using public variables in lieu of well-constrained property procedures.

  12. #12
    Valued Forum Contributor
    Join Date
    09-17-2012
    Location
    Johannesburg, South Africa
    MS-Off Ver
    Excel 2007
    Posts
    454

    Re: Store Array created by VBA macro “Internally” for use in same VBA macro by next run..o

    Alan,

    I can’t seem to force (a variable) into becoming an Array of one element
    In this function:
    Please Login or Register  to view this content.
    You can use this approach (see why we don't declare the variant with the '()'?):
    Please Login or Register  to view this content.

  13. #13
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: Store Array created by VBA macro “Internally” for use in same VBA macro by next run..o

    Quote Originally Posted by cyiangou View Post
    BTW, it's a private, not a public variable; it's only accessible from functions inside the module. This is the usual structure when making classes.
    I'd missed that, I was reading on my phone. I'd rather have a class to encapsulate it though rather than simulating the behaviour - you just know that at some point someone's going to put more code in that module , a class makes it less likely and static less likely to be affected.

    Quote Originally Posted by cyiangou View Post
    I often use Statics too (usually when I'm trying to achieve self-containing functions), but then you have to do different things (read: unconventional/'non-standard') with optional parameters when you need to force a re-initialisation.
    I'm not really sure what you mean by this, I don't see how it's any easier to force re-initialisation with:

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

  14. #14
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: Store Array created by VBA macro “Internally” for use in same VBA macro by next run..o

    P.S this thread has an insane amount of views for one created yesterday!

  15. #15
    Valued Forum Contributor
    Join Date
    09-17-2012
    Location
    Johannesburg, South Africa
    MS-Off Ver
    Excel 2007
    Posts
    454

    Re: Store Array created by VBA macro “Internally” for use in same VBA macro by next run..o

    I'm not really sure what you mean by this, I don't see how it's any easier to force re-initialisation with
    I was half expecting that. The static doesn't work when you have a family of customised functions that need to access the same shared variable, which is a major part of why we want to do this. But I do love those occasions where I can make a fully encapsulated function that does the whole job; for that I always use Static.

    P.S this thread has an insane amount of views for one created yesterday!
    Wow. I hadn't noticed. And I only found the OP under 'Unanswered threads' where it had been languishing for 2 hours already.

    ...and thank you for the rep.
    Last edited by cyiangou; 08-10-2015 at 05:24 AM.

  16. #16
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: Store Array created by VBA macro “Internally” for use in same VBA macro by next run..o

    Quote Originally Posted by cyiangou View Post
    I was half expecting that. The static doesn't work when you have a family of customised functions that need to access the same shared variable, which is a major part of why we want to do this. But I do love those occasions where I can make a fully encapsulated function that does the whole job; for that I always use Static.
    Why? I don't think I agree with you, why can't one simply always call the function and operate on that? The point of doing it like this is that the shared value is cacheable and immutable, if you are wanting to mutate it's state I think it should be in a class to properly control access (though I'd put it in a class anyway). Though you may well be making a point that I can't get my head round - I'm easily confused!

  17. #17
    Valued Forum Contributor
    Join Date
    09-17-2012
    Location
    Johannesburg, South Africa
    MS-Off Ver
    Excel 2007
    Posts
    454

    Re: Store Array created by VBA macro “Internally” for use in same VBA macro by next run..o

    I'll get to some of your other points, but a major factor is that a class's members, even if public, are not visible to a worksheet. You still have to make a public wrapper function at module level so that you can use it in the worksheet. My usual architecture is to have a number of specialised classes in my project, with one specialised module to hold all the wrapper worksheet functions that use the classes. I'll usually name these all in CAPS, in keeping with Excel functions.

  18. #18
    Valued Forum Contributor
    Join Date
    09-17-2012
    Location
    Johannesburg, South Africa
    MS-Off Ver
    Excel 2007
    Posts
    454

    Re: Store Array created by VBA macro “Internally” for use in same VBA macro by next run..o

    I'm agreeing with you, when appropriate, Static is perfect.

    But here is an example scenario where the use of Static to maintain state persistence would be more a hindrance than a help. I hope that this illustration is self-explanatory.

    Here I'm choosing to keep all functionality in one .bas file, so that a user only has to 'Import File' once. It's also a normal module, because only this can fulfill ALL our requirements.

    Please Login or Register  to view this content.

  19. #19
    Valued Forum Contributor
    Join Date
    09-17-2012
    Location
    Johannesburg, South Africa
    MS-Off Ver
    Excel 2007
    Posts
    454

    Re: Store Array created by VBA macro “Internally” for use in same VBA macro by next run..o

    I was half expecting that
    Sorry, this was just me expecting a justified rebuttal when I realised I hadn't specified multi-function access.

  20. #20
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: Store Array created by VBA macro “Internally” for use in same VBA macro by next run..o

    So it's really about worksheet visibilty, I must admit, I'd write a class and a module "interface" as you do. You could still use static in the above though, but it's really just horses for courses:
    Please Login or Register  to view this content.

  21. #21
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: Store Array created by VBA macro “Internally” for use in same VBA macro by next run..o

    @ cyiangou,
    .. Thanks for the many follow up replies and contributing to make this a very interesting ( and looked at.... ) Thread. I am afraid I am a long way behind you pros and getting over my head ( jumping in a bit too deep again ) .
    ......
    . 1)
    Quote Originally Posted by cyiangou View Post
    ..... when I use Variants like this, I declare them as undimensioned variants, ie. without the (). Part of the trick is detecting whether the variant has been initialised as an array, and repopulating if not. .......
    ... so I can follow some reasoning as to why you do not use the () ... And ..... - how stupid of me - - I should have noticed the If Not IsArray bit ...

    . ... so this does the stuff,
    ( Albeit:
    .1) With an error handler or two , which is bad practice,
    And ;
    .2) still will not working for a single cell..
    ...
    )

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

    Quote Originally Posted by cyiangou View Post
    Usually this 'singleton pattern' approach is applied to objects, not variants/arrays, .......
    . 2 ) I do not really understand this singleton thing.. so I cannot really follow your arguments ...yet... I do not want to push my luck as you have been more than helpful.. but can you describe that singleton thing in English. ( The link you gave I cannot follow – I have got a basic idea of Class modules in VBA, ( thanks mainly to Kyle’s and Rory’s replies in previous threads)... ). BUT NO RUSH... I see you are busy in an intellectual discussion with kyle now ( which i have no chance of following - maybe when you time later you can answer my “Beginner question” !!

    ...........................
    .. i will review your further posts now, wow is this Thread taking off.. I cannot keep up!!
    Last edited by Doc.AElstein; 08-10-2015 at 07:56 AM.

  22. #22
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: Store Array created by VBA macro “Internally” for use in same VBA macro by next run..o

    What cyiangou is referring to as a singleton is that only one instance of the array should ever be able to exist. I.E Create it if it doesn't exist, then just return the one that's already created rather than creating a new one - singleton referring to the fact that there will only be one of them, a new one is not created each time.

    What may confuse you whilst reading is that the term singleton isn't usually used in this context, it is more commonly used to describe objects (rather than arrays) - that's why if you read up on it used in this context it may not quite fit the descriptions that you are reading. There is a bit of debate out there whether they should really exist at all since they introduce global scope and state which in some circles is considered bad practice - though that's one for another day.

  23. #23
    Valued Forum Contributor
    Join Date
    09-17-2012
    Location
    Johannesburg, South Africa
    MS-Off Ver
    Excel 2007
    Posts
    454

    Re: Store Array created by VBA macro “Internally” for use in same VBA macro by next run..o

    What Kyle said.

    Alan, from what I've seen, you can understand what we're talking about; it's just the terms we're using. But these nuances are unimportant to you for now.
    Last edited by cyiangou; 08-10-2015 at 08:17 AM.

  24. #24
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: Store Array created by VBA macro “Internally” for use in same VBA macro by next run..o

    Quote Originally Posted by Kyle123 View Post
    What cyiangou is referring to as a singleton is that only one instance of the array should ever be able to exist. I.E Create it if it doesn't exist, then just return the one that's already created rather than creating a new one - singleton referring to the fact that there will only be one of them, a new one is not created each time. ......
    Thanks Kyle , even I can follow that.... singleton... Get it ?

    Quote Originally Posted by Kyle123 View Post
    ...... There is a bit of debate out there whether they should really exist at all since they introduce global scope and state which in some circles is considered bad practice - though that's one for another day.
    .... maybe catch you in another of my "Beginner's Thread on that one... In a year or two

    Alan

  25. #25
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: Store Array created by VBA macro “Internally” for use in same VBA macro by next run..o

    Quote Originally Posted by Doc.AElstein View Post
    .............. on that one... In a year or two

    Alan
    ...
    Quote Originally Posted by cyiangou View Post
    ..... But these nuances are unimportant to you for now.
    .. Yep !

  26. #26
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: Store Array created by VBA macro “Internally” for use in same VBA macro by next run..o

    @ cyiangou

    ... Regarding Post # 12

    O.K. I think I have this one now. I follow all your arguments there, thanks again.. ( And .....
    Quote Originally Posted by cyiangou View Post
    Alan,
    ...........(see why we don't declare the variant with the '()'?):
    .....
    ......... Yes...- I learn once again why often people who know what they are doing deliberately miss out the () bit when playing around with variables associated with Arrays. Naively I always thought it was carelessness, ( Sometimes it is, but 0nly occasionally )

    ... I follow the new solution you gave me, and why:

    .. But the stumbling point that remains is that
    . 1 ) I will not ( I think ) be able to produce a simple function along your original lines ( or any other function for that matter ) that will also return a 0ne dimensional ( Or 1 column, 1 row 2 dimensional ) of one element. The problem is that a Function, ( Say AlanFunctionsBad ) that may return an Array must be a Variant.
    Sub AlanFunctionsBad() as Variant
    . So if I try to make my Function equal to a 1 element Array, it will always fall down at The last line in the Function

    AlanFunctionsBad = ArrX(1)
    .----- as VBA will look at this , ArrX(1) and give back to
    AlanFunctionsBad
    A value. ( assuming ArrX() is a 1 element Array ),

    ..................
    . As opposed to when the ArrX() has more than one element. In that case
    AlanFunctionsBad = ArrX()
    . -------- VBA will look at ArrX(), find a data field, and give back a data field or array.
    .
    ... The solution you gave me in post # 12, if I understand it, would require an extra interaction with the sheet initially to attain if the given range was a single cell. This would make any following “Singleton - like” Function redundant, that is to say the advantages of checking the existence of the Array before, if necessary interacting with the Spreadsheet to obtain it, would be lost.

    . But Thanks to all your inputs I think I have learnt now how to get close to it.
    This would be my final ( I think ) solution

    Please Login or Register  to view this content.


    P.s. I may have misunderstood exactly what you meant in post # 12, and maybe something along the lines of my final solution is what you meant. In any case I got there thanks to all your help.
    Many Thanks again
    . Alan

    ......

    .. now to try and follow the rest of this Thread..... Maybe tomorrow.. !! – the Wife’s moaning again about my Excel affliction ! (Although it is all for here Diets anyway ! )

  27. #27
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: Store Array created by VBA macro “Internally” for use in same VBA macro by next run..o

    EDIT:


    Quote Originally Posted by Doc.AElstein View Post
    .........
    .. But the stumbling point that remains is that
    . 1 ) I will not ( I think ) be able to produce a simple function along your original lines ( or any other function for that matter ) that will also return a 0ne dimensional ( Or 1 column, 1 row 2 dimensional ) of one element. The problem is that a Function, ( Say AlanFunctionsBad ) that may return an Array must be a Variant.
    Sub AlanFunctionsBad() as Variant
    . So if I try to make my Function equal to a 1 element Array, it will always fall down at The last line in the Function

    AlanFunctionsBad = ArrX(1)
    .----- as VBA will look at this , ArrX(1) and give back to
    AlanFunctionsBad
    A value. ( assuming ArrX() is a 1 element Array ),

    ..................
    . As opposed to when the ArrX() has more than one element. In that case
    AlanFunctionsBad = ArrX()
    . -------- VBA will look at ArrX(), find a data field, and give back a data field or array.
    . ...........
    ... There is a way, ( But only to return a 1 Dimensional Array in the case of one cell )......
    .... Use =Array( _____ ) Method to force AlanFunctionsBad to be a 1 Dimensional Array..


    Please Login or Register  to view this content.

    .. Now if only in that function I could force
    GetCachedValue2sarr2_1Dalso
    To be a 2 Dimensional 1 Element Array.......

    ...................................

  28. #28
    Valued Forum Contributor
    Join Date
    09-17-2012
    Location
    Johannesburg, South Africa
    MS-Off Ver
    Excel 2007
    Posts
    454

    Re: Store Array created by VBA macro “Internally” for use in same VBA macro by next run..o

    Naively I always thought it was carelessness
    IMO, it mostly is. As powerful as it is, this power is usually an instrument of laziness.

    Now if only in that function I could force
    GetCachedValue2sarr2_1Dalso
    To be a 2 Dimensional 1 Element Array.....
    Do these checks:
    • There must be NO dim statement for a variant using () in any of your code, only use it in Redim statements.
    • When fetching the array behind a Range, do it like this (where v is a plain variant):
    Please Login or Register  to view this content.
    • You'll also notice in my code that i convert the referenced range to a variant array as early as possible, and just use the array from there on.
    • vba will always return either a single value non-array variant OR a 2-dimensional array. So never a one-dimensional array.
    • So you only need to test whether it's an array or not. If it is, it's always a 2-dim.

    Do all the above, and my instructions will work

  29. #29
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: Store Array created by VBA macro “Internally” for use in same VBA macro by next run..o

    Hi cyiangou,
    . Thanks for the continued replies. Much appreciated.

    Quote Originally Posted by cyiangou View Post
    ...
    Please Login or Register  to view this content.
    ......
    .. I will keep trying, But
    . 1) I have found and been told by many people that, when VBA sees
    = rng.value
    .. then
    .. - if rng is a cell, it returns a value.
    .. - if rng is more than one cell it returns a ( as you correctly say 2 Dimensional ) Array.
    v , as variant, takes what it gets..
    .. – I still see no way to get v to become a 2 dimensional array of 1 element, ( which for reasons of later compatibility in various calculations i do would be helpful )

    . 2 ) VBA seems ( I think ) to do something similar with Arrays.... It does not help to do any sort of re dimensioning to ( 1 to 1, 1 to 1 ) or ( 0 , 0 ) ..
    .. if I do do that and then, say do
    v = arr ( 1 , 1), where arr is a 2 dimensional 1 element array,
    .. VBa still returns a value = to what is in arr ( 1 , 1 ) and once again v becomes a value ( at least it did the last few times I tried.....
    .. but i will go for a long jog, have a quick kip, and have another session later ( if the wife lets me!! )

    Thanks again
    Alan
    Last edited by Doc.AElstein; 08-10-2015 at 01:56 PM.

  30. #30
    Valued Forum Contributor
    Join Date
    09-17-2012
    Location
    Johannesburg, South Africa
    MS-Off Ver
    Excel 2007
    Posts
    454

    Re: Store Array created by VBA macro “Internally” for use in same VBA macro by next run..o

    Add this to your module:
    Please Login or Register  to view this content.
    And just use it like this to convert your range to an array:
    Please Login or Register  to view this content.

  31. #31
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: Store Array created by VBA macro “Internally” for use in same VBA macro by next run..o

    Quote Originally Posted by cyiangou View Post
    Add this to your module:.....

    And just use it like this to convert your range to an array:........
    OK. Got it !!!!

    ... along the way i had almost got there.....
    .. looking back the problem was I usally did this..

    RangeToArray = Arr(1, 1)

    and the bit that did it was

    RangeToArray = Arr

    ..... Thanks for taking the time to do that nice demo code.
    ..
    . I will tidy up the " Internal Array " codes based on that and post back later ( the point being not to have to do the check based on
    v = rng.Value
    If IsArray(v) Then
    as this then always does an extra interaction with the sheet.
    .. )


    Very many thanks
    Alan

  32. #32
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Store Array created by VBA macro “Internally” for use in same VBA macro by next run..o

    About ten years or so ago, I had a gig at PG&E where they were getting reports from the field and the data was all over the place with different headings and different expressions - well, to consolidate the field reports I used an UDT and an array of that UDT in the module - boy, was I pleased and surprised at the time to find that that array remained in core as long as the Book was in play!
    If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)

    You can't do one thing. XLAdept

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin

  33. #33
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: Store Array created by VBA macro “Internally” for use in same VBA macro by next run..o

    The other thing that can be quite nice for this type of thing is to use a class and set it's VB_PredeclaredId property to True. This allows it to be auto instanced much like the default userform instances - so it's global and can simply be referenced by its name without initializing it - it allows you to get pretty close to a global singleton (as long as you don't manually instance it).

    Whether this is best practice or not is another question...

  34. #34
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: Store Array created by VBA macro “Internally” for use in same VBA macro by next run..o

    Coming close to wrapping up the parallel running part of the Thread written in English...
    Quote Originally Posted by Doc.AElstein View Post
    OK. Got it !!!!
    ........
    . I will tidy up the " Internal Array " codes based on that and post back later ( the point being not to have to do the check based on
    v = rng.Value
    If IsArray(v) Then
    as this then always does an extra interaction with the sheet.
    .. ).....
    ... was not too difficult to get the working solution, once cyiangou had given me the answer....

    .. The final Function that will ...“ .... Store Array .... Created by VBA macro “Internally” for use in same VBA macro by next run, or by other VBA macros, after the macro creating the Array or Variables has stopped. ....”......
    ... It will always create a 2 Dimensional Array, even when the data is accessed from a single cell. It will create that Array the first time around and store it “ Internally “ , and on subsequent rounds it will accesss it from the “ internal “ Array..

    Please Login or Register  to view this content.

    .....
    . So I am 99% along the way to mark the thread as solved, at least the English written part.
    . For my part I would just like to re write the code without using error handlers to prevent Rory getting upset if he sees it...

    Alan
    P.s. Thanks very much for all the “other” stuff in the Thread. I am going to have a go at understanding that as well...

  35. #35
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: Store Array created by VBA macro “Internally” for use in same VBA macro by next run..o

    @ Kyle....

    Quote Originally Posted by Doc.AElstein View Post
    .................
    P.s. Thanks very much for all the “other” stuff in the Thread. I am going to have a go at understanding that as well...
    ... or maybe not ... this year anyway... !

    ... This is as far as i can / should go..

    ... So in Post # 34 I have a ( maybe ) Final version that I am quite happy with..

    ... This would be your "Static Version

    Please Login or Register  to view this content.

    ... seems to work just the same as the one I got through cyiangou’s Privates ideas..

    . Could you give any advice on if / when / what any differences might affect what I am doing, and any consequences that etc. That might suggest one or the other is better. .. ( my basic requirement again... from Post # 1 .......The basic requirement comes from a code I am developing here:
    http://www.excelforum.com/excel-prog...user-form.html
    . In this code a Worksheets_Change Sub routine accesses a very large spreadsheet range, and creates various large Arrays from which totals calculations are made based on an entry in column C in the spreadsheet.... )……So I was wondering if it is possible to create some of those Arrays for example in another Sub routine, such as a in a Worksheets Open Code, and have those already available to the Worksheets Change Sub routine immediately when it runs........
    ...
    ... )

    Thanks, I appreciate all your extra inputs, even though I am too dumb to understand them , yet..
    Alan

  36. #36
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: Store Array created by VBA macro “Internally” for use in same VBA macro by next run..o

    Hi Alan..

    Quote Originally Posted by Doc.AElstein View Post
    ....
    .....
    . 1 )
    . So I am 99% along the way to mark the thread as solved, at least the English written part.
    . For my part I would just like to re write the code without using error handlers ........
    ...
    .. I do not think as long as you persist in ( For whatever reason ? ) in Dimensioning your “Internal Array” with the extra () you will manage your function completely without use of Error Handling technique. Fundamentally testing for if The Array is filled by all the method I have tried requires knowing the its dimension, which cannot be known if it has not been filled leading to an error which can be “caught as in all your codes do far..
    .
    . The next code seems to be the nearest, - At least you have reduced it to having only one Error Handler: – This came out by really getting to grips with when and how to use the Variant..
    Quote Originally Posted by cyiangou View Post
    ...When fetching the array behind a Range, do it like this (where v is a plain variant):....
    Dim v As Variant
    v = Rng.Value
    ...... That is not always required, but can make the important difference.. ...
    .. The key to the following code working was not just this line....
    Please Login or Register  to view this content.
    ………… ( As Let myInternalArray2() = VBAreturnedFromDotValue() also works )
    .. But here it was required....
    Please Login or Register  to view this content.
    Full Code:

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

    . 2 ) In your actual requirement you have a few Arrays, so really you should consider either

    . 2a) Not using the code as a Function, but simply use the code as a code section when you need it, referencing in that code section the particular MyInternalArray_X that you need at that time.... It may not “look” as well orgasnised, but know one knows how “VBA” “sees” these things, and there maybe no real advantages in running speed etc. From the Function approach..

    Or

    . 2b ) The code should be modified thus...

    Please Login or Register  to view this content.

    Note ByRef insted of ByVal is required in the first Function Line. This ensures that the Variable Passed does not get initialised / lose its value at the end of running the Function...The idea behind this could be a Further alternative Altogether to the cyiangou and Kyle Functions..
    ( Strangely ByVal MyIntArr() As Variant throws up a syntax error anyway?? ( Moans about a data field having to be given over By ref ) - ByVal MyIntArr As Variant does not.........
    ....... maybe these last points are for another Day,...or Thread.. mark this Thread as solved... For now....


    Alan.


    The next questions will probably be... What is the difference between and different merits of..
    cyiangou Functions.
    Kyle Functions.
    And new coming
    Alan
    ByRef Functions
    … when trying to ... ".... Store Array and values in variables created by VBA macro “Internally” for use in same VBA macro by next run, or by other VBA macros, after the macro creating the Array or Variables has stopped....".....

  37. #37
    Valued Forum Contributor
    Join Date
    09-17-2012
    Location
    Johannesburg, South Africa
    MS-Off Ver
    Excel 2007
    Posts
    454

    Re: Store Array created by VBA macro “Internally” for use in same VBA macro by next run..o

    Hi Alan and Alan
    .. I do not think as long as you persist in ( For whatever reason ? ) in Dimensioning your “Internal Array” with the extra () you will manage your function completely without use of Error Handling technique
    Exactly, but I don't want to get between you two on this.

    For this kind of application we want to use the Variant at it's 'most polymorphic'. We do not want to impose what kind of data it can contain when we declare it.
    When you do this:
    Please Login or Register  to view this content.
    You are declaring an 'array of variants', and not necessarily a 'variant array' that may contain, say decimal data. Your declaration implies that it can contain many 'variant arrays'.
    However, there will be applications where the above is the correct and appropriate usage.

  38. #38
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: Store Array created by VBA macro “Internally” for use in same VBA macro by next run..o

    Quote Originally Posted by cyiangou View Post
    .....I don't want to get between you two on this.....
    . Hi cyiangou,
    . Thanks very much for the further input we ( I ) was not expecting it. Helps me ( us ) to keep sane having some info from someone sane who know what he is talking about..

    . 1 ) Words like polymorphic send me a bit crazy: I guess in English 'most polymorphic' is just saying that variant can be the most ( all ) of the type of the ‘Family’ to which it belongs. Or simply it can be any of the declarable VBA variables. I think this point I have finally got thanks to your input and the experimenting that I did. And more importantly there are occasions when such a possibility, that is to say the variable type Variant is really needed, so is an essential Type... I think I finally have that as well understood as anyone .
    . 2)
    ... I do not 100% follow this
    Quote Originally Posted by cyiangou View Post
    ...
    Please Login or Register  to view this content.
    You are declaring an 'array of variants', and not necessarily a 'variant array' that may contain, say decimal data. Your declaration implies that it can contain many 'variant arrays'..
    . 2a)
    . I do follow this part
    When you do this Dim v() As Variant You are declaring an 'array of variants', ........
    . - As, my understanding is
    Dim Arr() as variant
    ..Defines an Array of elements. The elements can be any allowed VBA type, that is to say the elements remain Variant type, but I can put any type of variable in them ( even Arrays which I recently successfully did in answering a few Threads.. )
    .....
    . 2b)
    .. but what you are saying here puzzles me...
    …. not necessarily a 'variant array' that may contain, say decimal data. Your declaration implies that it can contain many 'variant arrays'..
    . - As my understanding is
    Dim Arr as Variant
    .. defines Arr as simply variant. It can have any type of variable assigned to it but remains variable type. I am not sure how that ties up with what you were saying.??

    .
    . .. In the meantime I confess I almost forgot myself why I insist on the extra (). I guess just trying to narrow things down to both conserve memory and make errors generally more likely to catch at an earlier stage etc, the normal “good programming practice” of not leaving things too "open" to misuse...

    ..............................................

    . .. 3) I stumbled in my last post #36 experiments across the ByRef idea in a Function as a “trick” to get a Function to return more than one value --- effectively the “extra” returns are put in the variable which is returned By referencing to that “Internally” held place for that variable which is ( necessarily then as it must be ) defined at the start of the module.
    . Hence I can achieve my very first requirement from this Thread through a code given at the end of this post, as a third alternative to the Kyle and cyiangou Functions developed in this Thread
    .....
    . I am starting to confuse myself now but I think the conclusion is...

    . Assuming I may have in the practice more than one Array....”..... created by the VBA macro to be stored “Internally” for use in same VBA macro by next run.....”.... ( A Point i may originally have omitted to mention )... then my solution would be either:
    .
    . a) . to use the codes in the Functions given by you or Kyle ( Or rather those I presented based on what I had learnt from your inputs ), but not as Functions. - Rather I would repeat the code section for each of the Arrays that i was interested in at the time in my main Code. This is because those Functions were tied down to a Particular Array.. ( I could modify the Functions having an extra variable taken in ( ByVal) by the Function , say a A1, A2 , A3, or A4... etc ) which would indicate, ( on a case Else using that extra taken in variable), which "Internal Array" to access - But that could all get a bit messy )
    Or
    . b). I could stay with a Function using my latest code based on the ...“ByRef in a function to return more than one thing”.. idea

    ..............................
    . My only last thought would be is there any reason from the “what VBA does / sees” point of view on which idea to use..
    .. clearly form the “neatness of appearance of my main Code ”, my last idea would be preferable.....
    ...
    .. maybe you, or anyone or Alan , if they had time could comment on that...
    . But no rush. I am very grateful for all the help from you all in this thread. I am really getting I think a good understanding now of this aspect of VBA.

    Thanks very much again
    Alans
    ............................................

    The latest “ Alans Byref ” idea to solve the problem of ....”.....ArrayS created by the VBA macro to be stored “Internally” for use in same VBA macro by next run.....”....

    Please Login or Register  to view this content.


    ................................
    Last edited by Doc.AElstein; 08-17-2015 at 10:39 AM.

  39. #39
    Valued Forum Contributor
    Join Date
    09-17-2012
    Location
    Johannesburg, South Africa
    MS-Off Ver
    Excel 2007
    Posts
    454

    Re: Store Array created by VBA macro “Internally” for use in same VBA macro by next run..o

    . 2b)
    .. but what you are saying here puzzles me...
    …. not necessarily a 'variant array' that may contain, say decimal data. Your declaration implies that it can contain many 'variant arrays'..
    . - As my understanding is
    Dim Arr as Variant
    .. defines Arr as simply variant. It can have any type of variable assigned to it but remains variable type. I am not sure how that ties up with what you were saying.??
    I wasn't happy with my own explanation here, but I'm happy that you understand what I was trying to say (which I gather from other things you've said).

  40. #40
    Valued Forum Contributor
    Join Date
    09-17-2012
    Location
    Johannesburg, South Africa
    MS-Off Ver
    Excel 2007
    Posts
    454

    Re: Store Array created by VBA macro “Internally” for use in same VBA macro by next run..o

    You should note that Kyle's method of substituting a Static for a module-level Private variable in post #20, will actually not work at all in that context. I actually put in a comment about doing this in my original code (#18).

    The cache variable (m_Locations) has been isolated in the GETLOCATIONS function, so it is now local and not accessible to the other functions in the library that need it. If you were to repeat the same method for each of the other functions, then they would each instance their own copy of the cached variable from scratch, violating your requirement of re-use.

    For the record, the use of a Static to maintain state is also one of my methods, I was just arguing against using it where it doesn't help us.

    I'm agreeing with you, when appropriate, Static is perfect. But here is an example scenario where the use of Static to maintain state persistence would be more a hindrance than a help.
    Edit: Ok, I now see what you did with the other functions, Kyle, so it should work, but it seems a little convoluted for no special advantage.
    Last edited by cyiangou; 08-17-2015 at 09:34 AM.

  41. #41
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: Store Array created by VBA macro “Internally” for use in same VBA macro by next run..o

    Quote Originally Posted by cyiangou View Post
    I wasn't happy with my own explanation here, but I'm happy that you understand what I was trying to say (which I gather from other things you've said).
    Thanks for clearing that up.
    Appreciate it
    Alans

  42. #42
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: Store Array created by VBA macro “Internally” for use in same VBA macro by next run..o

    Hi

    Quote Originally Posted by cyiangou View Post
    You should note that Kyle's method of substituting a Static for a module-level Private variable in post #20, will actually not work at all in that context. I actually put in a comment about doing this in my original code (#18).

    The cache variable (m_Locations) has been isolated in the GETLOCATIONS function, so it is now local and not accessible to the other functions in the library that need it. If you were to repeat the same method for each of the other functions, then they would each instance their own copy of the cached variable from scratch, violating your requirement of re-use.

    For the record, the use of a Static to maintain state is also one of my methods, I was just arguing against using it where it doesn't help us.



    . Thanks very much again for all that.

    . I was not referring to the above, as at that point in the Thread I was totally outside my confidence / abilities, and had totally lost track of what you two were talking about.
    . I was referring to post #35 where I presented a code based on Kyles initial input which was about as far as I could understand, as I noted at the outset of that post, Post #35. The code in that post appears to preserve My Internal Array in the practice exactly as does your Function does.
    . But thanks very much for the continued input. i hope one day to re visit this Thread and understand the valuable info resulting form you Profi's discussion
    Thanks again
    Alans

  43. #43
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: Store Array created by VBA macro “Internally” for use in same VBA macro by next run..o

    Quote Originally Posted by cyiangou View Post
    .......

    Edit: Ok, I now see what you did with the other functions, Kyle, so it should work, but it seems a little convoluted for no special advantage.
    Ahh, I missed your update.....

    .. I think we are in agreement then on that one...

    Thanks again
    Last edited by Doc.AElstein; 08-17-2015 at 10:36 AM.

  44. #44
    Valued Forum Contributor
    Join Date
    09-17-2012
    Location
    Johannesburg, South Africa
    MS-Off Ver
    Excel 2007
    Posts
    454

    Re: Store Array created by VBA macro “Internally” for use in same VBA macro by next run..o

    ... aaand you've smashed through 3000 views on this thread.

  45. #45
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: Store Array created by VBA macro “Internally” for use in same VBA macro by next run..o

    Quote Originally Posted by cyiangou View Post
    ... aaand you've smashed through 3000 views on this thread.
    Hmm ? .

    . Well I will re mark the Thread as Solved, I think it looks like my last code from #38 is a good solution, at least for prettyness, if using a function rather than duplicatijg code sections. ( BTW. I just edited that post to include that code as I seemed to have omitted it the first time around..)


    . Still interested in any comment about the relative merits of the 3 solutions

    . 1 ) cyiangou Function codes, ( I think about Post #34 was the latest, all be - it at that point the two Error handler version )(which i just noticed BTW also works with the bit at the start changed from
    Private myInternalArray2DogsBolx() As Variant
    to
    Dim myInternalArray2DogsBolx() As Variant ... minor point....I guess that mod just makes myInternalArray2DogsBolx() available in another Module . But this point is also telling me that any variable defined outside the sub and at the start of the module has its contents preserved after being filled the first time
    )

    . 2) Kyle's Static Function code ( Post # 35 )

    . 3 ) Alans By Ref Idea Function code ( Now in Post # 38 )

    ... Alan

  46. #46
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: Store Array created by VBA macro “Internally” for use in same VBA macro by next run..o

    Quote Originally Posted by Doc.AElstein View Post
    .........
    . 3 ) Alans By Ref Idea Function code ( Now in Post # 38 )

    ... Alan
    Hi.
    . Well done Alan on your continued learning. And Thanks for sharing you results.
    . One thing, you missed the point again on your last ( quite good ) Code solution.
    . As you are using the ByRef trick to get your My(?)InternalArray you can drop of that last bit in the Function normally used to return a something altogether. Then the Function works like, ( and in all intents and purposes is, a Sub... so this is just as good

    Please Login or Register  to view this content.

  47. #47
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: Store Array created by VBA macro “Internally” for use in same VBA macro by next run..o

    @ Alan and cyiangou
    Quote Originally Posted by Doc.AElstein View Post
    ......
    .. I do not think as long as you persist in ( For whatever reason ? ) in Dimensioning your “Internal Array” with the extra () you will manage your function completely without use of Error Handling technique. Fundamentally testing for if The Array is filled by all the method I have tried requires knowing the its dimension, which cannot be known if it has not been filled leading to an error which can be “caught as in all your codes do far.........
    Quote Originally Posted by cyiangou View Post
    Hi Alan and Alan.....
    Exactly,........
    Hi Alan and cyiangou

    _ . Actually in the case of what you are doing that is not quite right: - I just noticed something in another Thread:
    http://www.mrexcel.com/forum/excel-q...ml#post4336013

    _ There is a VBA TypeName Function, which has a characteristic when applied to a Range Object which you can use to good effect...
    _ ...This is the Story........

    _ ( Sensibly ) when you reference your Ranges you first do a bit like
    Dim rng As Range
    Set rng =_______
    then do your
    Let GetCatchedValue(rng)

    _ For this new idea to work, you need to take the
    Dim rng As Range
    out of the Sub Procedure and put it alongside your “Internal Array” Dimensioning at the start of the module. ( No Harm in doing that in how you were currently doing things )

    _ . A characteristic of the VBA TypeName Function when applied to a Range Object is that if the Range was never Set, then it returns the String “Nothing” . If the Range Object was Set ( which you always do anyway just before doing your GetCatchedValue(rng) bit ) then the string “Range” is returned

    _ So this would be your Code form:

    Please Login or Register  to view this content.
    _. Run the Procedure Sub TestWithoutErrorHandler() a couple of times, and I think you will get the point!

    _ . ( BTW. You cannot do something similar with
    If Not TypeName(myInternalArray()) =”____”
    As unfortunately this returns the string “Variant()” regardless of whether the array has been assigned/ filled or Not. Not sure of the logic to this. Maybe in VBA’s way of thinking a range does not exist before it is set, but somehow a it thinks a dynamic Array not yet assigned/ filled does exist )

    _.. Once again proved... “You can do just about anything in VBA... just a question of if you stumble across how.. I expect often no one can tell you how, because no one taught anyone how because Bill Gates didn’t have time to tell anyone and forgot in the meantime

    Hope that helps give you another possibility to consider.
    Alan

  48. #48
    Valued Forum Contributor
    Join Date
    09-17-2012
    Location
    Johannesburg, South Africa
    MS-Off Ver
    Excel 2007
    Posts
    454

    Re: Store Array created by VBA macro “Internally” for use in same VBA macro by next run..o

    The normal way to check the initialisation state of an object is the IS keyword, eg:
    Please Login or Register  to view this content.
    Also, if you're now caching the source range at the module level anyway, then you no longer need to store the internal array like this, as you can extract this data on the fly from the cached Range object.

  49. #49
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: Store Array created by VBA macro “Internally” for use in same VBA macro by next run..o

    Hi cyiangou,
    Thanks for coming back again to us here
    Hi cyiangou,
    Thanks for coming back again to us here

    Quote Originally Posted by cyiangou View Post
    The normal way to check the initialisation state of an object is the IS keyword....
    Thanks for that, I think i may have seen that previously, but was somehow always sidetracked by Looking at the Array... because

    Quote Originally Posted by cyiangou View Post
    ..... if you're now caching the source range at the module level anyway, then you no longer need to store the internal array like this, as you can extract this data on the fly from the cached Range object.
    _ ..Sorry it was probably not too clear that i always did have the Range Object “There” in my codes, but in my actual very large files, I am trying to minimise interaction with the Spreadsheet, for speed considerations. So Hence I “catch” all data ( just the once, thanks to this Thread ) and access all Values in the program from that large Array, rather than extract this data on the fly from the cached Range object. – I have learnt in some other very long “Beginner Basic” Threads here that you mostly do not actually extract data directly from a Range Object ... You cannot ( easily / quickly / In one go ) store all the Data ( Properties etc ) from a Range Object, ( Apart from as I do, things like the Values )
    If you try, as i did in these “Beginner’s Threads”, you only actually have stored the “reference” to the sheet. And then you interact with the sheet each time you get info, with, for example
    =Range(“A1”).Value.
    As I am primarily interested in Values I can “Capture” those in one go into an Array. I then access a lot of those Array Elements in my program as it runs, which would take a lot of time if i got those by referencing the Range Object, that is to say Interacting with the sheet each time.

    _ . The issue in this Thread was initially to see if the large Array() could be filled once and live on after a Sub closed. You showed that it could be done, but there appeared to be those limitations , as we discussed, as to what methods could be used to determine if the Array had been filled. (In particular I had preferrred not too use Error handling. The last stand we had then was that that could not be done ) But now with the
    VBA TypeName Function
    Or , as You have kindly reminded me the more usual
    If ______ Is Nothing Then
    Applied to the defined Range Object
    We can indirectly determine if the Array() had been filled without those various limitations ( I say Indirectly because I just happened to Dim a variable and Set it to the Range Object in my normal way ( rather than I think the word is "Hard Coding ) and as i mentioned it does no harm to have that Dim moved outside to "live" after the sub closes just as my "myInternalArray()" does )

    Thanks again for taking the time to reply
    Alan
    Last edited by Doc.AElstein; 11-09-2015 at 03:59 PM.

  50. #50
    Valued Forum Contributor
    Join Date
    09-17-2012
    Location
    Johannesburg, South Africa
    MS-Off Ver
    Excel 2007
    Posts
    454

    Re: Store Array created by VBA macro “Internally” for use in same VBA macro by next run..o

    Cool, your points about the inefficiency of accessing the range on the fly are completely valid in your 'snapshot' context.

+ 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] Dynamic Changing Data, Based on Criteria Store in Array, Sum array and store in Cell
    By penbeacho in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 07-28-2015, 10:31 AM
  2. Add file to the Record Macro Store macro in drop down
    By dalfiuss in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-27-2015, 11:26 AM
  3. Replies: 3
    Last Post: 03-21-2014, 01:19 PM
  4. [SOLVED] Write and assign macro to newly created button using macro
    By tryingtolearn in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-19-2012, 10:41 PM
  5. Perform macro "on open" specific file- store macro in Personal Macro Workbook?
    By thompssc in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-17-2012, 12:38 PM
  6. macro to store each ine of a text file in an array
    By mithil in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-19-2010, 01:41 AM
  7. Array limit for a macro created chart
    By sanraso in forum Excel Programming / VBA / Macros
    Replies: 16
    Last Post: 06-16-2008, 02:33 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