Welcome to my Helpful Section for VBA. Here you will find helpful articles and advice on best practices for VBA. You can read about any updates to this section or follow my work on Facebook. If you have used any of my other work and wish to donate, you can do so on my Donate Page

Use the Quick Search to find an article of choice. You can also Sort the list either ascending or descending by Name, or by the links that have a File available to view or download. You can press Clear to reset the Search:





VBA Best Practices

[Back to Search]

Quick Links
My Quick Tips
Preparing the Visual Basic Editor
Preparing the Code Modules
Variable Naming Conventions
Using Comments
Additional Code Module Formatting
Using Defined Names
Using Other Peoples Code
Worksheet Protection & Securing Data Integrity
Hiding Subroutines in Code Modules & the Macro Dialog




My Quick Tips

[Back to VBA Best Practices]


(1) Always rename your Code Modules like 'Sheet1' and key Objects like 'Pivot Tables', 'Tables', 'Charts' etc. You should then work with the properties and methods in your Code ie. for an options Code Module renaming it to 'Options' makes a lot of sense. Here is an example of writing Code to select the Worksheet and the Table 'Options' within that Worksheet:

Options.Select
Options.ListObjects("Options").DataBodyRange.Select
 

(2) When working with an Object extensively, try to use the 'Set' command to assign a like Variable and then work with the properties and methods of the Object like this:
Dim objTable As ListObject
Set objTable = ActiveCell.ListObject

objTable.DataBodyRange.Select
 

(3) Comment extensively, but make the comments relevant and add any insights you may have. Here is an example of commented Code where the comments outweight the actual Code but are useful at explaining what is happening when an option is double-clicked within a Table within a Worksheet:
    ' // as the Code exits the above procedure we will check & allow a toggle on the "Auto Column Fit" option in the "Options" Worksheet
    '    Font is Marlett and "a" = a tick, "" (blank) is unticked
    If Not Intersect(Target, Range(Options.ListObjects("Options").DataBodyRange.Find("Auto Column Fit").Offset(0, 1).Address)) Is Nothing Then
        ' // the use of IIf is to return one of two parts, depending on the evaluation of an expression
        '    therefore depending on whether the Cell contains the value "a" it either sets the Cell to "" (blank) or "a"
        '    so if the Cell contains a tick it will toggle this off and leave a blank.  if not it will leave a tick
        Range(Target.Cells.Address).Value = IIf(Range(Target.Cells.Address).Value = "a", "", "a")
        Cancel = True
    End If
 

(4) Always add a Changelog into your Code to describe any changes made between versions. Depending on what you are writing it may also be beneficial to leave a Note and a Changelog within a major Subroutine or Function





Preparing the Visual Basic Editor

[Back to VBA Best Practices]


(1) I like to turn off the 'Auto Syntax Check' setting in the Visual Basic Editor. While you could argue it is useful I consider it annoying as I like to move around when I am Coding and jumping to and fro between unfinished Code fragments causes Compile Error Dialog boxes to pop up. To turn off this option press ALT+F11 to go into the Visual Basic Editor. Select Tools, 'Options...' and in the Editor Tab underneath Code Settings uncheck the 'Auto Syntax Check' Checkbox.

(2) I also turn on the 'Require Variable Declaration' setting in the Visual Basic Editor. This will automatically add a statement to each Worksheet Code Module, the ThisWorkBook Code Module and all subsequently inserted Code Modules that will force explicit variable declaration. In other words you must declare all variables or you will receive an error. To turn on this option press ALT+F11 to go into the Visual Basic Editor. Select Tools, 'Options...' and in the Editor Tab underneath Code Settings check the 'Require Variable Declaration' Checkbox. The option will add the following single statement to all Code Modules:

Option Explicit
 

(3) I like to use the 'Comment Block and 'Uncomment Block' to comment large amounts of Code. It makes commenting a simple task. To use this editing feature, right-click on the Toolbar area of the Visual Basic Editor at the top of your screen. Select the Edit option to make the Edit Toolbar visible. Now, when you want to comment or uncomment Code blocks you can do it quickly by selecting your Code with the Mouse and then clicking the small 'Comment Block' & 'Uncomment Block' icons on the Toolbar.





