Generate the coding for worksheets

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 '
    ''''' ' 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.


    Section contents

    About Wim

    Wim Gielis is a Business Intelligence consultant and Excel expert

    Other links