This post is ongoing and I will add to it as we progress.
Info Boxes
Note I have put what I call a comment block here. Now, if you just use one-off functions and have just a couple functions sitting around that you use, you may not think this is necessary. However, if you are planning to put together a database or a system where you might have many functions and/or macros, my opinion is, it’s a good idea to put at the top some information about the function so that you and others looking back on it later have a little info about it. So most functions from here on out will have that blurb. List or describe anything special about it or any required arguments it needs.
Function fDAORecordsetDemo() '======================== ' Name : fDAORecordsetDemo ' Author : Erica L Ingram ' Copyright : 2019, A Quo Co. ' Call command: Call fDAORecordsetDemo ' Description : function description, special notes ' list here any required arguments '=======================
Short Lines of Code
It is a good idea to keep short lines when you code. To continue onto the next line in VBA, you type a space followed by an underscore, then enter to go to the next line. You may indent after that however you like for looks, but those three characters need to be typed 1, 2, 3; space, underscore, enter; just as I described.
Blank Space
Indent and add blank lines frequently and often but in as consistent a manner as possible to make it easier to read. For example, you will see me indenting actions/code lines that take place within if-then-else statements and loops and setting it off with blank lines.
Private v. Public Function Names
I like to follow a general rule that private function names start with ‘f’ and public ones start with ‘pf’, although that is not a convention or anything.
Variable Names
You can’t have symbols or spaces in your variable name or start it with a number. You can use underscores if you like, although I use something called camelCase instead of underscores. Camelcase is where the first letter is lowercase and then each word within is capitalized, such as camelCase. It is a good habit to use a naming system. There are various ones out there. I start all variables with a letter that represents what type they are (s for string, o for object, rst for DAO.recordset, i for integer, etc.) and then follow it with a word or phrase that describes what it holds or what it does. All variables must start with a letter. They can be between 1-255 characters.
Try to use terse, unique names that describe what it’s doing, or storing, or being used for so that, if some random person has to go back through your code later, you’ve made it easier for them to read.
Pseudocode
It’s a really good practice to get into, especially the more complicated your functions get, to write some of what is generally called ‘pseudocode’ before you start.
Much like what you guys probably already do when you write a brief or a paper of some sort, you outline it real quick to make sure you stay on the track you meant to stay on. Right?
Well, this is very similar and extremely open ended. Just make a few notes/lines or outline of what the function is supposed to do or its parts. It could be words, phrases, or lines of code, anything that helps you remember what direction and where the function is going. I recommend making them all comments by putting an apostrophe at the beginning of the line.
If you then write the code around the pseudocode so that the pseudocode outline remains within the code, you’re left with a nice function that actually contains notes about what you did and usually suffices to explain later when you or others look back at it.
The more complicated solution that you build, the more helpful pseudocode is, like if you have a lot of loops or something. The compartmentalized nature of what we’re doing really lends itself to outlining and pseudocode, so consistently writing it into your functions is a good habit to get into and keep.
Debug.Print/Immediate Window
Ctrl + G opens the debug window or the window where “Debug.Print” prints to.
Commenting
In VBA, an apostrophe begins a comment for the rest of the line.
Labels
Notice the use of the phrase “Line2” in the following example. This is called a ‘label’ and you MUST start a label from the beginning of the line, no indents. It allows you to mark a place in your code and refer or go to it:
Function fFunction()
Dim item As Variant
Dim sFileTypes() As String
sFileTypes = Array("trs", "trm")
For Each item In sFileTypes
If fiCurrentFile Like "*trs*" Then GoTo Line2
ElseIf fiCurrentFile Like "*trm*" Then GoTo Line2
Else
Debug.Print "Not an FTR player extension!"
End If
Next
Line2:
Debug.Print "Done with FileTypes!"
End Function
Troubleshooting Tips
Try hitting Ctrl + Pause/Break to interrupt the code execution if you need it. Sometimes you will need to hold Ctrl and press the Pause/Break button many times to interrupt.
When you are dealing with complex strings in variables, it’s a good idea to send it to the debug/immediate window, Ctrl + G, with the command “Debug.Print” so you may ensure the result is correct:
Function fFunction() Dim u as String Dim v as String Dim w as String Dim x as String Dim y as String Dim z as String v = "A " w = "E " x = "I " y = "O " z = "U" u = v & w & x & y & z Debug.Print u End Function
Disclaimer
**While we have made every effort to provide accurate information, A Quo Co. and Erica L. Ingram assume no responsibility or liability for any errors or omissions in the content of this blog. The information contained in this blog is provided on an “as is” basis with no guarantees of completeness, accuracy, usefulness, or timeliness.