This morning I had to process about 5,000 lines of data from a web site into an Excel table (2007 for Win XP). Transmission glitches added two or three cells to a couple of hundred rows. I needed to append one cell to the cell to the left and then move a cell over. A word processor might have done the task but brute force seemed the least pleasant option. It seemed like a simple matter of recording a macro.
At work, I use Excel with the full Ballmer-blessed Office experience. In this environment, I mistakenly believed Excel would record a "copy" and a "paste." Instead, it hard-code the original values into the macro. Hit the keystroke and the cursor moves around and plunks that data into a new cell. Worthless.
Also worthless was F1, and don't even ask about our IT department. Cussing, I googled and found several web sites with author's showing off their VBA prowess with complex data manipulation. (M$, BTW, kept pointing me to broken links. Go figure.) Finally I found something that almost fit my purposes. After about a half hour, I had a working macro.
1) I bet the exercise would have gone a lot faster if VBA were more procedural than object-oriented. Maybe for a giant project, an object-oriented macro language makes sense. But for crying out loud, it's a MACRO. Nobody is going to land the damn space shuttle with VBA!
2) That is one crappy editor for VBA. I don't understand how Microsoft jockeys aren't going blind; the company's interfaces are based on pixel sized icons.
3) I completed the work with greater appreciation for Apple's lowly scripting language. Granted, I know Applescript better, but I bet if I had a spreadsheet with a decent AS dictionary (Mariner Calc perhaps?), I could have completed the job in 10 minutes, with a script I could use again. This VBA mess I wrote will never again see the light of day.
Excuse the rant ...
mt
At work, I use Excel with the full Ballmer-blessed Office experience. In this environment, I mistakenly believed Excel would record a "copy" and a "paste." Instead, it hard-code the original values into the macro. Hit the keystroke and the cursor moves around and plunks that data into a new cell. Worthless.
Also worthless was F1, and don't even ask about our IT department. Cussing, I googled and found several web sites with author's showing off their VBA prowess with complex data manipulation. (M$, BTW, kept pointing me to broken links. Go figure.) Finally I found something that almost fit my purposes. After about a half hour, I had a working macro.
1) I bet the exercise would have gone a lot faster if VBA were more procedural than object-oriented. Maybe for a giant project, an object-oriented macro language makes sense. But for crying out loud, it's a MACRO. Nobody is going to land the damn space shuttle with VBA!
2) That is one crappy editor for VBA. I don't understand how Microsoft jockeys aren't going blind; the company's interfaces are based on pixel sized icons.
3) I completed the work with greater appreciation for Apple's lowly scripting language. Granted, I know Applescript better, but I bet if I had a spreadsheet with a decent AS dictionary (Mariner Calc perhaps?), I could have completed the job in 10 minutes, with a script I could use again. This VBA mess I wrote will never again see the light of day.
Excuse the rant ...
mt
