4 ms·

Or it could be like the place I worked where IT made as many hurdles as possible to the installation of new software. So I, as a database marketing associate, l

Or it could be like the place I worked where IT made as many hurdles as possible to the installation of new software. So I, as a database marketing associate, linked 4 different CRMs used by different departments together using the magic of Access, Excel and VBA. IT found out when they disabled a library (DAO) because "cryptolockers" could use it, only to break almost every single process at my organization. The IT director was pissed, but he had to reenable the library because everything ground to a halt. Bonus points: he told my boss that VBA macros were bad because hackers could use them to take over the mouse and click on things.(Edit: I also distinctly remember the IT director accusing me of being a hacker when he found out I was using VBA.) I still wake up in a cold sweat with nightmares that something broke in that system. The VBA editor in Office is an absolute nightmare when it comes to debugging. No version control either.

deleted 9y ago
[deleted]
agar 9y ago
Version control in Excel VBA is easy. Just do a Save As..."Mission Critical Finance Model v2.xlsm", then "Mission Critical Finance Model v3.xlsm", then "Mission Critical Finance Model v3a (temp with changes for Jim).xlsm" and then "Mission Critical Finance Model v4 (maybe-trying to merge 3a back-DO NOT USE).xlsm" and so on...
- Declanomous 9y ago
  That's true. Good luck doing anything with that though. My main database was up to version 2051 by the time I left. The class I have nightmares about is the "compile and backup" class I wrote, that decompiled the database, appended the date to each file, compared all of the non-data and non-binary components, recompiled the database with the most recent versions, and then made sure every database was linked to the most recent versions of other databases. Why did I decompile everything? Well I learned that unless the file size changed markedly, the deduplication feature of the file replication system that our company used assumed the files were the same. Well Access databases can stay the same size for a long time unless you run a compact and repair on them. So I lost several weeks of work more than once because the system decided that an older version and a newer version of a database were the same. Without that stupid "feature" I would have just had the production Access file be named Access-prod.accdb and updated it each day. I honestly have so many nightmares about that job I'm intentionally doing easy work until I can convince myself that files don't magically delete themselves without a trace and administrators aren't deliberately evil.
  - agar 9y ago
    Just to clarify, I was being sarcastic about it being easy (or actually being version control, for that matter). But thanks for the horror story. What a nightmare.
  - CydeWeys 9y ago
    ... wow. Instead of hashing the file contents, it just checked file size? Who came up with this algorithm?! There's plenty of applications that use files of fixed large sizes initialized at install, for performance reasons or just simply so if you're going to run out of space you do so during interactive installation, rather than silently breaking later on.
    - Declanomous 9y ago
      From what I can determine, the authoritative version was always the one with the later timestamp. This might have worked at least in theory, however there were always issues. The foremost issue was that the timeserver was virtualized, and drifted to an unbelievable degree. This issue was compounded by the fact that we used citrix virtual machines for some applications, and our local desktops for other applications. The latter issue wasn't a problem until they moved the citrix servers from an on-site server room to cloud provider about 40 miles away. (I think this is when the timeserver became virtualized as well) At that point the latency between the citrix sessions and the local desktops was such that I could create a document on the file share, edit it on my local desktop, close it, save it, and then open it on the remote desktop and it would lock the replication from my computer and lose all my work. My department managed to wipe out the changes every other department had made to the yearly budget workbook because of this interaction. Of course part of the issue was using Excel as a shared document, but that issue was hugely compounded by the fact that our network infrastructure was bananas. For instance, building access databases using the Microsoft recommended separate front-end/back-end files, with each individual user having their own front-end file actually managed to make the issue occur more frequently. If you used a single access database file on a shared drive the issue was substantially less likely to occur. I can only imagine this is because the dedupe was postponed until everyone was done using the file, and the file was more likely to substantially change in size when multiple people were using it. The IT director had an incredibly fragile ego, and he attacked anyone who challenged him in any way. He hired a truck driver (with absolutely no IT experience) to work the IT help desk, which I assume was because he wanted a sycophant. He reported me to HR any time I did anything he didn't understand or which perceived to be a challenge of his power. He reported me to HR for using open-source software, with a description of open source software that made it sound like Wikipedia meets programming, and only used by hackers. He also reported me to HR after he heard I called our IT situation Kafkaesque. As far as nightmares go, that might have been my favorite, because my defense was literally "it is Kafkaesque, and I can prove it." I managed to successfully convince HR that the IT situation was Kafkaesque using examples from Kafka's work, whose response was "well there isn't anything we can do about it, but you aren't wrong." The sensible thing would have been to leave as soon as possible, but I really liked the field I was in, which was kind of specialized. Looking back I stayed way too long.
- hexane360 9y ago
  Mission Critical Finance Model v4 FINAL_revised (2).xlsm
  - agar 9y ago
    Stop looking at my file system!
  - jrs235 9y ago
    Mission Critical Finance Model v4 FINAL_revised (2) - Copy (2) (2).xlsm
- manigandham 9y ago
  Box, Dropbox, etc solve much of the problem
- cosmie 9y ago
  I was once on a team piloting a new inventory planning model in Excel, testing it out at a handful of distinctively different manufacturing sites to stress test it before paying SAP's ransom to bake it into the official system. When I started, I was backfilling someone who created the Excel model and was responsible for batch exporting data from SAP, crunching the numbers in Excel, and sending that out to the sites every morning. My onboarding consisted of being given his laptop and his login credentials[1], and being met with a desktop full of dozens nested folders of Excel sheets in that exact version control format. I still have nightmares of that week. [1] My manager had successfully kept IT from being able to get the laptop for re-imaging. But my senior VP located on a different continent had to get involved to get IT to reset the guy's password for us.
