Since function getVBProject uses some non-built VBA functions, they also must be included in your code for the function to work properly.
Otherwise the following error will occur: Compile error: Sub or Function not defined.
Required functions are listed below. You can get to each function's source code by clicking its name:
When adding the functions above to your VBA project, make sure you haven't done it before. If there are two different public functions with the same name in a single VBA project, the following compilation error is thrown: Compile error: Ambiguous name detected: function_name.
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
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
'**********************************************************************************************************
' Name: getVBProject
' Author: mielk | 2015-03-01
'
' Description: Function to return Visual Basic Project defined by the given parameter [book].
'
' Parameters:
' book Parameter defining what VB project should be returned.
' This parameter can take several types of values:
' * object of Excel.Workbook type - in this case VB project assigned to this file
' is returned.
' ** the name of workbook - in this case the function tries to find a workbook with
' such a name among all the open workbooks. If the workbook is found, VB project
' assigned to this workbook is returned, otherwise a proper exception is thrown
' and the function returns Nothing.
' *** the path of the workbook - in this case the function checks if the given path
' is correct and tries to open this Excel file. If this workbook could be opened
' VBProject assigned to this workbook is returned. Otherwise a proper exception
' is thrown and function returns Nothing.
'
'
' Returns:
' VBProject Object of VB project type, representing Visual Basic project assigned to the Excel
' workbook defined by the input parameter [book].
'
'
' Exceptions:
' IllegalTypeException Thrown if the parameter [book] has other type than any of available
' types described above.
' IllegalWorkbookException Thrown if the parameter [book] is of Excel.Workbook type, but it cannot
' be used for any reason (i.e. it has been already closed or deleted).
' WorkbookNotExistException Thrown if the parameter [book] is a string defining the path or the
' name of a workbook, but the function could not find such a file.
'
'
' --- Changes log -----------------------------------------------------------------------------------------
' 2015-03-01 mielk Function created.
'**********************************************************************************************************
Public Function getVBProject(book As Variant) As vbProject
Const METHOD_NAME As String = "getVBProject"
'------------------------------------------------------------------------------------------------------
Const STRING_TYPE As String = "String"
'------------------------------------------------------------------------------------------------------
Dim wkb As Excel.Workbook
Dim path As String
'------------------------------------------------------------------------------------------------------
'Check the type of the input parameter [wkb] and select the proper logic depending on this type.
If TypeOf book Is vbProject Then
Set getVBProject = book
ElseIf TypeOf book Is Excel.Workbook Then
'Assign the given parameter [book] to variable [wkb] for data-type compatibility.
Set wkb = book
'Check if the given workbook is a valid one. If not, throw IllegalWorkbookException. ------------|
If Not isBookValid(wkb) Then '|
Set wkb = Nothing '|
GoTo IllegalWorkbookException '|
Else '|
Set getVBProject = wkb.vbProject '|
End If '|
'------------------------------------------------------------------------------------------------|
ElseIf VBA.TypeName(book) = STRING_TYPE Then
'Assign the given parameter [book] to variable [path] for data-type compatibility.
path = stringify(book)
'------------------------------------------------------------------------------------------------|
If fileExists(path) Then '|
'|
'Try to open a workbook with the given path. If the path is not found and exception is '|
'raised by the function [openFile], catch this exception and go to '|
'[WorkbookNotExistException] where this function's own exception is thrown. '|
On Error GoTo WorkbookNotExistException '|
Set wkb = openWorkbook(path, False) '|
'|
Else '|
'|
'Try to find workbook as if the given parameter [book] was its name only. '|
On Error GoTo WorkbookNotExistException '|
Set wkb = Excel.Workbooks(path) '|
'|
End If '|
'------------------------------------------------------------------------------------------------|
'Check if a workbook has been successfully assigned to variable [wkb]. If it has, ---------------|
'assign its VBA project to the result of the function. '|
If Not wkb Is Nothing Then '|
'|
Set getVBProject = wkb.vbProject '|
'|
Else '|
'|
'If workbook with such filepath cannot be opened WorkbookNotExistException '|
'exception is thrown. '|
On Error GoTo WorkbookNotExistException '|
'|
End If '|
'------------------------------------------------------------------------------------------------|
Else
'If function reaches this point, it means that the parameter [book] was of illegal type and
'exception IllegalTypeException is thrown.
GoTo IllegalTypeException
End If
'==========================================================================================================
ExitPoint:
Exit Function
'----------------------------------------------------------------------------------------------------------
IllegalTypeException:
'(...)
'Error handling for a case if parameter [book] is of other type than Excel.Workbook or String.
GoTo ExitPoint
IllegalWorkbookException:
'(...)
'Error handling for a case if parameter [book] is of Excel.Workbook type but it cannot be
'referred (i.e. it has been closed or deleted).
GoTo ExitPoint
WorkbookNotExistException:
'(...)
'Error handling for a case if parameter [book] is of String type, but function could not find
'any workbook with such name or file path.
GoTo ExitPoint
End Function