{"id":52,"date":"2019-04-04T20:38:05","date_gmt":"2019-04-04T20:38:05","guid":{"rendered":"https:\/\/keithratner.live\/optionexplicit\/?p=52"},"modified":"2021-08-03T12:42:59","modified_gmt":"2021-08-03T12:42:59","slug":"notes-on-style-vba-coding-style","status":"publish","type":"post","link":"https:\/\/keithratner.live\/optionexplicit\/notes-on-style-vba-coding-style\/","title":{"rendered":"Notes on VBA Coding Style: Maximizing Scalability and Readability"},"content":{"rendered":"\n

Establishing and adhering to a VBA coding style guide enables increased project reusability and scalability. It makes code more readable and, by extension, the coding experience far more enjoyable.<\/p>\n\n\n

\"Notes<\/p>\n

Minimize Horizontal Scrolling<\/h3>\n

Split lines (use the underscore!) and indent. My rationale is that horizontal scrolling takes too long. You want to be able to scan through your code quickly. You also want to be able to view code modules side-by-side, which ties into another upcoming post (TODO…) regarding code modularity – specifically, if your procedure is going to be lengthy, consider placing it in its own module, and ALWAYS use a naming convention. This applies to variables and modules. Pick a convention and stick to it. The VBE (Visual Basic Editor) has split screen, but that only works with a horizontal separator. It is helpful to be able to view code side-by-side, particularly if you’re writing class definitions that may have similar implementations. If your lines are short, you can view more modules side-by-side.<\/p>\n

The screenshot illustrates the following conventions that I like to use:<\/p>\n

Split line after open parentheses and each parameter<\/h4>\n

This includes the first line of any procedure definition (Sub, Property, Function).
Each parameter should be on its own line. Be mindful of proper syntax with regards to commas. This will drive you crazy if you’re not careful. The debugger is your friend. So, for instance:<\/p>\n

Function MyFunction( _\n  strParamOne as String, _\n  strParamTwo as String, _\n  intParamCounter as Integer, _\n  strLastParam as String _\n)<\/pre>\n
Note the comma-space-underscore<\/strong><\/em> syntax for all parameters except the last one. The debugger will yell at you about this, but do yourself a favor and form the habit.<\/p>\n
Split line after Dim statement when declaring multiple variables<\/h4>\n
This ties into my convention for splitting lines after multiple parameters. It may not be necessary for single parameters, but consider that VBA permits you to use up to 1,203 characters to name your variables<\/a>. That enables you to be descriptive with your variable names, which you should be, because someone will probably have to decipher that code of yours eventually – and it might just be you. NEVER use non-descriptive variable names such as a, b, c or x, y, z. Just don’t do it. So, conserve your horizontal space on each line.\u00a0 For instance:<\/p>\n
Dim strMyVariable as String<\/pre>\n
Keeping a single variable declaration on one line may be acceptable. It’s up to you. Just make a decision and stick to it. If you have a long variable name, you might want to conserve some space like this:<\/p>\n
Dim _\n  strWorksheetNameConcatenatedFromSourceWorkbook as String<\/pre>\n
When declaring multiple variables, I like to give them each their own indented line:<\/p>\n
Dim _\n  strMyFirstVariable as String, _\n  strMySecondVariable as String, _\n  intMyThirdVariable as Integer, _\n  strMyLastVariable as String<\/pre>\n
Split lengthy strings into separate lines<\/h4>\n
Your mileage may vary on this, but I think 80 characters is a good limit to set. Split your strings and concatenate them with an ampersand, then use ampersand-space-underscore<\/strong><\/em> to split your lines:<\/p>\n
strMyLongString = _\n  \"This will be a \" & _\n  \"run-on sentence with \" & _\n  \"a few variables thrown in \" & _\n  \"here: \" & _\n  strMyFirstConcatenatedStringElement & _\n  \" and here: \" & _\n  strMySecondConcatenatedStringElement & _\n  \" to properly illustrate my \" & _\n  \"point.\"<\/pre>\n
Note that there is a limit on VBA line continuations (see Microsoft Office Dev Center Article<\/a>). Per the specs: Your code should have no more than 25 physical lines joined with line-continuation characters or more than 24 consecutive line-continuation characters in a single line. Make some of the constituent lines physically longer to reduce the number of line-continuation characters needed, or break the construct into more than one statement.<\/p>\n
Be Verbose<\/h4>\n
Function and Object names get compiled down so their length does not affect performance. VBA is meant to enable verbosity. Use camel case and name your functions and objects explicitly. Establish a naming convention early on if possible. For example, the following variable has a three letter prefix indicating its data type (string, in this case), then a concise camel case description of the variable’s purpose.\u00a0<\/p>\n
strWorksheetName<\/pre>\n
Whether you use this VBA coding style guide or build your own, the important thing is to adhere to it throughout your project, especially if you are working with a team. It helps to speak the same language, both programmatically and stylistically. The less guesswork is involved, the more efficiently you can get things done.<\/p>\n


<\/pre>","protected":false},"excerpt":{"rendered":"
Establishing and adhering to a VBA coding style guide enables increased project reusability and scalability. It makes code more readable and, by extension, the coding experience far more enjoyable. Minimize Horizontal Scrolling Split lines (use the underscore!) and indent. My rationale is that horizontal scrolling takes too long. You want to be able to scan […]<\/p>\n","protected":false},"author":1,"featured_media":55,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"inline_featured_image":false,"_exactmetrics_skip_tracking":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_feature_clip_id":0,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_post_was_ever_published":false},"categories":[111],"tags":[136],"class_list":["post-52","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-article","tag-style"],"krwpengineoptions-featuredonwpengineblog":"","jetpack_featured_media_url":"https:\/\/i0.wp.com\/keithratner.live\/optionexplicit\/wp-content\/uploads\/sites\/29\/2019\/04\/notes-on-style.png?fit=724%2C962&ssl=1","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/pgsIIA-Q","jetpack-related-posts":[{"id":319,"url":"https:\/\/keithratner.live\/optionexplicit\/why-object-oriented-vba\/","url_meta":{"origin":52,"position":0},"title":"Why Object-Oriented VBA?","author":"Keith","date":"March 23, 2023","format":false,"excerpt":"Object-oriented programming is an essential concept in modern software engineering that encapsulates data and functionality within a single entity called an object. Microsoft's Visual Basic for Applications (VBA) is a popular programming language used in creating software for Microsoft Office applications like Excel and PowerPoint. Although VBA has its roots\u2026","rel":"","context":"In "Article"","block_context":{"text":"Article","link":"https:\/\/keithratner.live\/optionexplicit\/category\/article\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":563,"url":"https:\/\/keithratner.live\/optionexplicit\/update-access-vba-saved-imports-exports\/","url_meta":{"origin":52,"position":1},"title":"Update Access VBA Saved Imports Exports: A Step-by-Step Guide","author":"Keith","date":"May 3, 2024","format":false,"excerpt":"Updating Access VBA saved imports exports is essential when dealing with external data sources. This step-by-step guide will show you how to update Access VBA saved imports exports by dynamically changing the file path within your import\/export specifications. In this article, we'll walk through a powerful VBA script that allows\u2026","rel":"","context":"In "Article"","block_context":{"text":"Article","link":"https:\/\/keithratner.live\/optionexplicit\/category\/article\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/keithratner.live\/optionexplicit\/wp-content\/uploads\/sites\/29\/2024\/05\/Screenshot-2024-05-03-133800.png?resize=350%2C200&ssl=1","width":350,"height":200},"classes":[]},{"id":160,"url":"https:\/\/keithratner.live\/optionexplicit\/edge-git-vscode\/","url_meta":{"origin":52,"position":2},"title":"Edge, Git and VS Code Integration","author":"Keith","date":"May 23, 2019","format":false,"excerpt":"In my VBA project, on common UserForms, I have three labels with click event handlers attached: First is a pause button, which just triggers a Stop command, launching the debugger\/VBA IDE. Second is a save button, which will save ThisWorkbook. Third triggers the ExportModules procedure. Source file updates are reflected\u2026","rel":"","context":"In "Article"","block_context":{"text":"Article","link":"https:\/\/keithratner.live\/optionexplicit\/category\/article\/"},"img":{"alt_text":"VS Code Git Updates","src":"https:\/\/i0.wp.com\/keithratner.live\/optionexplicit\/wp-content\/uploads\/sites\/29\/2019\/05\/vscode_git_updates.png?resize=350%2C200&ssl=1","width":350,"height":200},"classes":[]},{"id":316,"url":"https:\/\/keithratner.live\/optionexplicit\/the-importance-of-visual-basic-for-applications-vba\/","url_meta":{"origin":52,"position":3},"title":"The Importance of Visual Basic for Applications (VBA)","author":"Keith","date":"March 23, 2023","format":false,"excerpt":"Visual Basic for Applications (VBA) is a programming language that is used to automate tasks in Microsoft Office applications, including Excel, Word, and PowerPoint. VBA allows users to create customized macros, automate repetitive tasks, and interface with external software, making it an essential part of many businesses' software workflows. In\u2026","rel":"","context":"In "Article"","block_context":{"text":"Article","link":"https:\/\/keithratner.live\/optionexplicit\/category\/article\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":35,"url":"https:\/\/keithratner.live\/optionexplicit\/export-modules-to-folder\/","url_meta":{"origin":52,"position":4},"title":"Export Modules to Folder","author":"Keith","date":"April 3, 2019","format":false,"excerpt":"This will create a folder using the base name of your Excel file, which is the filename without its extension, along with a vba subfolder. Your VBA modules will be placed there. Pass UseTimestamp:=True to put each export in its own timestamped subfolder so repeated runs don't overwrite earlier exports.\u2026","rel":"","context":"In "Snippet"","block_context":{"text":"Snippet","link":"https:\/\/keithratner.live\/optionexplicit\/category\/snippet\/"},"img":{"alt_text":"Export Modules","src":"https:\/\/i0.wp.com\/keithratner.live\/optionexplicit\/wp-content\/uploads\/sites\/29\/2019\/04\/4-4-2019-2-16-36-AM.png?fit=665%2C496&ssl=1&resize=350%2C200","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/keithratner.live\/optionexplicit\/wp-content\/uploads\/sites\/29\/2019\/04\/4-4-2019-2-16-36-AM.png?fit=665%2C496&ssl=1&resize=350%2C200 1x, https:\/\/i0.wp.com\/keithratner.live\/optionexplicit\/wp-content\/uploads\/sites\/29\/2019\/04\/4-4-2019-2-16-36-AM.png?fit=665%2C496&ssl=1&resize=525%2C300 1.5x"},"classes":[]},{"id":322,"url":"https:\/\/keithratner.live\/optionexplicit\/why-use-vba-in-excel\/","url_meta":{"origin":52,"position":5},"title":"Why Use VBA in Excel?","author":"Keith","date":"March 23, 2023","format":false,"excerpt":"Excel VBA, or Visual Basic for Applications, is a programming language that allows developers to automate tasks and build applications within the Excel environment. Utilizing Excel VBA can dramatically improve your productivity and increase your Excel skillset, as well as provide you with the opportunity to create customized macros for\u2026","rel":"","context":"In "Article"","block_context":{"text":"Article","link":"https:\/\/keithratner.live\/optionexplicit\/category\/article\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]}],"_links":{"self":[{"href":"https:\/\/keithratner.live\/optionexplicit\/wp-json\/wp\/v2\/posts\/52","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/keithratner.live\/optionexplicit\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/keithratner.live\/optionexplicit\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/keithratner.live\/optionexplicit\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/keithratner.live\/optionexplicit\/wp-json\/wp\/v2\/comments?post=52"}],"version-history":[{"count":10,"href":"https:\/\/keithratner.live\/optionexplicit\/wp-json\/wp\/v2\/posts\/52\/revisions"}],"predecessor-version":[{"id":231,"href":"https:\/\/keithratner.live\/optionexplicit\/wp-json\/wp\/v2\/posts\/52\/revisions\/231"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/keithratner.live\/optionexplicit\/wp-json\/wp\/v2\/media\/55"}],"wp:attachment":[{"href":"https:\/\/keithratner.live\/optionexplicit\/wp-json\/wp\/v2\/media?parent=52"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/keithratner.live\/optionexplicit\/wp-json\/wp\/v2\/categories?post=52"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/keithratner.live\/optionexplicit\/wp-json\/wp\/v2\/tags?post=52"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}