I'm learning Visual Basic for Applications, and I'm a little unsure of when to use procedures. For example, is a procedure needed every time an object variable is created? Or can an object variable be created without being executed as a procedure?
Could I have just
Dim wkbInventory As Workbook
Set wkbInventory = Application.Workbooks("name.xlsm")
Thank you!
A object variable can be declared outside a procedure. So the
Dim wkbInventory As Workbook
can be in the Declarations (see https://msdn.microsoft.com/en-us/library/dd897495%28v=office.12%29.aspx#odc_ac2007_bk_BeginningAccess2007VBA_Chapt2_CreatingModules) .
The scope of the variable is dependent on where the declaration happens. See http://www.cpearson.com/Excel/Scope.aspx.
But a variable can't be instantiated outside a procedure or function. So the
Set wkbInventory = Application.Workbooks("name.xlsm")
must be inside the Procedures within a sub procedure or a function.
Almost all of the work in VBA happens inside of macros. You can declare variables outside of macros (although this changes the variable's scope compared to declaring it inside a macro) and specify some compiler options but that's about it.
This is OK:
Option Explicit
' Declaration
Dim c As Collection
Sub foo()
Set c = New Collection
c.Add "hello", "world"
MsgBox c.Count
End Sub
whereas this will generate a compile error - "Invalid outside procedure":
Option Explicit
' Declaration
Dim c As Collection
' This causes an error
Set c = New Collection
Sub foo()
c.Add "hello", "world"
MsgBox c.Count
End Sub
If you use implicit creation with your variable declaration (Dim x As New y) then that works outside of a macro. Not all objects can be created that way though. This code works:
Option Explicit
' Declaration with implicit creation
Dim c As New Collection
Sub foo()
c.Add "hello", "world"
MsgBox c.Count
End Sub
whereas this code doesn't work (you'll get error 429 - ActiveX component can't create object):
Option Explicit
' Declaration with implicit creation
Dim w As New Workbook
Sub foo()
MsgBox w.FullName
End Sub
First recommendation I would make is to put Option Explicit at the top of your code(above all subs and functions). That will help you know when things are not correct.
Dim wkbInventory As Workbook Set wkbInventory = Application.Workbooks("name.xlsm")
Will not work. You will have to do
Dim wkbInventory As Workbook
Set wkbInventory = Application.Workbooks("name.xlsm")
FYI, there is late binding versus early binding that might help you (see also this thread for a an overview) . This is something to learn unrelated to procedures so I'm just providing as an if you already din't know...
You can early bind
' Set reference to 'Microsoft Excel 8.0 Object Library' in
' the Project|References dialog (or Tools|References for VB4 or VBA).
' Declare the object as an early-bound object
Dim oExcel As Excel.Application
Set oExcel = CreateObject("Excel.Application")
' The Visible property is called via the v-table
oExcel.Visible = True
' Good practice to explicitly set object variables to "Nothing" when
' done but not mandatory (especially not for local references).
Set oExcel = Nothing
Or late bind
' No reference to a type library is needed to use late binding.
' As long as the object supports IDispatch, the method can
' be dynamically located and invoked at run-time.
' Declare the object as a late-bound object
Dim oExcel As Object
Set oExcel = CreateObject("Excel.Application")
' The Visible property is called via IDispatch
oExcel.Visible = True
' Good practice to explicitly set object variables to "Nothing" when
' done but not mandatory (especially not for local references).
Set oExcel = Nothing
