Excel, in the right hands, can be a very powerful tool for efficiency in the workplace. A well designed spreadsheet can save hours every day, while badly designed spreadsheets can be time-consuming to work with and errors in them can actually cause financial losses.





VBA macros are an important part of designing high quality spreadsheets which perform the way your end-users want them to, which are intuitive to use and provide accurate data and reporting.





What are VBA macros?





Macros or Visual Basic for Applications (VBA) is a programming language which can be used to write programs to perform tasks automatically in Excel (and also Outlook, Access, Word, and many other programs too numerous to mention).





This means almost any task that can be performed by a human (and many tasks that can't) can be performed by the computer at the click of a button.





Why Should I Use VBA macros?





VBA macros of great use when creating high quality smart spreadsheets because they can:





Perform routine repetitive tasks with extremely quickly and with high reliability

Be customised to adapt to circumstances or changes in data

Perform complex logical steps or apply intricate rules. Enabling complex calculations and data manipulation that is too difficult or impossible to perform using Excel's built in functions

Perform any step or process a human does in Excel, or any of the other Office applications

Combine Applications to perform transformation of business processes, such as importing data from a database, performing calculations and presentation in Excel, exporting to PDF then sending an email with that data attached

Provides an interface your users are already familiar with.





What VBA Macros Should I Use?





Our Excel Consultants regularly use VBA when developing the best Excel spreadsheets and over years of experience have certain tried and true macros they use in almost all their projects. The following is a list of some of the favourites and most useful:









1. Make Excel VBA Run Quickly





The Excel Application has default system settings that are not optimised for the running of VBA. This is not surprising as the vast majority of users will never write a VBA macro. If you are developing in VBA then you need to be aware of the importance of changing these settings so the code will run as quickly as possible.





All our code is initialised with a call to the following startExcel subroutine:





Sub startExcel()





On Error Resume Next

With Application

.ScreenUpdating = False

.DisplayAlerts = False

.EnableEvents = False

.Calculation = xlCalculationManual

.StatusBar = ""

End With

On Error GoTo 0

End Sub





Application.ScreenUpdating - tells the Excel program whether or not the screen should be updated following any interaction with the program. This is slow and resource intensive and can account for a large part of why some macros can run very slowly.





If you are running VBA code then it is unlikely that the screen needs to be refreshed with the changes made until all the code has run. So, set the value to False to stop the screen updating.





Application.DisplayAlerts - tells the Excel program whether or not to present any system generated alerts. These usually get in the way of your code running and require a specific response from your user to ensure the program is routed the correct way and not interrupted.





It is far better to set this value to False and handle any errors internally within your VBA program.





Application.EnableEvents - tells the Excel program whether or not to allow events to continue to be triggered. An example of an event is Worksheet_Change or Wookbook_Open. These events are monitored and can be used trigger a routine once the application senses them.





The risk is that without setting the value to False it is possible to end up in an infinite loop of events triggering events. far safer to set the value to False and switch it back on if you actually need to trigger an event during your code running.





Application.Calculation - tells the Excel program whether formulas should be calculated. In large spreadsheets with many and complex formulas it is far quicker to set the value to xlCalculationManual and then perform an explicit calculation whenever a particular range, sheet, workbook requires it.





Otherwise formulas can refresh many times through the running of code and cause it to run far slower than it should.





2. Return Excel to Default Settings





After using the above code to optimise Excel for the running of VBA code it is necessary to return all the values back to their previous settings to ensure that the Excel Application functions as the end user would expect.





All our code is completed with a call to the following endExcel subroutine:





Sub endExcel()





On Error Resume Next

With Application

.ScreenUpdating = True

.DisplayAlerts = True

.EnableEvents = True

.Calculation = xlCalculationAutomatic

.StatusBar = ""

End With

On Error GoTo 0

End Sub





Failure to change these settings back will result in application behaviour that will likely lead an end-user to believe Excel is now broken.





Application.ScreenUpdating - the screen will not change whatever the user inputs, however, their actions will still take effect.





Application.DisplayAlerts - system warnings will not be displayed





Application.EnableEvents - event monitoring will not take place, so any code linked to an event trigger will no longer function.





Application.Calculation - formulas will not automatically update leading users to believe formulas are broken or that the wrong information is being used





3. Option Explicit





This is a key requirement for helping you write better code and to be able to bug fix more effectively. It will also enable your code to run more quickly as the VBA won't need to spend time guessing what the variable data type is. Your program will also use less memory as you can define the smallest type rather tyhan rely on Excel guessing and using an overly large or broad data type.





At the top of each module write the words:





Option Explicit





This forces the requirement that all variables are Explicitly defined e.g. Dim ws as Worksheet.





This can be set automatically by opening the VBA Project window, selecting Tools, Options





Select Tools then Options to launch the menu





Then ticking Require Variable Declaration on the Editor tab:









Tick Require Variable Declaration to get automatic 'Option Explicit'

Option Explicit will now automatically appear at the top of any module, class, sheet or workbook.





4. Get the Last Used Row





When building smart spreadsheets it is a routine occurrence to need to find the end of your data. This is often a difficult task as data structures can be in consistent, so just because a cell is empty doesn't mean it is the end of the data.





Our solution for this is the following function





Function getLRow(ByVal ws As Worksheet, ByVal lCol As Long, Optional ByVal bFirstEmpty As Boolean) As Long

Dim lRow As Long





lRow = ws.Columns(lCol).Cells.Count

getLRow = ws.Cells(lRow, lCol).End(xlUp).Row

If bFirstEmpty = True Then

getLRow = getLRow + 1

End If

End Function









It is called in the following way:





getLRow (worksheet object to be checked, column number to be checked, True/False - true returns an offset by 1 row from last used cell)





An example of it in use is:





lRow = getLRow( Sheet1,1,False)





This would check column A of Sheet1 and return the row number of the last used cell.





Because this function counts the number of cells in the provided column, it is compatible with .xls, .csv and .xls* type files.









5. Get the Last Used Cell





A slight variation on the above but more useful when you perhaps don't know, or have, a column you can rely on as a consistent source for determining the last used row.





The following function receives a Range object and outputs a single cell range at the bottom right corner of any data. It returns the bottom right hand cell of the received range that contains any data. So if a limited range is passed it it will only check that. If a whole sheet (of cells) is passed in it will check that.





Function findLastCell(ByVal rng As Range) As Range





Dim lCol As Integer

Dim lRow As Long





If WorksheetFunction.CountA(rng) > 0 Then

lRow = rng.Find(What:="*", After:=Cells(rng.Row, rng.Column), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

lCol = rng.Find(What:="*", After:=Cells(rng.Row, rng.Column), SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column

Set FindLastCell = Cells(lRow, lCol)

End If





End Function





It is called in the following way:





findLastCell(range object to be checked)





An example of it in use is:





rngEnd= findLastCell(Sheet1.UsedRange)





This would check all used cells on Sheet1 and return the range of the last cell in that range to contain any data.





6. Align UserForms in the Center of the Screen





When presenting userforms that have been created in VBA, to help them look more professional and to present them to the best affect for users, it is best practice to present them in a consistent location on screen.





The following function consistently displays the userform in the centre of the screen that contains the application window.





Sub userFormCentered(frm As Object)

With frm

.StartUpPosition = 0

.Left = Application.Left + (0.5 * Application.Width) - (0.5 * .Width)

.Top = Application.Top + (0.5 * Application.Height) - (0.5 * .Height)

End With

End Sub





It is called in the following way:





userFormCentered(userform object to be displayed)





An example of it in use is:





userFormCentered Me





Use the above line in your UserForm_Initialise event to centre the form on load and make use of the Me keyword.





7. Remove Illegal Characters from Strings





This function is of particular use when handling user inputs or building filepaths or file names. Pass a string through the belwo function and it will remove any characters that not allowable for Windows filepaths & names.





It returns a new string stripped of disallowed special characters





Function removeIllegalChars(ByVal strInput As String) As String





Dim charArray As Variant

Dim x As Long

charArray = Array("<", ">", "|", "/", "*", "\", "?", """", "(", ")", ":")

For x = LBound(charArray) To UBound(charArray)

strInput = Replace(strInput, charArray(x), "", 1)

Next x

strInput = Replace(Replace(strInput, Chr(10), ""), Chr(13), "")





removeIllegalChars = strInput





End Function





It is called in the following way:





removeIllegalCharacters(string to be checked)





An example of it in use is:





tempText = removeIllegalCharacters("//TEST<>TEXT?\\")





The above would return "TESTTEXT" to tempText variable.





8. Does the Workbook Have Any Protection?





When working with workbooks and worksheets it can be useful to know before running a long involved process (e.g. applying roundings to all formulas) if any part of the document is protected and thus might stop a process from running completely.





The following function receives a workbook object and checks the workbook and each worksheet to determine if it is protected. If any aspect is protected then the function returns a True value.





Function isWBProtected(ByVal wb As Workbook) As Boolean

Dim ws As Worksheet





isWBProtected = False

If Not wb Is Nothing Then

If wb.ProtectStructure = True Or wb.ProtectWindows = True Then

isWBProtected = True

GoTo exitCheck

Else

For Each ws In wb.Worksheets

If ws.ProtectContents = True Then

isWBProtected = True

GoTo exitCheck

End If

Next ws

End If

End If





exitCheck:

Set ws = Nothing

Set wb = Nothing





End Function





It is called in the following way:





isWBProtected(workbook object to be checked)





An example of it in use is:





bProtectCheck= isWBProtected(ActiveWorkbook)





The value returned would be True or False depending on results.





9. Set Object = Nothing





The VBA memory management isn't always reliable. To ensure that your program doesn't use too much memory when running, and thus risk running out of memory or crashing, ensure to always set any object variables to Nothing.





Doing this releases the memory that has been set aside. Not doing this can cause the unused and orphaned variables to be stored in memory until the application is closed entirely.





This applies to all Object data types e.g. Workbook, Worksheet, Range, etc





Set variableName = Nothing





Add the above line, one for each object type variable, at the end of your Functions or Subs to close them out and release the memory.





10. Translate Text Using Google Translate





A fun one to show the range of possibilities in Excel. Pass your text string into the function along with a two character string to denote the required output language to receive a translated response from Google Translate,





Function GoogleTranslate(InputText As String, ToLang As String) As String





Dim IE As Object





Set IE = CreateObject("InternetExplorer.application")

IE.Visible = False

IE.navigate "http://translate.google.com/#auto/" & ToLang & "/" & InputText

Do Until IE.ReadyState = 4

DoEvents

Loop

Application.Wait (Now + TimeValue("0:00:5"))

Do Until IE.ReadyState = 4

DoEvents

Loop





GoogleTranslate = IE.Document.getElementsByClassName("result-shield-container tlid-copy-target")(0).innertext





IE.Quit





End Function





It is called in the following way:





GoogleTranslate(string to be translated, two char code for return language)





An example of it in use is:





returnTExt= GoogleTranslate("Hello World", "fr")





The above should return "Bonjour le monde".





What if I need more help?





The Excel experts at Get Help With Excel are on hand to provide customised training covering any topic and suitable for all levels of experience with Excel, from complete novices to advanced VBA developers. Or, if you would prefer, have one of our expert Excel developers build you the perfect spreadsheet, using our VBA expertise.







