Today I really got back into coding in a big way. The iron grip of CiV has loosened some and I am now beginning to really hammer out my Retribution Paladin spreadsheet for the upcoming expansion. Going back to coding after a long vacation really reminds me of some of the incredible highs and lows that it brings; it triggers memories of long, late night programming sessions in the labs at university.
The trick always seems to be the details. I have the structure down no problem and I know exactly what logical path the code needs to follow. My math and logic skills have not degraded since last I worked on my spreadsheet but my knowledge of the programming language has become weak and doddering. This is not helped at all by the fact that VBA has so many issues with the documentation and with bugs in the code running it. For example, I wanted to round up a random number to an integer. I look in the documentation and see the ROUNDUP function which should do exactly what I need it to do. Instead it crashes my program every time I try to run it with a devilishly vague error message. I go through the documentation over and over, I go online and check the documentation there and try to redo and bugproof my code and utterly fail; ROUNDUP simply does not work. It turns out that I can just use the INT function instead and get the results I need but the fact that in a widely distributed Microsoft program a basic built in function crashes the program is wretched. Not that I can blame all my troubles on VBA and Microsoft! I keep forgetting to declare variables properly and VBA is happy to create and delete them on a whim, wreaking havoc with my program. I fail to initialize a few of my many, many variables and that causes all kinds of bizarre behaviour.
Strangely coding ends up being much like an addictive video game in that I lose time while doing it. I sat down early in the morning to do just a little work and ended up noticing that I was hungry hours and hours later. I kept thinking that I had lots of time to do the chores of the day but I ended up doing very little because huge chunks of my day just kept vanishing as I struggled against the machine and against my own incompetence. I love the feeling of a program that hums along, doing precisely what I intended. I love finally finding that stubborn bug and crushing it and watching the numbers flow just the way they should. That high of success is very reminiscent of a video game high I think, just as the drudgery of bug hunting is like the drudgery of killing more monsters to level up.
The most amusing part of all this is that I grumble about my code and Wendy comes over to try to help. She is good at coding and a fresh perspective really does help catch bugs a lot of the time but I have a tremendous independent, proud streak that wants to do it all myself. I don't want help, I don't want someone to wander along and crush the bug I have been hunting... then I still get the annoying part but without the sense of triumph at the end! Of course, I do want help because I want my program to work in the end and I want the bugs squashed, even if I don't get to do all the squashing myself. I am not going to order her away because she is really helpful, but a voice in the back somewhere is shouting
"NO! Don't accept help, you can do it yourself. If you let someone else fix the problem you LOSE!"
As a tip, put Option Explicit at the top of your module. This forces every variable to be declared before use and will throw an error when you try to run a subroutine that has an undeclared variable instead of just trying to wing it.
ReplyDeleteFor roundup, there is no such VBA function, but there is an Excel function with that name. You can access the Excel function with application.roundup(number, precision). Alternatively, you can use the actual VBA function round, which you can trick to do a base roundup instead of round by using round(number-.5, 0)+1.
The internet and the help section for VBA fully believe that there is a VBA function with that name. They sure don't seem to be right, but they tell me all about it and how to use it and are very clear that it is part of VBA code and not excel itself. Why this is I do not know.
ReplyDeleteOption Explicit is exactly what I need to stop myself making one class of terrible mistakes. Thanks! Now I can spend more time on other types of mistakes. :)
It is entirely possible it got added into VBA itself in a future version but the version I'm running here at work has no roundup function in VBA, and the only reference in the help file is to worksheetfunction.roundup which uses the Excel one. (Application.worksheetfunction.roundup is the fully qualified way to access it, but the application.roundup shortcut seems to work for me.)
ReplyDeleteCoding is awesome. We were talking about exploration in video games the other night. Coding is the best exploration game there is.
ReplyDelete