**It turns out, I'm an idiot (or, au moins, hopelessly inexperienced avec this whole logiciel development thing)**. The below is what I spent my whole day panicking about. I'll write an answer detailing what I saw, what actions followed and what was en fait going on.
**tl;dr:** How I *thought* my IDE fonctionne and how it *en fait* fonctionne are 2 separate things. There was, en fait a bug in my code, but parce que of how I interpreted what I thought my IDE was telling me, I thought the problème was a much bigger one. And I'm an idiot for encore not having implemented version control yet.
**What I wrote Earlier:**
So, Je sun'est pas really a SysAdmin but Je suis effectively "the IT guy" for my company (we're 12 employees).
Sometime entre 7pm GMT last night 25/august/2015 and 11am GMT this morning 26/august/2015, all vba macros on every ordinateur (we run Excel 2010 and Excel 2013) starting exhibiting a behaviour whereby publicly declared Variables (which devrait être disponible to all subroutines in a given workbook) are seulement disponible withdans le module they were declared.
This renders a lot of our macros inoperable.
My suspicion is that there a été a Microsoft mise à jour that has messed avec Excel but I cannot find any confirmation of that online. In addition, our remote desktop server (which n'est pas automatiquement updated) is exhibiting the same behaviour. Any suggestions serait much appreciated.
**Mise à jour :** There n'a pas been a windows mise à jour récemment. Ceci est really bad news, parce que it means that quelque chose, somewhere has gone (probably uniquely) wrong. Variable scoping *should* be a fundamental part of a language. It ne devrait pas be a thing that *can* change. And yet, it has, sur le entire réseau, seemingly by itself.
All Public Variables in VBA Macros in my office ne fonctionne plus this morning
Re: All Public Variables in VBA Macros in my office ne fonctionne plus this morning
**What happened:** I opened up a macro this morning to work on expanding it. I ran it to check it was encore fonctionnel. it threw an "automation erreur".
[side note](I've always been used to being able to see my global variables in my locals window. In a nice pull down tab under the module name. It turns out, that only happens when you publicly declare them in the module you're viewing. I did not know this, and my new macro has all my public variables in their own separate module)[return]
So, I debugged my code, had a cherchez why this worksheet object was throwing an erreur and saw: `` in my locals window where my public variables toujours used to be. Naturally, I panicked. I figured the programme must've stopped referencing public variables in other modules. J'ai vérifié all the other ordinateurs in my office, same thing. I frantically searched the internet. Nothing on this. In desparation, I turned to Stack Overflow and Server Fault and Super Utilisateur and the chat room at Code Review. No luck.
**Ensuite I really panicked. I willingly phoned Microsoft tech support.**
Needless to say, this went so badly that I gave up. Went away for a bit and took out my frustrations on an empty floor of our building avec my katana (LARP safe, so no actual damage vers le building).
Ensuite I came back, went back to chat, talked to some more people, tried some more stuff and finally found out the thing about the locals window, which allowed me to trouvez le actual problème avec my code:
My macro was closing a workbook avant a point dans le code where J'étais trying to reference its worksheets. That was it.
**In all, I wasted an entire workday trying to fix a problème that n'a pas exist.**
**So, ici's a list of things I could have done / should ont été doing that would have prevented this:**
**Version Control:** Specifically, being able to revert code to a previous point in time when it was known to be fonctionnel. This would have immediately demonstrated that my code from e.g. the day avant encore ran as expected.
**The Watch Window:** dans le VBA IDE this allows you to specify variables and track their status throughout the entire macro. this would have shown me that my variable encore existed, it was encore being referenced, it just suddenly got emptied at a point dans le code.
**En fait running the old macros:** Parce que I thought the locals window showed public variables too, and they weren't there. all I did on other ordinateurs was step into a macro, see that they weren't there, and assumed the worst. Si je had en fait just run any of my macros from even a day avant, this problème would ont été averted.
**TDD / Unit Testing:** Would've caught my mistake almost the moment I introduced it, and certainly within minutes of writing it, which would have provided a big hint about what had gone wrong and prevented this whole thing avant it even started.
**Not Panicking:** I forgot / n'a pas try a lot of really obvious things, any one of which would have proved my theory wrong. À la place, parce que I thought I had a problème, I went looking seulement for evidence that would confirm it, not for evidence that would disprove it.
On the plus side, au moins Je suis maintenant much wiser than J'étais this morning.
[side note](I've always been used to being able to see my global variables in my locals window. In a nice pull down tab under the module name. It turns out, that only happens when you publicly declare them in the module you're viewing. I did not know this, and my new macro has all my public variables in their own separate module)[return]
So, I debugged my code, had a cherchez why this worksheet object was throwing an erreur and saw: `
**Ensuite I really panicked. I willingly phoned Microsoft tech support.**
Needless to say, this went so badly that I gave up. Went away for a bit and took out my frustrations on an empty floor of our building avec my katana (LARP safe, so no actual damage vers le building).
Ensuite I came back, went back to chat, talked to some more people, tried some more stuff and finally found out the thing about the locals window, which allowed me to trouvez le actual problème avec my code:
My macro was closing a workbook avant a point dans le code where J'étais trying to reference its worksheets. That was it.
**In all, I wasted an entire workday trying to fix a problème that n'a pas exist.**
**So, ici's a list of things I could have done / should ont été doing that would have prevented this:**
**Version Control:** Specifically, being able to revert code to a previous point in time when it was known to be fonctionnel. This would have immediately demonstrated that my code from e.g. the day avant encore ran as expected.
**The Watch Window:** dans le VBA IDE this allows you to specify variables and track their status throughout the entire macro. this would have shown me that my variable encore existed, it was encore being referenced, it just suddenly got emptied at a point dans le code.
**En fait running the old macros:** Parce que I thought the locals window showed public variables too, and they weren't there. all I did on other ordinateurs was step into a macro, see that they weren't there, and assumed the worst. Si je had en fait just run any of my macros from even a day avant, this problème would ont été averted.
**TDD / Unit Testing:** Would've caught my mistake almost the moment I introduced it, and certainly within minutes of writing it, which would have provided a big hint about what had gone wrong and prevented this whole thing avant it even started.
**Not Panicking:** I forgot / n'a pas try a lot of really obvious things, any one of which would have proved my theory wrong. À la place, parce que I thought I had a problème, I went looking seulement for evidence that would confirm it, not for evidence that would disprove it.
On the plus side, au moins Je suis maintenant much wiser than J'étais this morning.