Excel VBA Macro Tutorial and Examples

Find the Last Day of any Month

Posted on June 9, 2009 by Admin

Simple Microsoft Excel formula to find the last day of any given month.

'
=DATE(YEAR(A1),MONTH(A1)+1,0)
'

last day of any month

Schedules to Refresh Data Automatically using VBA Macro

Posted on June 9, 2009 by Admin

I once asked by Joshuacht about how to make a macro running every 15 minutes. Let say because every 15 minutes we have to refresh or reload the data in the Workbook.

Below is a sample of how we can do this in Microsoft Excel *just an outline*.

The logic is simple, when we open the Workbook, we call for the first time the procedure needed to refresh the data. In that procedure, we also add a code to call back the procedure itself 15 minutes later. So while the Workbook is still open, the procedure will continue to call itself every 15 minutes.

' Put this procedure in ThisWorkbook module
Private Sub Workbook_Open()

    ' Run the procedure to refresh data when the workbook opened
    Call RefreshData

End Sub

' Add new module and put this procedure in it
Public Sub RefreshData()

    Debug.Print "Data refreshed at " & Now
    '
    ' This is where we put everything needed to refresh the data
    '

    ' Schedules this procedure to be run 15 minutes from now
    Application.OnTime Now + TimeValue("00:15:00"), "RefreshData"

End Sub

Even looks like an infinite loop, this is not a problem for Excel when we use Application.OnTime function. The scheduling will stop automatically when the Workbook is close.

Posted in Workbook and Worksheet | Tagged application.ontime, data refresh, excel macro refresh data, excel vba refresh external data, function refresh, run macro automatically, vba refresh data | 27 Comments

Excel VBA Add Set of Worksheets Automatically

Posted on June 8, 2009 by Admin

There is a time when we must create a set of Excel Worksheets templates on a regular basis in our work. For example, a set of Excel Worksheet for each month of the year or may be based on the type of work we should done in certain time base.

Here a simple example of how to create a new set 12 Worksheets for each month of the year.

Sub CreateMonthlyWorksheetTemplate()
    Dim ix As Integer, oSheet As Worksheet, sMonth As String

    For ix = 1 To 12

        'add new worksheet
        Set oSheet = Worksheets.Add

        'get month name from index
        sMonth = Format("1/" & CStr(ix), "mmm")

        'rename the new worksheet
        oSheet.Name = sMonth

        Set oSheet = Nothing
    Next ix

End Sub

Read also a previous post about how vba add worksheet complete with error handler when we counter duplicate Worksheet.

Posted in Workbook and Worksheet | Tagged excel vba add worksheet, excel vba worksheet, excel worksheet | 33 Comments

Auto Format Excel Cells with Error Value

Posted on June 3, 2009 by Admin

With the simple Excel macro below, we can make all cells in active Worksheet that contains Error value in it, like #NULL, #Div/0!, #VALUE!, #Ref, #NAME?, #NUM!, And #N/A, will automatically having cell format that different/stand out among all other cells.

In this example, background color of the cell that contains the error value will automatically change color to red, each time the Excel Worksheet activated.

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Dim errCells As Range

Set errCells = Cells.SpecialCells(xlCellTypeFormulas, xlErrors)
errCells.Interior.Color = 255

Set errCells = Nothing
End Sub

Note that using the Cells.SpeciallCells function, we do not need to perform any looping to get Cells which contains the Error value.

This is just like automating Excel cell conditional formating process. Instead of selecting manually all the cells contains Error value or select all cells and then performing cell conditional format, with above code, we do it automatically using simple Excel VBA macro.

Posted in Cells and Range | Tagged excel auto format cells, excel cell colorindex, excel cell conditional formating, excel cell format vba, excel error value, excel special cells | 29 Comments

Auto Expanding VLOOKUP Table Array

Posted on April 2, 2009 by Admin

Ok, this time I want to talk about how to create a vlookup table array that automatically expanding in accordance with the numbers of available data.

As we know, the basic form of Excel VLOOKUP to look for A1 value in range B1:C15 column no 2 is like this:

=VLOOKUP(A1,B1:C15,2)

With parameters such as the value we looking for, range of table array where we would like to find the value that we want, and how far the column of values that we want from the left most column of the table array range.

