Array declaration

[Luis939]

If it's true that declaring a variable as variant takes up the most memory, then if I have a predefined array with say 4 strings
Myarray = Array("value1","value2","value3","value4&quot
how could i declare that as string or as a nonvariant,
ive tried declaring
Dim Myarray as string
but it hasnt worked, what am i missing, or is that not possible, thanks!!

 

[SkipVought]

Hi,

Dim Myarray(1 to 4) as string

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[Luis939]

so then i should seperately assign those values to each index
myarray(1) = "value1"
myarray(2) = "value2"
myarray(3) = "value3"
myarray(4) = "value4"
?

 

[SkipVought]

Yup.



Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[Luis939]

On a semi-related topic, regarding speed, is there a difference in speed or perhaps also memory if lets say i need to use .find() to locate 8 strings. Would it be faster to do run that function for the 8 strings once, then later run that again to locate them again, or,
run it once, store their cell address in a collection, and then recall the items in the collection,
im just asking becuase im thinking a collection might be faster since i dont have to search each cell again of the entire worksheet (at least thats what i assume excel does, but running the .find() function again requires less instructions for me)
hope i can get some good advise, thanks

 

[SkipVought]

.Find has an argument After:= and SearchDirection defaults to xlNext

So it does not need to search the entire worksheet.

I would only store the addresses if I needed to use the set of addresses MORE THAN ONCE.

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[BrainB]

You may have covered this already, but the greatest saving in Find times comes from limiting the search to a particular Range, Row(s), or Column(s)rather than searching the whole sheet each time. You may be able to put more often used items at the top.



Regards
BrianB
** Let us know if you get something that works !
================================

 

[Luis939]

Well to be perfectly honest, i wanted to use a collection becuase DEFINITELY not all 8 strings will be found, so i thought it be better to store them instead of searching for all strings all over again, it was just that looking at my coding it seemed a bit more than if i just searched for all those strings all over again

 

[SkipVought]

Luis,

You would not necessarily need a collection, if you were to add another dimension to the array to store the found addresses.

Obviously, there's more that one way to skin a cat.

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[Luis939]

Interesting, ill give it a try thanks

 

[Luis939]

If i do choose the route of using collections, would it be unwise to group up items of different types, would there by negative consequences? should i only group items of the same type, why?