Hi Sscprig,
Hopefully enough time has elapsed now since you took Rory’s advice, and you have recovered from the shock of losing your code! ( If you have not tried what he said yet, then don’t, until you have read this. )
What you are asking is a seemingly simple but subtly fundamental point, which if you can understand can help, I feel, in understanding some Topics in VBA that many of the best experts seem not always of quite fully grasped...
One gets into the habit of thinking of getting your stuff ( File ) open in front of you or
“up and running”. Then one thinks one is doing things with it and change it etc... At least I did thought that once.
But it is a bit more subtle than that.... At least that is how I have been getting to feel these things recently.
In general, your main stuff ( File ) stays where it is, most of the time. That is to say you have Excel Files somewhere. These Data File things are pretty meaningless and incomprehensible to most things and people. But the Microsoft Office Application of Excel recognises the information in them in such a way that it can “decode” this information and use it to make things “ happen” in front of you in the way you familiar with experiencing them / it.
I think that when you think you “open” your file with Excel what happens can very crudely be explained as follows: The Excel software either clones, makes a copy of, or at least makes some “Thing” that is representative enough of your Original File such that Excel can change that clone or copy or “´Thing” which it now has in such a way that it can meaningfully enough interpret all those changes in such a way as to make finally the changes to your actual File when you “Save”.
For want of me not having thought of a more correct and meaningful or absurd sounding Concept , let’s talk about what you are working on as the “
Clone” that is “
on” at the time. Let’s call it
Klonon for short. So you are not actually doing stuff or changing anything in your File. You are working on the Klonon. Sometimes people will put it a different way and say you are working on a copy of your File, or, working with and changing Values which were originally based on those in your File at the time you
Kloponed it, as it were ( Klopone : an imaginary concept somewhere between open and clone )
Earlier versions of Excel made no distinguishing I think between the two File sorts, xlsm and xlsx. You just had a .xls. I expect therefore the software is still fundamentally the same, and Klonon does not know the difference, or has remained basically the same, supporting all macro possibilities. He / She still can meaningfully enough interpret all that your version or versions of Excel is / are capable of doing in such a way as to finally save information in all the supported file versions,( at least to a first approximation! )
I think the idea behind making a distinguish in the two types at XL2007 was just because of security issues. They thought it would be a good idea to have a version of your File which would not be capable of holding enough Information to run a Code, so that someone could not, for example, have maliciously written a code in that File such as to cause you problems. This is because VBA is very powerful and you can fairly easily set a “booby trap” which sets off a damaging code.
I have limited experience, but I have not too date seen significant memory saving aspects over saving a file as .xlsx rather than .xlsm. I do not believe optimising of memory usage was a major factor behind the decision to introduce a “macro free” file version.
So you see, when you thought you “saved your File” as .xlsx you were doing something else. What Excel did under the command to “save” was to look at the current state of your Klonon. It took ( almost all ) the info the klonon had to offer. It just missed taking some information which would of enabled you to carry on with your macro work with a new klonon made the next time you “opened” that .xlsx File. After you “saved” you were continuing to work on the current Klonon. ( You were not, and had never worked on, or changed, your original File. Specific changes were only made to that File by the "save" Method )
By saving as .xlsx, you had instructed Excel to miss out saving
all the macro info. ( I say
all in meaning
all of it – I expect it may still have saved some of it. Probably the answer to that is a secret – Microsoft maybe collect info about exactly what the klonon is doing and do not tell anybody ).
If you then “closed” your file as Rory suggested after saving as .xlsx, then the current Klonon, which still had all your macro information up to this point, would however “die” on the “closing” ( at least officially ). At that point your macro information was lost, ( officially at least ). So when you “opened” your .xlsx a new Klonon was made. There was not enough info in your .xlsx file to make a new klonon that Excel could interpret in such a way as to construct the Macro info you would have been hoping to see again. In short, your macro was gone!! Ha Ha!
When you “work” with or change your klonon, you are not referencing your File at all. ( Your Excel may periodically make a “Back Up” copy of some information necessary to attempt a Recall of the last Klonon state, should your Excel crash. But that is a separate issue )
There is a nice parallel idea here that could help you understand a more difficult concept in VBA. It is do with working with / on
Variables. A VBA procedure may “call” or “pass” a
Variable. A
Variable can be something very complicated, such as in the case of an Array. You can consider this “passing” similar to “opening” your file. When you make your Klonon from your File you “take” or “pass” its various data
By its current face
Value. You then work with what you have. There the parallel ends, a bit. With all but the most complicated
Variables you have a second option . This is
Referred to as taking or passing
By Ref. You may or may not be still working on a “pseudo” klonon. I guess nobody anymore understands enough to answer that! But importantly, all changes you do are reflected ( almost ) immediately in the
Variable. You
Refer to it continuously. I guess the parallel there would be to have Excel save your file after every action you take. ( This is a good parallel as in all but the simplest cases a
By Ref will be making changes on
Referral somewhat more complicated then just changing a simply number to a different value )
A last light hearted way i like to think of it. Like in the Film Total Recall. Say you live on the Main Land and there is a Holiday Island. When you “go” on Holiday, you go to the Port. There you have two ways to “take” a Holiday”,
The word “take”, as often has a fairly imprecise meaning here,
like “Taking a "Dump!" )
You have the normal fairly expensive way to “take” a Holiday.
But they also offer you a cheaper way. For this cheaper way, a Clone is made of you. That goes and has a couple of Weeks Holiday on the island. Or rather it is “worked” on by some Technicians and has changes made to it, such as if it were on Holiday. When it comes back, or rather the Tech Team have finished with it, they operate on you changing both your body and brain to reflect all the experiences the Clone had , or feels it had. That was the cheap
By Value Holiday. (
It is uncertain what happens to the clone. Officially it dies. I expect it is sold to a data collecting agency, or given to the Secret Service )
The normal more expensive method is referred to as going
By Reference – Here everything is done / experienced by you, in , or close to, real Time. You are continually
Refered to
Alan
Reference
http://www.mrexcel.com/forum/excel-questions/917689-passing-array-class-byval-byref.html#post4414307