Preparing the Code Modules

[Back to VBA Best Practices]


(1) In Worksheet Code Modules I will leave the force explicit variable declaration statement (Option Explicit) at the top and in the ThisWorkbook Code Module I leave the force explicit variable declaration statement (Option Explicit) and a comment (ie. see mdlMyCodeModule) to refer the user to any Public Code Modules that are used within the VBA Project. I may also add an options to set default array subscripts to 1 (Option Base 1) and to set the default comparison method to use when comparing string data (Option Compare Text) like so:

' force explicit variable declaration
Option Explicit

' set default array subscripts to 1
Option Base 1

' set the default comparison method to use when comparing string data
Option Compare Text

' see mdlMyCodeModule for Subroutines & Functions

If I need to add any variable declarations, Subroutines or Functions to Worksheet Code Modules I declare Constants first and then local variables. I will add the Subroutines and Functions at the bottom of the Worksheet Code Module. I like to declare everything as Private and expose Subroutines and Functions from a separate Code Module. I do not ever change Worksheet Code Module Object names unless I need to Refer to them from another Code Module. I may also add in a Code Module Description but only if it is absolutely neccesary.

(2) I like to rename any inserted Form Code Modules by prefixing them with 'frm', any standard Code Modules by prefixing them with 'mdl' or 'md' and any Class Modules by prefixing them with 'cls' like this:
' Form Code Module Naming Convention
frmJuiicy

' Code Module Naming Convention
mdJuiicyMenu
...or mdJuiicyMenu

' Class Module Naming Convention
clsMatrix
 
In UserForm Code Modules I will leave the force explicit variable declaration statement (Option Explicit) and any other Option statements at the top. I will follow Worksheet Code Module conventions, however if the UserForm is the VBA Project then I will follow the schema for the Public Code Modules (although you cannot use Public Declarations). Ultimately you will need to refer to a UserForm Object in order to display it, so I usually change the UserForm Object Name to something meaningful. I also like to be able to enter and view the UserForm Code Module quickly. You can press F7 to do this but I like to leave the following Event Handler in situ so that I can simply double-click anywhere on the UserForm background to enter and view the Code (you can use this technique to your advantage to position yourself in the UserForm Code just above any added Subroutines or Functions).

'
'
' ## UserForm_Click, not used
' =========================================================================================================
Private Sub UserForm_Click()
 ' allows us to double-click the userform to enter the code module anywhere this subroutine is placed...
End Sub

Please Note: one thing to remember is that Constants, fixed-length strings, arrays, user-defined types and Declare statements are not allowed as Public members of Object Code Modules.

(3) In Public Code Modules I use the following schema:
- a full Description of what the Code Module is used for, details about the Project and / or Code Attribution
Details about the Project may (or may not) include any of the following:
VBA Object Name
Filename
Author
Date
Version
Change Log
Defined Names (listed from the Workbook)

- any required Option <Keyword> Statements
- Public API Declarations with both 32 & 64 bit Excel Directives
I used to wrap these type of Declarations using the '_' (underscore) character to break apart the lines. I no longer do this and prefer to leave them as 1 Declaration per line.

- Public Constant Declarations
- Public Enum (enumeration) & Types
- Public Variables
- Private API Declarations with both 32 & 64 bit Excel Directives
I used to wrap these type of Declarations using the '_' (underscore) character to break apart the lines. I no longer do this and prefer to leave them as 1 Declaration per line.

- Private Constant Declarations
- Private Enum (enumeration) & Types
- Private Variables





Variable Naming Conventions

[Back to VBA Best Practices]


