'********************************************************************************************************** ' Name: newWorkbook ' Author: mielk | 2013-04-16 ' ' Comment: Function to create a new Excel workbook with the specific number of worksheets. ' Function allows to pass the initial names of worksheets being created. ' ' Parameters: ' sheetsNumber The number of worksheets to be inserted into newly created Excel workbook. ' names Names of the worksheets to be input into the created Excel workbook. ' * This is ParamArray argument, that means user can specify custom number of names ' (up to 30). ' * If the number of names is less than number of worksheets in the newly created ' workbook, rest of the worksheets will have their original names (i.e. Sheet3). ' * If the number of names is greater than the number of worksheets in the newly ' created workbook, all excessive names will be ignored. ' ' ' Returns: ' Excel.Workbook New Excel workbook with the given number of worksheets having specified names. ' ' ' --- Changes log ----------------------------------------------------------------------------------------- ' 2013-04-16 mielk Function created. '********************************************************************************************************** Public Function newWorkbook(sheetsNumber As Integer, ParamArray names() As Variant) As Excel.Workbook Const METHOD_NAME As String = "newWorkbook" '------------------------------------------------------------------------------------------------------ Dim defaultSheetsNumber As Integer Dim wks As Excel.Worksheet Dim varName As Variant Dim i As Long '------------------------------------------------------------------------------------------------------ 'Temporarily change the [SheetsInNewWorkbook] settings and save the current value of this -----------| 'setting to restore it after the function ends. '| With Excel.Application '| defaultSheetsNumber = .SheetsInNewWorkbook '| .SheetsInNewWorkbook = sheetsNumber '| End With '| '----------------------------------------------------------------------------------------------------| 'Create new Excel workbook. It will have desired number of worksheets, since property ---------------| '[SheetsInNewWorkbook] of current Excel.Application was temporarily changed. '| Set newWorkbook = Excel.Workbooks.add '| '----------------------------------------------------------------------------------------------------| 'Iterate through items of [names] param array and name worksheets based on the values of ------------| 'this array. '| For Each varName In names '| i = i + 1 '| '| 'Check if the iterator doesn't exceed the total number of sheets in the workbook. -----------| '| If i <= newWorkbook.Worksheets.Count Then '| '| Set wks = newWorkbook.Worksheets(i) '| '| wks.name = VBA.CStr(varName) '| '| End If '| '| '--------------------------------------------------------------------------------------------| '| '| Next varName '| '----------------------------------------------------------------------------------------------------| 'Restore previous [SheetsInNewWorkbook] setting. ----------------------------------------------------| Excel.Application.SheetsInNewWorkbook = defaultSheetsNumber '| '----------------------------------------------------------------------------------------------------| End Function