wimgielis.com

Generate the coding for worksheets

• Dec. 21, 2013

	Example files with this article:
	Generate worksheet coding

Introduction

Hello to my English speaking friends and readers! This is the first Excel blog article in English. The past months I worked hard to provide useful content on my popular Excel and TM1 website, for the benefit of all. Here is the first such article, hopefully a lot of other articles will follow soon.

In this article I would like to show you some code I use when starting a project or helping someone online. The context is that usually, a number of worksheets in a file need to be used in the VBA-code to be written. For example, there is a sheet containing data, a sheet with a pivot table, a sheet with VLOOKUP functions and so on. In the VBA-coding, most of the sheets need be addressed using Worksheet variables (object variables). The code is always similar, hence why I want to automate this.

End result

Before showing the code, here is an illustration of the end result, based on a file with 5 worksheets:

The worksheet 'Historic data' is hidden in the real workbook, but shown here for the illustration.

You can see that the code is created and output in the VBE Immediate Window. That is good enough for my purposes. From there I can copy/paste the code to a regular code module in the VBE project. The code generates statements that we often use in VBA. I find it easier to delete code lines I do not need in a procedure, rather than adding code statements again (manually).

On top, we see the statements to declare a workbook variable and 5 worksheet variables. The variable names that I use, are derived from the worksheet names in the (active) workbook. Using inputboxes, I complete the names of the worksheets so that I can concatenate 'ws' and my chosen name to complete the variable name. When I leave an inputbox empty, no variable will be created for that worksheet. In general, not all worksheets need to be treated in a VBA procedure. After the code for the worksheets, you will notice a number of whitelines where I tend to enter the custom code.

The complete code listing follows now. It is lengthy but relatively easy to understand and follow. Loops over worksheets are the basis.

Sub CreateWorksheetCoding()
' Wim Gielis
' https://www.wimgielis.com
'''''
' Code to generate VBA-code for worksheets
' 21/12/13
'''''

    Dim ws As Worksheet
    Dim wsName As String
    Dim sName As String
    Dim sNames As String
    Dim blnSheetsWanted As Boolean

    c00 = "Dim ws"
    c01 = " as Worksheet"
    c02 = "Set ws"
    c03 = " = wb.Sheets("""
    c04 = " = Nothing"

    
'get the worksheet name choices
    sNames = "/"

    For i = 1 To ActiveWorkbook.Sheets.Count

        Set ws = ActiveWorkbook.Sheets(i)
        wsName = ws.Name

        ws.Activate

        sName = ""
        Do
            sName = Trim(UCase(Application.InputBox( _
                               IIf(ws.Visible = xlSheetHidden, "HIDDEN - " & vbCr & vbCr, IIf(ws.Visible = xlSheetVeryHidden, "VERY HIDDEN - " & vbCr & vbCr, "")) & _
                               "This sheet: " & wsName, "Worksheet names", UCase(Left(wsName, 3)), , , , , 2)))
        Loop Until sName = "" Or InStr(sNames, "/" & sName & "/") = 0

        sNames = sNames & sName & "/"

    Next

    blnSheetsWanted = (Len(Trim(Replace(sNames, "/", ""))) > 0)

    If blnSheetsWanted Then sNames = Mid(sNames, 2, Len(sNames) - 2)

    sq = Split(sNames, "/")

    
'generate the coding
    Debug.Print "Sub MyProc()"

    
'dim as workbook
    sOutput ""
    sOutput "Dim wb As Workbook"

    
'dim as worksheet
    If blnSheetsWanted Then
        For i = 0 To UBound(sq)
            If Len(sq(i)) Then
                sOutput c00 & sq(i) & c01
            End If
        Next
    End If

    
'set wb
    sOutput ""
    sOutput "Set wb = ActiveWorkbook"

    
'set ws
    If blnSheetsWanted Then
        For i = 0 To UBound(sq)
            If Len(sq(i)) Then
                sOutput c02 & sq(i) & c03 & ActiveWorkbook.Sheets(i + 1).Name & """)"
            End If
        Next
    End If


    
'actual coding
    sOutput ""
    sOutput "Application.ScreenUpdating = False"
    sOutput "Application.Calculation = xlCalculationManual"

    For i = 1 To 10
        sOutput ""
    Next



    
'ending statements

    sOutput ""
    sOutput "Application.ScreenUpdating = True"
    sOutput "Application.Calculation = xlCalculationAutomatic"

    
'set wb to nothing
    sOutput ""
    sOutput "Set wb = Nothing"

    
'set ws to nothing
    If blnSheetsWanted Then
        For i = 0 To UBound(sq)
            If Len(sq(i)) Then
                sOutput c02 & sq(i) & c04
            End If
        Next
    End If

    sOutput ""
    sOutput "MsgBox ""The code has finished."", vbInformation, ""Status"""

    sOutput ""
    Debug.Print "End Sub"

End Sub

Sub sOutput(s As String)
    Debug.Print vbTab & s
End Sub

Closing remarks

The code will jump to the sheet for which it asks the name. When the sheet is hidden (or very hidden) then this will be indicated as such.

Have fun and save time with this code! Stay tuned for next Excel articles on my website.

• « Previous Excel article
• Next Excel article »