(1) Some Guidelines you can adopt
- When selecting identifiers (words) to use for your variables choose easily readable identifier names and try to favor readability over brevity (it's not cool if nobody can understand it)
- Try not to use numeric characters or underscores (except for Constant Declarations)
- Avoid using using identifiers that conflict with VBA keywords ie. 'Error'
- Avoid using abbreviations or contractions as part of identifier names
- Try not to use any acronyms that are not widely accepted, and even if they are, only when necessary
- Use semantically interesting names rather than language-specific keywords for type names

(2) Using Dim to declare VBA Variables
I always use Dim to declare my VBA variables as opposed to leaving it to the compiler to ascertain the variable Type. I like to declare variables on a single line for clarity but there is nothing wrong with multiple one line variable declarations providing you add the 'As' part and explicitly name the Type of variable that you are declaring for each declaration. I used to break apart variable line declarations using '_' (underscore) but I no longer do this as it not easy to add comments around the variables. Using the 'Option Explicit' statement at the top of every Code Module will force explicit variable declaration. Here are a couple of examples:

'
' single line variable declarations, my preferred method
'
 
' defines an array used to hold Worksheet jagged array data
Dim vntData As Variant
 
' defines a data item used to reference items within the data array
Dim lngDataItem As Long
 
' defines the data array item count
Dim lngDataItemCount As Long
 
'
' single line, multiple type variable declarations, an alternative method
'
Dim lngRows As Long, lngColumns As Long, strItemName As String ' not as easy to add clear & concise comments!
 
Please note: A VBA Variant data type can hold any type of data but uses more memory and requires VBA to guess what kind of data the variable is going to hold. If you know the data Type, try to explicitly use that Type in your declaration. Of course they may be times when you cannot do this for example when picking up Worksheet data en masse.

(3) Give your Variables a descriptive name
I now like to give all of my variables meaningful names using camelCasing. The camelCasing convention, capitalizes the first character of each word except the first word (I also use this for Parameters within Subroutines and Functions by adding a 'my' prefix). By adding a prefix to the variable Name you can quickly identify the data Type given to the variable at the point of declaration. By using a lowercase prefix you also segregate variables that may look similar to Subroutine or Function Names. The table below lists the naming conventions that I now use for variables:
' Integer
Dim intItem As Integer

' Long
Dim lngItem As Long

' Double
Dim dblItem As Double

' Single
Dim sngItem As Single

' Currency
Dim curItem As Currency

' Byte
Dim bytItem As Byte

' Date / Time
Dim dtmItem As Date

' Boolean
Dim blnItem As Boolean

' String
Dim strItem As String

' Variant
Dim vntItem As Variant

' Range
Dim rngItem As Range

' Workbook
Dim wbItem As Workbook

' Worksheet
Dim wsItem As Worksheet

' Object
Dim objItem As Object

' Collection Object
Dim colItem As Collection

' anything else
Dim objAnythingElse as Object

'
' Other more bespoke naming conventions I may use
'

' Prefixing using VBA prefix for Named Types
Const vbDoubleLine = vbNewLine & vbNewLine

' Property, may be any variable Type but I add a 'p' prefix
' I would store this in a Class Module
Private pItem As String

' Where there is a PascalCasing naming convention for a Type not defined above
' I would transpose the Capital letters of each word to a Lower Case prefix
' For example the Variable Type FormatCondition  
Dim fcItem as FormatCondition
 

(3) Constant Declarations for Defined Values
Whenever I define a Constant variable Type for example in the case of a MAXIMUM or MINIMUM value I like to UPPERCASE the declaration entirely and split the individual words that make up the naming of the variable using the '_' (underscore) character. I just think it looks nice and clear. To see what I mean take a look at the Code below:
Private Const MIN_TRAIL = 1

Private Const MAX_TRAIL = 100

Private Const MIN_CORNER_ROUNDNESS = 0

Private Const MAX_CORNER_ROUNDNESS = 1    

Private Const MY_VARIABLE as String = "Some Value"          

(4) Using the camelCasing naming convention for Parameter Variable Declarations for internal Subroutines & Functions
Whenever I declare Variables used to pass Parameter in Subroutines and / or Functions that are used internally I usually use camelCasing with a 'my' prefix (a legacy from writing in PERL). The camelCasing convention, capitalises the first character of each word except the first word. This allows me to instantly pick out the variables used as Parameters. Any variables within the Subroutine or Function are named using the conventions set out in section 3. Take a look at this Function that returns a valid CurrentRegion Range Object (or Nothing which you can test using the TypeName Function) given a Worksheet ie. Sheets("Sheet1") and a Key ("Product Code") to see what I mean:
Private Function GetCurrentRegion(ByVal myWorksheet As Worksheet, ByVal myKey As String) As Range

    ' find the Cell in a Worksheet given a Key
    Dim rngCellToFind As Range
    Set rngCellToFind = _
    myWorksheet.Cells.Find(myKey, Cells(1, 1), xlValues, xlWhole, xlByRows, , True) ' easy to pick out the 2 Parameters!

    ' return a valid CurrentRegion Range or Nothing
    If Not rngCellToFind Is Nothing Then _
       Set GetCurrentRegion = rngCellToFind.CurrentRegion Else _
       Set GetCurrentRegion = Nothing

End Function
Please Note: I do not always adopt the 'my' prefix, for example consider if you are writing a Function that other people need to use with a lot of Parameters. It may be better if you name these so that when a user is passing the Parameters the Intellisense delivers a meaningful description of what is required. For example I would probably rather see "ByVal RangeHeader as String" as opposed to "ByVal myRangeHeader as String".

(4.1) Using the PascalCasing naming convention for Parameter Variable Declarations for exposed Subroutines & Functions
Whenever I allow a user to pass in or select an Enumeration for a Named Argument for exposed Subroutines & Functions I like to use PascalCasing. I use this in Preloader. Here is an example of what I mean:
    If Preloader.ControlRunning = True Then Exit Sub

    Preloader.ControlDisable = CTRLBreak

    Preloader.Initialise Width:=132, _
                         Height:=132, _
                         WindowCornerRoundness:=6, _
                         ScreenPosition:=ApplicationCenter, _
                         Window:=FramedWindow, _
                         WindowMessage:=""

    Preloader.Transition FadeinDuration:=1400, _
                         DisplayDuration:=2000, _
                         FadeoutDuration:=1400, _
                         Colour:=rgbNightRiderGrey, _
                         Lines:=9, _
                         LineLength:=24, _
                         LineWidth:=4, _
                         LineColour:=rgbWhite, _
                         InnerCircleRadius:=14, _
                         CornerRoundness:=1, _
                         Speed:=1#, _
                         Trail:=60

(5) Using the PascalCasing naming convention for naming Subroutine & Function Names
I like to use the PascalCasing convention for Subroutine, Function Names and Class Property Types. The PascalCasing convention capitalises the first character of each word (including acronyms over two letters in length). Here is an example:
' call the Subroutine
Call CreatePromoFile

' the Subroutine
Public Sub CreatePromoFile()

' Class Property
Public Property Get ItemName() As String: ItemName = pItem: End Property
 





Using Comments

[Back to VBA Best Practices]


(1) Code Module Description & Attribution Comments
I like to add a Description Comment to the top of any inserted Code Modules even if it is a brief one. I then add in my Code Attribution notice and a little about the Project. It is entirely up to you how you format, wrap or indent Comments - if you want to see my latest styles which may change from time to time, then purchase some of my Software and you will see my techniques for dealing with comments:

'
' Description:     Juiicy is a stylish Menu and Workbook Theme for Excel using my modified version
'                   		of the Rollover technique by Jordan Goldmeier.  Juiicy has an expandable,
'                   		moveable and configurable Menu system featuring Menu Items that exhibit a
'                   		Rollover as the Mouse is moved across them. When they are in a Rollover state,
'                   		they become Hyperlinks for jumping to other locations or for using in VBA to run
'                   		Code. As the Mouse is moved away the Menu Items all lose focus and the Menu is
'                   		reset. Juiicy also has a stylish Workbook Theme with an about Worksheet and a
'                   		removeable Typography Worksheet
'

'
' VBA Code Attribution to software creator must remain in situ:
' © Copyright/Author:
' Mark Kubiszyn 2012-2017. All Rights Reserved
' Website/Follow:
' http://www.kubiszyn.co.uk/
' https://www.facebook.com/Kubiszyn.co.uk/
'

'
' VBA Object Name:  mdlJuiicyMenu
' Filename:         		 Juiicy.xlsm
' Date:             		    September 2013
' Version:          	 	  1.0
'   

(2) Subroutine & Function Comments
For Subroutines and Functions I now adopt a strict protocol. My line is exactly 160 characters wide and I add the Name of the Subroutine or Function along with a description of what is does like this:
' /////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
' ## ScreenWidth, width of the screen, in pixels
' /////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
Private Function ScreenWidth() As Long
    On Error Resume Next
    ScreenWidth = GetSystemMetrics(SM_CXSCREEN)
End Function
Please note: if these are Subroutines or Functions that other developers will be maintaining I would add 'Developer:', 'Date:' and 'Changelog:' to the comment.

(3) Variable Declaration Comments
For variable declaration comments whether at the top of the Code Module or within the Subroutine or Function I add a standard comment indented with the Code wrapped at 108 Columns like this:
' defines a double line formatting variable
Public Const vbDoubleLine = vbNewLine & vbNewLine
 

(4) Inline Code Comments
I add inline comments indented with the Code wrapped at 160 characters. To make the comment stand out I may also add a ' // ' between the comment indicator and the actual comment itself like this:
' // update the Column & Row Defined Names
Range("juiicy_column_x").Value2 = myCol
Range("juiicy_row_y").Value2 = myRow
 





Additional Code Module Formatting

[Back to VBA Best Practices]


(1) Spacing between Subroutines & Functions
I like to space things out to maintain some kind of order. I do this by spacing the very first Subroutine in a Code Module by a single line. I then space every consecutive Subroutine or Function either 2 or 4 lines apart. This gives a very nice uniformity to the Code Module. I also adopt this technique inside of any Subroutine or Function following variable declarations. Here is an example of what I mean:

' force explicit variable declaration
Option Explicit

' defines a double line formatting variable
Public Const vbDoubleLine = vbNewLine & vbNewLine

'==========================================================================================================
' ## juiicy_rollover UDF
'       the Juiicy Rollover Menu Item Handler highlights a Menu Item upon Selection by the Mouse.  you can
'       also set a Menu Item by passing a Column & Row via VBA
'==========================================================================================================
Public Function juiicy_rollover(myColumn As Integer, myRow As Integer)

    ' // update the Column & Row Defined Names
    Range("juiicy_column_x").Value2 = myColumn
    Range("juiicy_row_y").Value2 = myRow

    ' // update the replica grid Defined Name as a whole array of zeros
    '    with a single 1 (the AND grid will have already calculated)
    Range("juiicy_rollover_replica_grid").Value2 = _
    Range("juiicy_rollover_grid").Value2

End Function




'==========================================================================================================
' ## juiicy_clear UDF
'       the Juiicy Clear Menu Item Handler clears all of the Menu Items.  you can also call this function
'       via VBA
'==========================================================================================================
Public Function juiicy_clear()

    ' // update the Column & Row Defined Names to 0
    Range("juiicy_column_x").Value2 = 0
    Range("juiicy_row_y").Value2 = 0

    ' // update the replica grid Defined Name as a whole array of zeros
    '    with a single 1 (the AND grid will have already calculated)
    Range("juiicy_rollover_replica_grid").Value2 = _
    Range("juiicy_rollover_grid").Value2

End Function

(2) Segregating Subroutines & Functions
In Code Modules I will always add Subroutines first and then Functions last. Where I add bespoke Subroutines and Functions into Worksheet Code Modules I will always segregate them from any Event Handlers.

(3) Single Line Subroutine, Function or Class Properties
If I have a Class Property (Let is how you set the value of a property of a class, Get is how you pass the value back), Subroutines or Functions that are short in that they return a simple calculated result or return the result of another API, I may add them as single lines like this:
' ## Screen System Metrics 
Private Function ScreenWidth() As Long: ScreenWidth = GetSystemMetrics(SM_CXSCREEN): End Function
Private Function ScreenHeight() As Long: ScreenHeight = GetSystemMetrics(SM_CYSCREEN): End Function

' ## Class Item Properties
Public Property Get ItemName() As String: ItemName = pItem: End Property
Public Property Let ItemName(myValue As String): pItem = myValue: End Property
 
(4) Leaving the Visual Basic Editor Setup to Open on a Specific Code Module
I think it is really nice when you have finished Coding to leave the Visual Basic Editor in a specific Code Module so that when a user presses CTRL+F11 the Visual Basic Editor will open in that Code Module.

To set this up (and it does not require any VBA) go into the VBA Editor any way you like. Make sure you are in a Code Module and at the top right of the Active Window click Restore Window once to restore all the Code Modules and UserForm Modules to their normal Window States. Now Minimise (or Close) any Windows that you don't want leaving the one that you want the VBA Editor to open by default. Now Maximise that Window and click Save in the VBA Editor. Close the VBA Editor. Close Excel. Reopen Excel and Press ALT+F11 to see your handywork. NB: sometimes when using UserForms the UserForm will still be given focus. To fix this go back into the VBA Editor and Minimize the Userform Window. Then click on the little Close Cross to actually close the Window leaving just the Code Module that you want to set focus on. Follow the same process, Save. Close VBA Editor. Close Excel. Reopen and check by pressing ALT+F11. You should Now definitely land in the correct Code Module. This also has the added advantage of collapsing the other Modules in the Navigation Tree (the Project Explorer on the LHS). You can read my article on this topic here

(5) Indenting Code Modules
I love indenting Code - it adds a professional look and feel to the Code. I use "Smart Indenter v3.5" by Office Automation Ltd. You can download it from the following link: Office Automation Ltd.

Smart Indenter is an Excel AddIn. You simply run the Indenter for Subroutines, Code Modules or the entire VBA Project. Here is an image of Smart Indenter at work on my Purge() Subroutine:

Smart Indenter





Using Defined Names

[Back to VBA Best Practices]


(1) Using Defined Names to refer to Cells & Ranges
I like to refer to Cells and Ranges using Defined Names. If a user changes a Worksheet Name or moves a Cell or expands the exact Defined Name Range then Excel will adjust the Defined Name reference accordingly. You can use Formula in a Defined Name including INDEX and OFFSET. Another best practice is to prefix your Defined Name with an identifier Tag that explicitly ties it into the current Workbook. For example, imagine that you had two Workbooks both with a Defined Name of 'date'. You would not want Excel to be presented with two different sets of 'date' Defined Names when copying one of these Worksheets into the same Workbook as the other one. Excel would ask you if you wanted to adopt the Defined Name that it has found, but this is a little confusing. In Juiicy I use Defined Names in the VBA Code to refer to the Juiicy Menu Item Column and Row like this:

    ' // update the Column & Row Defined Names
    Range("juiicy_column_x").Value2 = myColumn
    Range("juiicy_row_y").Value2 = myRow
 
You can list out all of the Defined Names in a Workbook using the following Code - just change the Worksheet Name:
    ' list Defined Names
    Worksheets("Sheet1").Range("A1").ListNames
 
It is best practice to explicitly refer to Defined Names that belong to other Worksheets when in a Worksheet Code Module otherwise Excel will throw an error (there is a good workaround for unique Defined Names - see below). It is even better practice to refer to the current Workbook too. So if writing Code for an Event handler where you know the Defined Name is in that Worksheet then simply use:
    Range("identifier_tag_defined_name")
 
Whereas writing Code that uses a Defined Name in Sheet2 in an Event Handler within Sheet1 Code Module use one of these methods:
    ThisWorkbook.Sheets("Sheet2").Range("identifier_tag_defined_name")

    ThisWorkbook.Sheets(2).Range("identifier_tag_defined_name")

    Sheet2.Range("identifier_tag_defined_name")

 
This also gives you the freedom to expose and run Public Subroutines from another Worksheet within the same Workbook or even from another Workbook in the same instance of Excel (prior to Excel Version 15, Office 2013).

(2) Using Defined Names anywhere in VBA
I have found that a good way of using Defined Names is by making them unique and then wrapping them in brackets like this:
MsgBox "Sheet1 Defined Name Value: " & [workbook_defined_name1]
 
This way you can use them from a Worksheet Code Module or the ThisWorkbook Code Module or any inserted Code Module. You can also just refer directly to the Defined Name to return its value. I have now adopted this method whenever writing Code that uses Defined Names. Here is an Example Workbook Using Defined Names.xlsm that demonstrates this technique





Using Other Peoples Code

[Back to VBA Best Practices]


I like to acknowledge other Authors if I have used any of their Code. A good way of doing this is to leave an acknowledgement next to the Subroutine or Function. I will also leave the Authors Naming Conventions in situ





Worksheet Protection & Securing Data Integrity

[Back to VBA Best Practices]


Worksheet and Workbook protection have their place but I prefer to protect the data integrity of Cells whenever I write a Tool or a Report that may be 'broken' accidentally by an end user. This even includes preventing double-click to go into Cell edit mode in some instances. To do this I use a combination method of Event Handlers and Data Validation. This combination method will prevent users from:

(1) Removing Cell contents entirely i.e. leaving a blank value
(2) Changing a Cell content i.e. changing a value to a value that may be out of bounds or invalid
(3) Deleting Columns or Rows that contain Cells / data that are required by the Tool, Report or VBA Code
(4) Inserting Columns or Rows that move Cells / data
(5) Double-clicking to enter Cell edit mode (users can still use the Formula bar & F2 - it just tidy's up the end users experience when clicking links)

You can read how I do this here by searching for Protecting Cell Data Integrity or you can download a Workbook demonstrating the techniques: Protecting Cell Data Integrity.xlsm





Hiding Subroutines in Code Modules & the Macro Dialog

[Back to VBA Best Practices]


You can hide Code in Code Modules (including hiding the Subroutine in the Macro Dialog) by adding this statement to the top of the Code Module - it prevents a modules contents from being referenced outside its project

' prevents a modules contents from being referenced outside its project
Option Private Module
 
You can also Hide a Subroutine from displaying in the Macro Dialog by passing a dummy Variable and using the 'Void' keyword like this:
Sub Example()
 Call GenerateCVCS("foobar")
End Sub

Public Sub GenerateCVCS(void)
'
End Sub
 





Install & Uninstall AddIns

[Back to Search]

If the AddIn is in a Zip Archive, right-click on the .ZIP Archive and select Extract All. The .XLA or .XLAM AddIn File should be placed in your default Excel 2010/2013 AddIn Folder as Excel always opens this by default, but you can extract or copy the files anywhere on your PC (after which you can browse to locate it as you perform the install). The Library Folder or one of its Subfolders is in the Microsoft Office/Office Folder. Alternatively you can use the Documents and Settings/Application Data/Microsoft/AddIns Folder. If working on a Network, AddIns should be stored in a central location so that other users can access / add them. If in doubt Click AddIns in Excel and then Click Browse to ascertain where Excel is storing its AddIns

Tip: If receiving the AddIn in an Email (you can also do this from a File location if you wish) you can right-click to Copy the AddIn to the Clipboard. When you bring up the Explorer Window Browsing to locate an AddIn in the process below you can Paste it directly into the default AddIns Folder

Pre-requisites
i) You need to enable macros in Excel 2010/2013
ii) You need to allow access to the VBA Project Object Model, the 'VBOM'
iii) Option, you can enable the Developer Tab on the Ribbon for Macro Security & AddIn management

