Activating the right sheet

Changing the active sheet given its name is one of the basic tasks when writing business macros and can be very simple with this VBA Bits library function:

SetActiveSheet (sheetname) eg: SetActiveSheet ("Sheet2")

The function is quite simple:

If the sheet exists, it will make it the active sheet (the one selected in Excel and visible to the user). If not, nothing happens, and no error is generated - it just does nothing, retaining whichever sheet is already active on the Excel file.

How does it knows if the given Sheetname exists? It uses the DoesSheetExists(“sheetname”) VBA Bits function detailed on an other post:

Testing if a sheet exists on the Excel file, given its name

You can copy the code here:

Sub SetActiveSheet(sSheetName As String)
' Activates the desired sheet by name if the sheet exists
Dim bOut As Boolean                                                     ' Declare the variables

    bOut = False                                                        ' Default - Assume Failure
    If DoesSheetExists(sSheetName) Then Worksheets(sSheetName).Activate ' Activate sheet with given name if it exists

End Sub

Testing if a sheet exists on the Excel file, given its name

DoesSheetExists (“sheetname”) eg: DoesSheetExists ("Sheet2")

Checking if a given sheet exists having its name is simple - iterate all existing sheets and compare each one’s name with the given one. If a match is found, the function returns a TRUE value to its called. If not, FALSE is returned and no sheet with the given name was found.

The sSheetName variable is a function parameter, supplied between () after the function name. Is is a string (text) and can either be a literal text ("Sheet1") or a variable from existing code - examples of this will be found on the next VBA Bits post, which uses this function.

The UPPER() function forces text to be uppercase, assuring the comparison is not case dependent, which is how Excel treats Worksheet names.

You can copy the code here:

Function DoesSheetExists(sSheetName As String) As Boolean
' Checks if a Sheet name exists - Returns TRUE or FALSE
Dim bOut As String, WSN As Worksheet                    ' Declare the variables

    bOut = False                                        ' Default - Assume Failure
    For Each WSN In Worksheets                          ' Loop all worksheets in current file
        If UCase(sSheetName) = UCase(WSN.Name) Then     ' Compare each sheet name with the supplied one
            bOut = True                                 ' Name Found
            Exit For                                    ' Exit loop - already found the sheet name
        End If
    Next                                                ' Loop until all sheets checked

DoesSheetExists = bOut                                  ' Return result to caller

End Function

The VBA Bits – getting rid of “plumbing”

Copying web development ideas.

In the past, web developers used to have a very rough deal - they had to spend most of the time coding tedious tasks like locating the page element to change or just plain working around browser differences. Then, one day, libraries like JQuery arrived taking care of all this “plumbing” and allowing them to just write what they needed to do:

(from http://purencool.com/javascript-and-jquery-what-is-the-difference )

This approach was so successful than nowadays there are thousands of libraries to write web & mobile applications – some generic, some specialized like charting libraries. But the lesson remains: “Code your needs, not plumbing”

This blog attempts to replicate this success with the VBA Bits. Small prices of independent VBA functions that can be copied to any macro to simplify specific tasks. Just copy them to a code module on your own macro and use them to simplify tasks.