Learning Macros for MS Word/Excel

  • I was starting to experiment with some macros for both MS Word and MS Excel, and I though I should ask if anyone knows of a good source of script, code or instruction.

    @pierre and @toxuin : I know this is generally in your guys wheelhouse and probably have a good answer.

  • 3D

    That excel sorcery is beyond me. I know there are formulas and even Microsoft © Visual Basic ® macro stuff, but I have never touch it more than a #SUM() thingy. Sorry.

  • I make great efforts to avoid working with or supporting Microsoft or Apple products. I can probably help you do do whatever you need done, but there is almost always a better (less wrong) way to do anything that needs a spreadsheet with macros. I’d advise that you learn a real programming language, and a database instead.

    People who build spreadsheets that are complex enough to need custom macro programming are doing things very, very wrong in almost all cases. What usually starts out as “I’m doing some quick calculations on a data set and would like to automate a bit of it.” (the correct use of a spread sheet) grows until it resembles a database. But without referential integrity constraints, row-level locking or any regard for efficient computability. Then they try to backdoor those features in with macros after it becomes too big to maintain by hand. They should be using Matlab/Scilab/Octave or R, or Python+Sqlite or almost any other tool chain intended for data of the scale they are working with.

    If this sounds harsh, consider the source. I once worked at a company where the CFO decided that accounting software was too complicated. Out comes excel, because they teach the shit out of excel in business school. There were hundreds of spreadsheets on the hard drive when the CFO suddenly up and quit with no notice. All of them reached out and pulled data out of other files on disks. Cross file references are a huge warning sign. As far as I could tell, the one sheet hit the (at that time) 65535 row limit in excel. Bad-ref errors come up everywhere, CFO spent a week trying to fix, realized they were going to be in shit and quit. A week before payday. New CFO hired in a rush. Opens accounting software we bought and paid for and goes: Why are there no entries for the last 8 months? Finds broken spreadsheets. I get called into it to “Just make it work” with 3 days before pay day. We wound up re-issuing the previous set of paychecks with the serial numbers filed off… and I spent 2 weeks getting it to work well enough to import the data into accounting software. This is the worst spreadsheet thing I’ve ever been involved in, but I always advise people to avoid building complex things in spreadsheets. Moreover, there is job security in being the person who can fix these horrible things, but it leads to bad digestion and lost sleep.