To Install an AddIn for Excel 2010/2013
Click the Developer Tab and press 'Add-Ins' or select the File Tab on the Ribbon and then Options. Press the AddIns Button. Click Go on Manage Excel AddIns. Click Browse and locate the AddIn (if you did right-click Copy from an Email or File location you can right-click Paste the AddIn into the location that the Windows Explorer brings up when you click Browse). Make sure the AddIn is Selected and then Click OK. The AddIn Name will now appear in the AddIns list with a check next to it and a description in the box below. Click OK. An Excel AddIn Installer Message may also be shown - if so, click OK. The AddIn is now installed





Enable Macros & Trust access for the VBA Project Object Model (VBOM) in Excel 2010/2013

[Back to Search]

1. Open Excel 2010/2013 and on File menu, click Options
2. In Options, from left sidebar, click Trust Center and from the main window on the right hand side, click Trust Center Settings
3. This will open the Trust Center dialog. From the left sidebar select macro Settings and select any option other than the top one
4. Under Developer Macro Settings make sure that the Trust Access to the VBA project object model is checked
5. Turn on the Developer Tab by selecting the File Tab (top left) and then selecting Options. Click on Customize Ribbon (left side) and under Main Tabs click the Developer checkbox





Open the VBA Editor in a Code Module of your Choice

