Skip to content

Instantly share code, notes, and snippets.

@jaykilleen
Last active August 29, 2015 14:17
Show Gist options
  • Save jaykilleen/6d16938a45760919bb6d to your computer and use it in GitHub Desktop.
Save jaykilleen/6d16938a45760919bb6d to your computer and use it in GitHub Desktop.
Excel VBA Basic Initialization module
Option Explicit
'Call this method from your main VBA module using 'Call init' or 'Call init.init' (if placed in a module called 'init')
'Declare the activeworkbook and any other related workbooks required
Public wb_1 As Workbook 'this is your activeworkbook where this VBA will be hosted
Public wb_2 As Workbook
'Delcare the worksheets that you will be interactive with
Public ws_1 As Worksheet
Public ws_2 As Worksheet
Public ws_3 As Worksheet
'Declare the last row values of the worksheets you will be using
Public lr_table1 As Long
Public lr_unique As Long
Sub init()
'Set your workbooks
Set wb_1 = Worksbooks("Book 1.xlsm")
Set wb_2 = Worksbooks("Book 2.xlsm")
'Set your worksheets
Set ws_sheet1 = wb_1.Sheets("sheet1")
Set ws_sheet2 = wb_1.Sheets("sheet2")
Set ws_sheet3 = wb_2.Sheets("sheet3")
'Calculate your last rows
lr_sheet1 = ws_sheet1.Range("A" & Rows.Count).End(xlUp).Row
lr_sheet2 = ws_sheet2.Range("A" & Rows.Count).End(xlUp).Row
lr_sheet3 = ws_sheet3.Range("A" & Rows.Count).End(xlUp).Row
End Sub
@jaykilleen
Copy link
Author

Names for worksheets would probably be things like:

ws_sales = wb_1.Sheets("sales")
ws_customers = wb_1.Sheets("customers")
ws_export_as_csv = wb_2.Sheets("export_as_csv")

I try and keep the ws_name matching the Sheets("name")

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment