https://www.stl-training.co.uk/hints-tips-vba-21.html Software application hints and tips from our Microsoft Qualified trainers en Tue, 21 Apr 2026 05:44:40 GMT Tue, 21 Apr 2026 05:44:40 GMT https://www.stl-training.co.uk/rss-feeds.php When needing to default a String variable back to it's default of "" use [b]vbNullString[/b] as in the following example: Sub ClearText() Dim strEmpName As String strEmpName = "John Smith" MsgBox strEmpName strEmpName = vbNullString MsgBox strEmpName End Sub https://www.stl-training.co.uk/hints-tips-vba-21.html#tip-480 Fri, 08 Dec 2006 00:00:00 GMT https://www.stl-training.co.uk/hints-tips-vba-21.html#tip-480 Running VBA code may cause the screen to flicker as the monitor is the slowest part of the program and cannot keep up with the very fast changes taking place. To switch off the screen until the program is run enter the following code line: [b]Application.ScreenUpdating = False[/b] The screen comes on automatically on completion of the program. https://www.stl-training.co.uk/hints-tips-vba-21.html#tip-476 Fri, 01 Dec 2006 00:00:00 GMT https://www.stl-training.co.uk/hints-tips-vba-21.html#tip-476 If you ever get stuck in an infinite code loop when programing in VBA use [b]CTRL+BREAK[/b] to exit the procedure https://www.stl-training.co.uk/hints-tips-vba-21.html#tip-395 Fri, 24 Nov 2006 00:00:00 GMT https://www.stl-training.co.uk/hints-tips-vba-21.html#tip-395 When displaying text in a [b]MsgBox[/b] you may need to show it over multiple lines. To do this use: [b]vbCrLf[/b] As in: MsgBox "The System has detected an error." & [b]vbCrLf[/b] & "Contact your System Administrator" The first sentence in quotes will appear above the second in the MsgBox. https://www.stl-training.co.uk/hints-tips-vba-21.html#tip-405 Fri, 24 Nov 2006 00:00:00 GMT https://www.stl-training.co.uk/hints-tips-vba-21.html#tip-405 After each Copy/Paste operation in VBA, you should use the following line of code to empty the clipboard. This ensures that the computer memory doesn't overload: ActiveSheet.Paste [b]Application.CutCopyMode = False [/b] https://www.stl-training.co.uk/hints-tips-vba-21.html#tip-416 Fri, 24 Nov 2006 00:00:00 GMT https://www.stl-training.co.uk/hints-tips-vba-21.html#tip-416 To stop Excel asking you things like "Do you want to delete this file...", use the following line of code at the beginning of the relevant VBA procedure: [b]Application.DisplayAlerts = False[/b] At the end of the procedure make sure you use the following code to reactivate Display Alerts: [b]Application.DisplayAlerts = True [/b] https://www.stl-training.co.uk/hints-tips-vba-21.html#tip-417 Fri, 24 Nov 2006 00:00:00 GMT https://www.stl-training.co.uk/hints-tips-vba-21.html#tip-417 To select a certain cell on a Worksheet you can use: [b]Application.Goto Reference:=Range("V300")[/b] or more simply [b]Range("V300").Select[/b] If, on the other hand, you want the selected cell to be the top/left cell on the screen you use: [b]Application.Goto Reference:=Range("V300"), Scroll=True [/b] https://www.stl-training.co.uk/hints-tips-vba-21.html#tip-418 Fri, 24 Nov 2006 00:00:00 GMT https://www.stl-training.co.uk/hints-tips-vba-21.html#tip-418 To add a new sheet at the end of the workbook you need to count the sheets in the workbook using: [b]Sheets.Count [/b] Then use this value as the rank of the sheet after which you want to add the new sheet: [b]Sheets.Add After:=Sheets(Sheets.Count)[/b] https://www.stl-training.co.uk/hints-tips-vba-21.html#tip-419 Fri, 24 Nov 2006 00:00:00 GMT https://www.stl-training.co.uk/hints-tips-vba-21.html#tip-419 If you need to count the number of rows or columns in a worksheet use the following code: [b]Selection.Rows.Count[/b] - Returns the number of rows in the selection [b]Selection.Columns.Count[/b] - Returns the number of columns in the selection [b]Selection.CurrentRegion.Rows.Count[/b] - Returns the number of rows in the current region of the selection https://www.stl-training.co.uk/hints-tips-vba-21.html#tip-421 Fri, 24 Nov 2006 00:00:00 GMT https://www.stl-training.co.uk/hints-tips-vba-21.html#tip-421 It is important that you always comment the VBA code that you write. By doing this it will make the code much easier to read, as both yourself and other people will be able to see what the code is doing or meant to be doing. This is very important if there is more than one person who writes code in the company as you may have different styles of coding and may not really understand what the other person is trying to do. Also if you write some code and then do not touch it for a year or so you may actually forget what it is meant to be doing. so it is very important to include comments throughout your code and try and make sure that they are as useful as possible https://www.stl-training.co.uk/hints-tips-vba-21.html#tip-388 Thu, 23 Nov 2006 00:00:00 GMT https://www.stl-training.co.uk/hints-tips-vba-21.html#tip-388 When creating or deleting a Worksheet in Excel you don't want Excel to inform you "A file already exists....." Or "Do you want to save this file...". To stop these alerts happening use the following line of code: [b]Application.DisplayAlerts = False[/b] After the Create or Delete routine use: [b]Application.DisplayAlerts = True[/b] https://www.stl-training.co.uk/hints-tips-vba-21.html#tip-335 Tue, 07 Nov 2006 00:00:00 GMT https://www.stl-training.co.uk/hints-tips-vba-21.html#tip-335