Add and return Excel.Worksheet references in VBA
March 3, 2010 • JP • 1 Comment • Rate This Article
The Workbooks.Open Method creates a new workbook, adds it to the Workbooks Collection, and returns a Workbook Object which can be set to an object reference. i.e.
Dim wkbks As Excel.Workbooks
Dim wkbk As Excel.Workbook
Set wkbks = Excel.Workbooks
Set wkbk = wkbks.Add
End Sub
From Excel Help:
Creates a new workbook. The new workbook becomes the active workbook. Returns a Workbook object.
Now you can use wkbk the way you would any Workbook object. But you can't do the same with Worksheet objects. In other words, this shouldn't work:
Dim wkbk As Excel.Workbook
Dim wkshts As Excel.Sheets
Dim newWorksheet As Excel.Worksheet
Set wkbk = ActiveWorkbook
Set wkshts = wkbk.Sheets
Set newWorksheet = wkshts.Add
Set TestWorksheetAdd = newWorksheet
End Function
According to Excel's Help system, Worksheets.Add doesn't return anything. Yet the above procedure does return a new Worksheet Object, and the Object Browser does show that it returns something:
Function Add([Before], [After], [Count], [Type]) As Object
In the off chance it's actually me doing something wrong, I wrote a function to (needlessly) duplicate the Worksheet.Add functionality. But I like it better because you can specify the position of the new worksheet.
As Excel.Worksheet
' add worksheet and return reference to it!
Dim sheetCount As Long
Dim sheetToAdd As Long
Dim wksht As Excel.Worksheet
' check how many sheets are in the workbook
sheetCount = wb.Worksheets.Count
' if position specified is greater than the number of sheets,
' insert the sheet at the end, else add it to wherever it
' was specified, or as the first sheet (default)
sheetToAdd = Application.Min(pos - 1, sheetCount)
sheetToAdd = Application.Max(sheetToAdd, 1)
With wb
.Worksheets.Add After:=wb.Worksheets(sheetToAdd)
Set wksht = .Worksheets(sheetToAdd + 1)
End With
Set AddWorksheet = wksht
End Function
Sample usage
Dim wks As Excel.Worksheet
Set wks = AddWorksheet(ActiveWorkbook, 6)
wks.Name = "Hello World"
End Sub
Excel ·
Rate This Article:
(No Ratings Yet)
Loading ...What now?
Subscribe To The Blog
Email This Article
Follow Me On Twitter
My Facebook Page
Or just keep reading:
I'm just an average guy who writes VBA code for a living. This is my personal blog. Excel and Outlook are my thing, with a sprinkle of Access and Word here and there. Follow this space if you want to learn more about VBA. Keep Reading »
Previous Post: Product Review: Chandoo's Project Management Templates
Next Post: Office links for February-March
Did you know that
worksheets.add.move after::=…
does just the same ?
Or you can create a new worksheet, give it a name, then move it where you want.
Sheets.Add.Name = SheetName
Sheets(SheetName).Move after:=…
Best regards.