So the other day I was asked to do a program review for an educational entity. One thing that struck me is that they’ll teach them about nested IF statements in an Excel worksheet, but won’t take the logical jump to a VBA script using a CASE statement, or using DLOOKUP in Access, etc. Especially since they were doing payroll worksheets that could have benefited greatly from VBA for the tax calculations.
I should define VBA, it’s Visual BASIC for Applications. Note the BASIC – that stands for Beginners All-Purpose Symbolic Instruction Code. Some command keywords, LET, DIM, IF, CASE, DLOOKUP, and math functions like +, -, *, /, and MOD. some string functions like RIGHT, LEFT, MID, INSTR (In String, it returns the position of the first occurrence of the character in the function parameter.)
One interesting little piece I wrote took a 10 character UPC code, and then calculated the check digit. The algorithm is to sum up the digits, then round to the nearest 10’s unit and the difference between your sum and the nearest 10’s unit is the check digit.
That works out to a function that looks like this C = (UPCSum MOD 10) – in essence it give you the remainder which is useful for finding how close you are to the next tens unit. So now we land at C=10-(UPCSum MOD 10) which gives us what we need to add to get there.
Here’s another snipet of code, one that prepends “00” to each value in a column of data:
Public Sub FixUPC() Dim dbs As Database, rst As Recordset Dim strSQL As String Dim UPC As String Dim PLU As String Set dbs = CurrentDb strSQL = "SELECT * FROM pre-export-frz_veg" Set rst = dbs.OpenRecordset(strSQL) rst.MoveFirst rst.MoveLast With rst .Edit UPC = .Fields("UPC") UPC = "00" + UPC .Field("UPC") = UPC .Update End With rst.Close Set dbs = Nothing End Sub
Ok now I’ll explain what it all does:
First let me explain this was used to pad UPC numbers out to 14 characters. Standard UPC without check digit is 10 characters, with check 11 characters and GTIN (Europen code) 14 characters. You see where I’m going here.
1. Public Sub FixUPC() – all this does is define a subroutine or section of code. The Public keyword says it is accessible to all other subs, the FixUPC is the name of the subroutine, while the () is the default for closed parameters.
2. Dim dbs As Database, rst As Recordset – Here we see Dim which Dimension or Dimension a variable of type. So dbs is defined as the variable Database connection descriptor. rst is defined as the variable to contain the records returned by the query.
3 through 5. These dimension string variables for various things like the SQL language string, or the the UPC string to contain the UPC number we’re going to perform the operation on. You may note that PLU isn’t used anywhere in the code. It’s a relic of modular code. In other words, I have this saved so that I could re-use it later in other modules.
6. Set dbs = CurrentDb – Simply sets the dbs variable to point to the database in use at current
7. strSQL = “SELECT * FROM pre-export-frz_veg” – Defines the SQL (Standard Query Language) to get the data you want. The way it’s defined here it just gets everything from the table pre-export-frz_veg.
8. Set rst = dbs.OpenRecordset(strSQL) – rst contains the record set returned by the SQL statement referenced in line 7.
9. rst.MoveFirst – Moves to the first record in a recordset. Notice the rst+dot convention. MoveFirst is a method of the object rst.
10. rst.MoveLast – MOves to last record in a recordset. This is sometimes required to get the database result set to fully load. See note 9 above.
11. With rst – In other words, do this until we reach the end of the recordset (rst).
12. .Edit – this allows you to edit the contents of a database row/column cell. Notice the dot in front, that’s a reference to the rst object.
13. UPC = .Fields(“UPC”) – This is a meaty part. It says assign the variable UPC with the value from the field in the record called UPC.
14. UPC = “00” + UPC – This adds the string literal “00” to the front of the UPC code.
15. .Field(“UPC”) = UPC – Again we see the dot operator, here we say to replace the content of the field UPC with the value of the variable UPC.
16. .Update – This commits the record.
17. End With – outer boundary of the With statement.
17. rst.Close – Closes the recordset. This further commits the change.
18. Set dbs = Nothing – Just good practice. This is useful when you’re dealing with multiple database tables.
19. End Sub – End the subroutine. Required for every one.
See, very simple.
During the open discussion I explained that during my I.T. career most of the programming I did was either BASH scripting, or VBA scripting with a smattering of modifying PHP scripts. Never ran across C though did hit C++ a couple times, and even a little COBOL.
Instead they reserve the C++ course for math whizzes. Even if you do games programming and even now 99% of that is flash/ActionScript based (Been there, done that, got the t-shirt) most of the physics is built into the language already. And that’s what you need, a good physics course, not so much any math beyond intermediate algebra.
My friend says I’m being subversive by proposing this. That I would give people better data skills to the point where they’d tell their bosses to go to hell. If so, I like it!
But the myth still exists in education that you can’t take a programming class until you’ve taken high end math classes. I can tell you that this is as far from the truth as you can get.