1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
'**********************************************************************************************************
' 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