[Back to Search]

It is annoying when you have 3 or 4 Code Modules and you add a UserForm or a Class Module. The VBA Editor then defaults to the UserForm Code or the Class Module Code every time you subsequently open the VBA Editor by pressing ALT+F11 placing you in a place that you don't really want to be! This is particularly frustrating when you want to distribute your code & place the user at a certain point when they first open up the VBA Editor i.e. Copyright or specific instructions that need to be read or followed when using any distributed code

To fix this (and it does not require any VBA) go into the VBA Editor any way you like. Make sure you are in a Code Module and at the top right of the Active Window click Restore Window once to restore all the Code Modules & UserForm Modules to their normal Window States

Now Minimize or Close any Windows that you don't want, leaving just the one that you want the VBA Editor to use as the default. OK, now Maximize that Window and click Save in the VBA Editor. Close the VBA Editor. Close Excel. Reopen Excel & Press ALT+F11 to see your handywork. NB: sometimes when using UserForms the UserForm will still be given focus. To fix this go back into the VBA Editor and Minimize the Userform Window. Then click on the little Close Cross to actually close the Window leaving just the Code Module that you want to set focus on. Follow the same process, Save. Close VBA Editor. Close Excel. Reopen and check by pressing ALT+F11. You should Now definitely land in the correct Code Module. This also has the added advantage of collapsing the other Modules in the Navigation Tree (the Project Explorer on the LHS)





