• Home
  • Other Stuff
  • Contact
Black Tor

VBA: A Few Tips

Simple VBA

Excel VBA

Below is some simple VBA code which you can use for a variety of purposes. Starting with these simple building blocks it is possible to build both your confidence, knowledge and ability to work with VBA completing complex appearing sub-routines.
Please revisit the site over the next few days and weeks as more content and helpful code is added.

Last Row and/or Column

The below sub-routine will find the last row containing data in column A (1) and the last used column in row 1. The results are then provided back in a message box, however when you use this code it is more likely that you would activate or select the appropriate cell to perform some operation.
Here is the code...

Sub LastRowAndColumn ()
' This provides the last use row and column (number and letter) in row and column 1
Dim NoLastRow As Long
Dim NoLastCol As Long
Dim LtLastCol As Long
' 1 in the below line denotes that we are examining column 1
NoLastRow = Cells(Rows.Count, 1).End(xlUp).Row
' The number 1 in the line below means that we are looking purely at row 1
NoLastCol = Cells(1, Columns.Count).End(xlToLeft).Column
' In the below row we change the number previously provided into the appropriate letter for the column.
LtLastCol = Replace(Cells(1, NoLastCol).Address(0, 0), 1, "")

MsgBox "The last row is " & NoLastRow & ", and the last column is " & _
LtLastCol & "(" & NoLastCol & ")"

End Sub

This code uses Rows.Count and Columns.Count because when changing between earlier versions of Excel you may not have the same number of either. To prevent errors occurring we allow the code to produce this number for itself.

Return to the  TOP Up Arrow

Add A Sheet

Often when working with Excel you will want to add a new sheet to the workbook and make calculation on it. This first step is to add a sheet to a specified location and name the sheet as appropriate.

Sub AddSheet ()
' This adds a sheet after the last sheet currently in the workbook and names it after the date
Sheets.Add after:=Sheets(ActiveWorkbook.Sheets.Count)
On Error GoTo ErrHandler
ActiveSheet.name = Format(Now, "yyyymmdd")

Exit Sub

ErrHandler:
ActiveSheet.name = Format(Now, "yyyymmdd") & "(" & ActiveWorkbook.Sheets.Count & ")"
End Sub

Note that we have used an error handling procedure in this code to account for the sub-routine being run on more than one occasion per day.

Return to the  TOP Up Arrow

Insert a Row and/or Column

Often you will want to add a new row or column to data that is already held in your workbook. Adding a row or column allows you to ensure that all tables retain their integrity and do not merge. Especially useful when multiple tables of data exist within one worksheet.
Here is the code...

Sub InsertRowAndColumn ()
' How to Insert a Row and Column
' Insert a Row in Row 1
Rows(1).Insert
' Insert a Column in Column A
Columns("A").Insert
' If you wish to Delete instead of Insert you can substitute Insert for Delete.

End Sub

You could include code to detect the first empty row (or column) from a fixed point, or locate the header row using VBA.

Return to the  TOP Up Arrow

Format A Range

This sub-routine will format columns 1 to 3 in differing formats

Sub FormatColumns()
' How to Format a column to a specified number type
' Although the dollar sign is used below this is generic for currency and your local settings will decide the symbol used.
Columns(1).EntireColumn.NumberFormat = ("$#,##0.00")
' Dates in single digits will be prefaced with a 0 as we have specified a two digit number.
Columns(2).EntireColumn.NumberFormat = ("dd/mm/yyyy")
' Specifying the date with 3 or more d's changes it from a number to a letter such as ddd=Mon (or Tue, Wed, etc as appropriate).
' Four m digits alters the month from a number to the full months name, use three digits for an abbreviated month.
Columns(3).EntireColumn.NumberFormat = ("dddd d mmmm")

End Sub

The examples above format the full column however you can format specific ranges if you require.

Return to the  TOP Up Arrow

Colour Cells

The code shown immediately below called 'ShowPalette' will change the interior colour of each cell in the range A1 to A56 using the colours currently contained within your Excel palette. The row that each colour resides within shows the index number of the colour.

Sub ShowPalette()
' This will colour cells A1 to A56 with the colours contained in the palette
Dim r As Integer
For r = 1 To 56
Cells(r,1).Interior.ColorIndex = r
Next r
End Sub

Below is an sub-routine that uses three separate methods to change the fill or interior colour of a cell. This only changes a single cell but it is easily possible for you to change a range or only change cells with a specified value.

Sub ColourA1 ()
' First give the cell some text
Cells(1, 1).Value = "Trial Text"
Cells(1, 1).Interior.Color = RGB(255, 0, 0)
' Red
Cells(1, 1).Font.Color = vbWhite
' So that you can see the changes a 2 second wait is placed between each stage
Call WaitFor2Secs
Cells(1, 1).Interior.ColorIndex = 45 ' Orange
Call WaitFor2Secs
Cells(1, 1).Font.Color = vbBlack
Call WaitFor2Secs
Cells(1, 1).Interior.Color = vbGreen
Call WaitFor2Secs
Cells(1, 1).Font.ColorIndex = 2 ' White
End Sub

Sub WaitFor2Secs ()
' This takes the time NOW and adds 2 seconds before it continues
Application.Wait (Now() + TimeValue("00:00:02"))
' You can remove the now section to make the macro work at a particular time of the day, such as 4:55pm...
' Application.Wait (TimeValue("16:55:00"))
End Sub

Return to the  TOP Up Arrow

Updating

Working on it!

Currently this site is under re-construction and this page amongst many is constantly being updated with information. Please revisit the site over the next few days and weeks as we add more content.