- gaius 9y ago
  The funny thing is you can save your spreadsheet into Sharepoint which does have version control and people still do it that way in Sharepoint too...
- tmzt 9y ago
  Or you could: unzip *.xlsm git add . Probably with some custom diff scripts for the xml files.
  - Declanomous 9y ago
    I did most of this in Access. Also, I wasn't allowed to install version control of any sort, so that's really the bigger problem.

deleted 9y ago

[deleted]

agar 9y ago

Version control in Excel VBA is easy. Just do a Save As..."Mission Critical Finance Model v2.xlsm", then "Mission Critical Finance Model v3.xlsm", then "Mission Critical Finance Model v3a (temp with changes for Jim).xlsm" and then "Mission Critical Finance Model v4 (maybe-trying to merge 3a back-DO NOT USE).xlsm" and so on...

Declanomous 9y ago
That's true. Good luck doing anything with that though. My main database was up to version 2051 by the time I left. The class I have nightmares about is the "compile and backup" class I wrote, that decompiled the database, appended the date to each file, compared all of the non-data and non-binary components, recompiled the database with the most recent versions, and then made sure every database was linked to the most recent versions of other databases. Why did I decompile everything? Well I learned that unless the file size changed markedly, the deduplication feature of the file replication system that our company used assumed the files were the same. Well Access databases can stay the same size for a long time unless you run a compact and repair on them. So I lost several weeks of work more than once because the system decided that an older version and a newer version of a database were the same. Without that stupid "feature" I would have just had the production Access file be named Access-prod.accdb and updated it each day. I honestly have so many nightmares about that job I'm intentionally doing easy work until I can convince myself that files don't magically delete themselves without a trace and administrators aren't deliberately evil.
- agar 9y ago
  Just to clarify, I was being sarcastic about it being easy (or actually being version control, for that matter). But thanks for the horror story. What a nightmare.
- CydeWeys 9y ago
  ... wow. Instead of hashing the file contents, it just checked file size? Who came up with this algorithm?! There's plenty of applications that use files of fixed large sizes initialized at install, for performance reasons or just simply so if you're going to run out of space you do so during interactive installation, rather than silently breaking later on.
  - Declanomous 9y ago
    From what I can determine, the authoritative version was always the one with the later timestamp. This might have worked at least in theory, however there were always issues. The foremost issue was that the timeserver was virtualized, and drifted to an unbelievable degree. This issue was compounded by the fact that we used citrix virtual machines for some applications, and our local desktops for other applications. The latter issue wasn't a problem until they moved the citrix servers from an on-site server room to cloud provider about 40 miles away. (I think this is when the timeserver became virtualized as well) At that point the latency between the citrix sessions and the local desktops was such that I could create a document on the file share, edit it on my local desktop, close it, save it, and then open it on the remote desktop and it would lock the replication from my computer and lose all my work. My department managed to wipe out the changes every other department had made to the yearly budget workbook because of this interaction. Of course part of the issue was using Excel as a shared document, but that issue was hugely compounded by the fact that our network infrastructure was bananas. For instance, building access databases using the Microsoft recommended separate front-end/back-end files, with each individual user having their own front-end file actually managed to make the issue occur more frequently. If you used a single access database file on a shared drive the issue was substantially less likely to occur. I can only imagine this is because the dedupe was postponed until everyone was done using the file, and the file was more likely to substantially change in size when multiple people were using it. The IT director had an incredibly fragile ego, and he attacked anyone who challenged him in any way. He hired a truck driver (with absolutely no IT experience) to work the IT help desk, which I assume was because he wanted a sycophant. He reported me to HR any time I did anything he didn't understand or which perceived to be a challenge of his power. He reported me to HR for using open-source software, with a description of open source software that made it sound like Wikipedia meets programming, and only used by hackers. He also reported me to HR after he heard I called our IT situation Kafkaesque. As far as nightmares go, that might have been my favorite, because my defense was literally "it is Kafkaesque, and I can prove it." I managed to successfully convince HR that the IT situation was Kafkaesque using examples from Kafka's work, whose response was "well there isn't anything we can do about it, but you aren't wrong." The sensible thing would have been to leave as soon as possible, but I really liked the field I was in, which was kind of specialized. Looking back I stayed way too long.
hexane360 9y ago
Mission Critical Finance Model v4 FINAL_revised (2).xlsm
- agar 9y ago
  Stop looking at my file system!
- jrs235 9y ago
  Mission Critical Finance Model v4 FINAL_revised (2) - Copy (2) (2).xlsm
manigandham 9y ago
Box, Dropbox, etc solve much of the problem
cosmie 9y ago
I was once on a team piloting a new inventory planning model in Excel, testing it out at a handful of distinctively different manufacturing sites to stress test it before paying SAP's ransom to bake it into the official system. When I started, I was backfilling someone who created the Excel model and was responsible for batch exporting data from SAP, crunching the numbers in Excel, and sending that out to the sites every morning. My onboarding consisted of being given his laptop and his login credentials[1], and being met with a desktop full of dozens nested folders of Excel sheets in that exact version control format. I still have nightmares of that week. [1] My manager had successfully kept IT from being able to get the laptop for re-imaging. But my senior VP located on a different continent had to get involved to get IT to reset the guy's password for us.
gaius 9y ago
The funny thing is you can save your spreadsheet into Sharepoint which does have version control and people still do it that way in Sharepoint too...
tmzt 9y ago
Or you could: unzip *.xlsm git add . Probably with some custom diff scripts for the xml files.
- Declanomous 9y ago
  I did most of this in Access. Also, I wasn't allowed to install version control of any sort, so that's really the bigger problem.