Unfortunately, this form is rigid, the table array range is fixed to row 1 until row number 15. So this form is not suitable for doing VLOOKUP on the database which always dynamic, increase or decrease from time to time.

Using the above VLOOKUP form, every time there is an addition of new data, we need to change the VLOOKUP formula to match the numbers of expanding data.

We need to change the format of the VLOOKUP formula to be more flexible and save the time we need to manually adjust the formula by hand.

The following changes is the form that we need:

=VLOOKUP($A1,OFFSET($B$1,0,0,COUNTA($B:$B),3),2)

Here we got help from OFFSET Excel function to return the reference of VLOOKUP table array range that we need plus the COUNTA function to calculate the maximum number of rows on the database.

This way, every time there are any reduction or addition of new data on the VLOOKUP table array, COUNTA will return the number of rows needed, and OFFSET will provide the referense of the new table array, so we do not need to make changes the formula manually

Formula at A2 returning a wrong vlookup value when we use the first vlookup =VLOOKUP(A1,B1:C15,2) formula. This is because the value that we are looking for already outside vlookup table array. Formula at A3 returning the correct value when we use the auto expanding version of the lookup.

Read this great article of Dynamic Ranges for more information about dynamic ranges in Excel.

I am confess, I can not write well, let alone give an easy to read and easy to understand explaination

Posted in Excel Formula | Tagged excel 2003 vlookup, excel formulas vlookup, excel vba vlookup, excel vlookup table array, lookup table, microsoft excel vlookup, vlookup array formula, vlookup excel vba, VLOOKUP macro, vlookup pivot tables, vlookup VBA macro | 43 Comments

VBA Delete Excel Rows Based on Certain Date

Posted on January 31, 2009 by Admin

Let say I have a bunch of formatted data in my Excel sheet, and in Column A I have a dates. Then I wanna filter all the data base on certain date, for example 1 Jan 2009, and delete all others data before that date.

The algorithm is like this: I’ll create a loop from the first row until the last row with data and check if the date is before Jan 1st, 2009 or not, if it is, simply delete that row.

Sub DeleteFilteredRows()
    Dim i As Long

    For i = 1 To Cells.SpecialCells(xlCellTypeLastCell).Row
        Debug.Print Cells(i, "A").Value
        If CDate(Cells(i, "A")) < CDate("1/jan/2009") Then
            Cells(i, "A").EntireRow.Delete
        End If
    Next i

End Sub

Of course I’ll make an adjustment to the code above to meet my current condition.

FIN.

Posted in Cells and Range, Workbook and Worksheet | Tagged columns, delete row, excel macro, excel macro delete row, excel vba delete row, filtered date, function cells, vba delete excel row | 85 Comments

Number of Cells/Rows/Columns With Formula

Posted on January 29, 2009 by Admin

A few weeks ago I received this question from someone called LS:

Hi
I want to write a macro that counts the following
- Number of cells with formula
- Number of Rows with formula
- Number of Columns with formula
Please let me know if there is a way of doing it in Excel VBA

To find out the number of cells, rows, or columns with formula in active Excel worksheet is quite simple.

In my previous post, we already have the Excel VBA code on how to select all cells with formula, so we only need to tweak this macro a little bit and get the results that we looking for.

Sub NumberOfCellsWFormula()

    'select all cells with formula
    Cells.SpecialCells(xlCellTypeFormulas).Select

    'number of cells with formula
    Debug.Print Selection.Count

    'number of rows with formula
    Debug.Print Selection.Rows.Count

    'number of columns with formula
    Debug.Print Selection.Columns.Count

End Sub

The Excel VBA procedure above will simply print the number of cells, rows, and columns of current active Excel Worksheet that containing any formula in it. Please be sure to run it from Excel VBA windows with Immediate Window turn on (View » Immediate Window or CTRL+G).

In short, we only need to use Range properties Count, Rows.Count, and Columns.Count to get the number of selected cells, rows or columns.

FIN.

Posted in Cells and Range, Workbook and Worksheet | Tagged cells, columns, excel cells, excel formula, excel macro count rows, excel macro count the no of rows with text, excel vba count rows, rows, vba count rows, vba macro, worksheet cells | 64 Comments

Excel VBA Macro