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.

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.

@ MarvinP,

Originally Posted by MarvinP

......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.)

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

Originally Posted by MarvinP

... 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??

@ 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

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.

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

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.

@ 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)

Originally Posted by cyiangou

..... 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.

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

Originally Posted by cyiangou

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!!

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.

Originally Posted by Kyle123

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 ?

Originally Posted by Kyle123

...... 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

Originally Posted by Doc.AElstein

.............. on that one... In a year or two

Alan

...

Originally Posted by cyiangou

..... But these nuances are unimportant to you for now.

.. Yep !

@ cyiangou

... Regarding Post # 12

O.K. I think I have this one now. I follow all your arguments there, thanks again.. ( And .....

Originally Posted by cyiangou

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 ! )

EDIT:

Originally Posted by Doc.AElstein

.........
.. 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.......

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

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

Originally Posted by cyiangou

...

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

Originally Posted by cyiangou

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

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!

Coming close to wrapping up the parallel running part of the Thread written in English...

Originally Posted by Doc.AElstein

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...

@ Kyle....

Originally Posted by Doc.AElstein

.................
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

Hi Alan..

Originally Posted by Doc.AElstein

....
.....
. 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..

Originally Posted by cyiangou

...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....".....

Originally Posted by cyiangou

.....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

Originally Posted by cyiangou

...

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.

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

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.

Originally Posted by cyiangou

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

Hi

Originally Posted by cyiangou

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

Originally Posted by cyiangou

.......

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

Originally Posted by cyiangou

... 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

Originally Posted by Doc.AElstein

.........
. 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.

@ Alan and cyiangou

Originally Posted by Doc.AElstein

......
.. 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.........

Originally Posted by cyiangou

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

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

Originally Posted by cyiangou

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

Originally Posted by cyiangou

..... 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