Generate the coding for worksheets
- Dec. 21, 2013
|Example files with this article:|
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.
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 ' http://www.wimgielis.comDim 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" 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, "/") Debug.Print "Sub MyProc()" sOutput "" sOutput "Dim wb As Workbook" If blnSheetsWanted Then For i = 0 To UBound(sq) If Len(sq(i)) Then sOutput c00 & sq(i) & c01 End If Next End If sOutput "" sOutput "Set wb = ActiveWorkbook" 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 sOutput "" sOutput "Application.ScreenUpdating = False" sOutput "Application.Calculation = xlCalculationManual" For i = 1 To 10 sOutput "" Next sOutput "" sOutput "Application.ScreenUpdating = True" sOutput "Application.Calculation = xlCalculationAutomatic" sOutput "" sOutput "Set wb = 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 SubSub sOutput(s As String)Debug.Print vbTab & sEnd Sub
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.