Excel VBA Application Stops Spontaneously With Message Code Execution Has Been Halted

[Back to Search]

If your Code keeps breaking with the message "Code execution has been halted" totally at random when nothing appears to be wrong then press the "Debug" button in the popup, then press twice [Ctrl+Break] and after this the Code will continue without stops

Source: stackoverflow - Excel VBA App stops spontaneously with message "Code execution has been halted"





Excel Opens Two Files When Opening A Workbook

[Back to Search]

Solution 1 (no apparent reason):
I searched everywhere and tried everything to try to fix this issue. Whenever I open Excel 2013 it opens 2 Files at once. I found the solution to my issue was with the Explorer Window.

How to fix Excel 2013 opening 2 Files when opening from Explorer:
Toggle the Preview Pane on in the Explorer Window and try re-opening. Toggle it back off and then re-open. Hopefully Excel will only open one File. Please note: this may not work if you cannot preview Excel Files

Solution 2 (when using VBA to create New Excel instance or Workbooks):
Another reason that Excel may open a previous File is that you created a New Application instance using VBA and the instance is still resident in memory but the Workbook is not. Ensure that when closing a Workbook that you also use the '.Quit' Method() of the Object to close the actual Excel instance and you should not get this problem





Hiding Public Subroutines From The Macro Dialog Or ALT And F8 Keypress

[Back to Search]

Q. How do I hide a Public Subroutine in VBA?
A. There are a couple of methods
1. Use the keyword "void" in the Subroutine and then pass any term as a Parameter i.e. "foobar" like this:

' // this Subroutine will be hidden
Public Sub TrialDialogDemo(void)
 TrialDialog.Tag = "(ANNOYING NAG MESSAGE):"
 TrialDialog.Show
End Sub

' // call the hidden Subroutine
Sub CallTrialDialog()
 TrialDialogDemo ("foobar")
End Sub
 
2. At the very top of the Code Module where you add the Subroutines add the Option declaration to hide Public Subs like this:
Option Explicit
Option Private Module
 




How To Cancel Or Reset Text To Column Settings

[Back to Search]

When you have applied the Excel Text to Columns Function, when Pasting new data, Excel will attempt to use the same settings again to split new data. To change or Cancel this setting, just go back through the text to columns wizard and uncheck the setting that you checked previously, like space or comma. You will need to select a Column again with some data to iterate through the Wizard