VBA Code

The difference between "ThisWorkbook" and "ActiveWorkbook" refers to the workbook that is currently in the active window, whereas "ThisWorkbooK" refers to the workbook where the code is actually running from.

loop through all the worksheets
loop through all the workbooks in a folder

Activating and Selecting

Workbooks("Wbk1.xls").Activate 

Can be used if the workbook is new and has not yet been saved.

Workbooks("Book3").Activate 
Workbooks("Book3.xls").Activate 

assuming that the workbook has been saved

Workbooks.Item(2).Activate 

This refers to the workbook that contains the code.

ThisWorkbook. 

ThisWorkbook

This is always the workbook that contain the code.

Application.ThisWoorkbook 
ThisWorkbook 

ActiveWorkbook

This is the worksheet that is currently active or selected

Application.ActiveWorkbook 
ActiveWorkbook 
ActiveWorkbook.Path = "C:\Temp" 
ActiveWorkbook.Name = "Book2" 

Application.Height 
Application.Left 
Application.Top 
Application.Width 

Application.UsuableWidth 
Application.UsuableHeight 

Application.ProductCode 
Application.Hwnd 
Application.MemoryUsed 
Application.UsedObjects 

objWorkbook.CreateBackup = False 

ThisWorkbook.NewWindow 
Windows(2).Activate 

Read values from a closed workbook

Private Function GetInfoFromClosedFile(ByVal sFolderPath As String, _ 
                                       ByVal sWbkName As String, _ 
                                       ByVal sWshName As String, _ 
                                       ByVal sCellRange As String) As Variant 

Dim sargument As String 

    GetInfoFromClosedFile = "" 
    If Right(sFolderPath, 1) <> "\" Then sFolderPath = sFolderPath & "\" 
    If Dir(sFolderPath & "\" & sWbkName) = "" Then 
      Call MsgBox("File not found.") 
      Exit Function 
   End If 

    sargument = "'" & sFolderPath & "[" & sWbkName & "]" & sWshName & "'!" & Range(sCellRange).Address(True, True, xlR1C1) 
        
    On Error Resume Next 
    GetInfoFromClosedFile = ExecuteExcel4Macro(sargument) 
End Function 

Prevent Users From Saving

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) 
Dim lResult As VBA.VbMsgBoxResult 

   If SaveAsUI = True Then 
      lResult = MsgBox("You are unable to save this workbook in a different location!" & vbCrLf & _ 
                       "Would you like to save your changes ?", vbQuestion + vbYesNo) 
      If lResult = vbYes Then 
         Me.Save 
      End If 
   End If 
End Sub 

FullName

There is only a folder path when the workbook has been saved
This property is equivalent to the Path property, followed by the current file system separator, followed by the Name property.

ActiveWorkbook.FullName 

Determines the version of a workbook, i.e. which version of Excel created this workbook.

ActiveWorkbook.FileFormat 

16 = Excel 2
29 = Excel 3
33 = Excel 5
39 = Excel 5/96
-4143 = Excel 97/2000/2002/2003

undo the last action performed by the user interface.

Application.Undo 
Application.Repeat 

Returns the collection of recently opened files

Application.RecentFiles 

Read-only returns an object allowing manipulation of the office assistant.

Application.Assistant 

Some of the content in this topic may not be applicable to some languages.

Application Caption

Application.Caption = "message" 

Status Bar

It is possible to add your own messages to the status bar.
This can be used as a way to keep the user informed of the current action when a macro takes quite a long time to run.

Application.StatusBar = "Processing .." 
Application.StatusBar = False 

It is important to reset the Statusbar property to False when your macro has finished.
Otherwise your last message will remain in the statusbar until Excel is closed.

Whenever referencing workbooks or worksheets always enclose them in single speech marks "WshName" as they could contain spaces and/or unusual characters.
When you open a workbook using VBA even if you have the Application.DisplayAlerts = False you will be prompted whether to update links. To prevent being prompted change the "updatelinks" property in the Open method.
You can write an event procedure that will work for any workbook that is open but you need to use a class module
If you want something to happen to all the workbooks in a workbook use the "SheetActivate" event for "ThisWorkbook".
In previous versions of Excel the Auto_Open event was used to execute when a workbook is opened. It has since been supplemented / replaced with the Workbook-Open event which is stored in the ThisWorkbook module. Workbook_Open is executed before the Auto_Open event.
Event sequences are not always in an obvious order. The SheetActiveWorkbook event actually occurs before the WorkbookNewSheet. Application event when a new worksheet is added to a workbook.
Workbook event procedures must be in the Code Module for the "ThisWorkbook" Object. If they are anywhere else they will be ignored.
If you want to obtain the number of workbooks that are open you must remember if you use workbooks.count this will include any hidden workbooks (including your Personal.xls) You must check for visibility as well.
If you are referencing a value in another workbook use Workbook(---.Sheets(---).Range().value instead of "Worksheets".

Application.WindowState = xlMinimised 
ActiveWorkbook.Windows.Arrange ArrangeStyle := xlArrangeStyleTiled, SyncHorizontal := True 

ActiveWorkbook.Visible = False 
ThisWorkbook.CustomViews.Add "my view name" 

This returns the file name

ThisWorkbook.Name 

This returns the folder path

ThisWorkbook.Path 

This returns the folderpath and filename

ThisWorkbook.FullName 

To make sure that code works on both Windows and Macintosh use: Since the windows uses "\" and macs use ":".

Application.PathSeparator 

Application.Workbooks.Count 
sFolderPathAndFileName = ThisWorkbook.FullName 
Workbooks(Range("A2").Worksheet.Parent).Name 
?? = Workbooks.Count 
?? = Workbooks.Item(3) 
ThisWorkbook.ChangeFileAccess xlReadOnly 

Workbooks("Book2.xls").RefreshAll 
Set wbktemp = Workbooks.Filename :="temp.xls" 

AnswerWizard

There's only one Answer Wizard per application, and all changes to the AnswerWizard or the AnswerWizardFiles collection affect the active Office application immediately.

Application.AnswerWizard 
Application.AnswerWizard.ResetFileList 

Global Methods and Properties

The following methods and properties of the Application object are global and therefore can be used without the Application prefix.

Application.ActiveCell 
ActiveCell 

This list can be obtained from the top list of classes in the Object Browser.

Application.Caller

The Caller property of the Application object returns a reference to the object that called (or executed a macro procedure).
This property applies to controls on the Forms toolbar and drawing objects that have macros attached.
It is also particularly useful in determining the cell that called a user defined function.

Public Function WorksheetName() As String 
   Application.Volatile = True 
   WorksheetName = Application.Caller.Parent.Name 
End Function 

You cannot use "ActiveSheet.Name" in case the calculation is invoked when a different worksheet is selected.

Window Object Properties

There are quite few properties which you might think belong to the workbook or the worksheet objects but in fact belong to the Window object.
The following are all properties of the Window object (and not the workbook or worksheet)
ActiveCell
DisplayFormulas
DisplayGridlines
DisplayHeading
Selection

SelectedSheets

If you want to detect what sheets are currently grouped then you can use the SelectedSheets property of the Window object.
You might think that the SelectedSheets property should be a property of the Workbook but the reason for this is that you can open many windows on the same workbook and each window can have a different group of worksheets selected.

Activewindow(1).Windows(1).SelectedSheets.Count 

Height, Width

Total height in points of the active window

ActiveWindow.Height 

Total width in points on the active window

ActiveWindow.Width 

Top, Left

The position inside the main Excel window

ActiveWindow.Top 
ActiveWindow.Left 

Split

ActiveWindow.Split = False 

A maximised window has a top = -17 and a left = -275

If Windows.Count > 1 Then 
End If 

Zoom

ActiveWindow.Zoom = 100 

TabRatio

Returns or sets the ratio of the width of the workbook's tab area to the width of the window's horizontal scroll bar (as a number between 0 (zero) and 1; the default value is 0.6).
This property has no effect when DisplayWorkbookTabs is set to False (its value is retained, but it has no effect on the display).

ActiveWindow.TabRatio = 0.5 

The Workbooks Collections

The Workbooks collection consists of all currently open workbook objects.
Members can be added to the workbooks collection in a number of different ways.
You can create a new empty workbook based on the default workbook template or you can create a new workbook based on a different template.

New Workbook

This creates a new empty workbook based on the default template Book.xls.

Workbooks.Add 

This method will create a new workbook with the name BookX where X is the corresponding sequence number.
The new workbook will obviously be the active workbook so you can refer to it using the ActiveWorkbook property.

ActiveWorkbook.SaveAs FileName:="C:\Temp.xls" 

It is possible to create a new workbook that contains just a single worksheet:

Workbooks.Add(xlWBATemplate.xlWBATWorksheet) 

It is also possible to create a new workbook that contains just a single chart sheet:

Workbooks.Add(xlWBATemplate.xlWBATChart) 

Specific Template

The Add method also lets you specify a template to use for your new workbook.
The Add method allows you to specify a template for the new workbook.
The template does not have to be saved as a template (with a .xlt extension) it can be a normal workbook (.xls extension).
When the argument is a string specifying the folder location of an existing workbook the new workbook is created using this workbook as the template.

Dim objWorkbook As Workbook 
Set objWorkbook = Workbooks.Add (Template:="C:\Temp\MyTemplate.xls") 

This method will create a new workbook with the name MyTemplateX where X is the corresponding sequence number.

Reference to New Workbook

A better approach is to use the return value from the Add method to create an object variable referring to the workbook.

Dim objWorkbook As Workbook 
Set objWorkbook = Workbooks.Add 
objWorkbook.Range("A2").Value = "some text" 

This can be useful for keeping track of temporary workbooks without the need to save them.

Copying

VB.FileCopy("C:\temp\One.xlsm", "C:\temp\Copy.xlsm" 

Workbooks.Open

Dim oWorkbook As Excel.Workbook 
oWorkbook = Workbooks.Open (FileName:="", _ 
                            UpdateLink:=2, _ 
                            ReadOnly:=False, _ 
                            Format:= 
                            Password:= 
                            WriteResPassword:= 
                            IgnoreReadOnlyRecommended:= 
                            Origin:=xlPlatform. 
                            Delimiter:= 
                            Editable:= 
                            Notify:= 
                            Converter:= 
                            AddToMru:=False, _ 
                            Local:= 
                            CorruptLoad:=xlCorruptLoad. 

FileName - The filename of the workbook to open
UpdateLinks - Specifies how links in the file are updated. 1 user specifies how links will be updated. 2 never update the links. 3 always update the links. If this argument is omitted, the user is (meant to be) prompted to make a choice.
ReadOnly - Opens the file as read-only
Format - If you are opening a text file this specifies the delimiter character. 1 tabs. 2 commas. 3 spaces. 4 semi-colons. 5 nothing. 6 custom character.
Password - The password required to open the workbook.
WriteResPassword - The password required to write to a write-reserved workbook
IgnoreReadOnlyRecommended - Lets you supress the read-only recommended prompt (assuming the workbook was saved with a Read-Only recommendation).
Origin - If you are opening a text file this indicates where is originated.
Delimiter - If you are opening a text file and the 'Format' argument is 6 then this is the custom delimiter character.
Editable - If the file is an Excel template, then true opens the specific template for editing. False opens a new workbook based on this template.
Notify - If the file cannot be opened in read/write mode, true will add the file to the file notification list.
Converter - The index of the first file converter to try when opening the file.
AddToMru - Adds the workbook to the list of recently used files.
Local - Saves the file either against the language of VBA or against the language of Excel. True is Excel language, false is VBA language.
CorruptLoad - The first attempt is normal. If Excel stops operating while opening the file, the second attempt is safe load. If Excel stops operating on the second attempt then the next attempt is data recovery.

Opening Workbooks

When a workbook is opened it automatically becomes the active workbook.

Workbooks.Open (??) 
Workbooks.OpenText (??) 
Workbooks.Open "C:\temp\Temp.xls" 
ActiveWorkbook.OpenText( ????? ) 

Dim objWorkbook As Workbook 
Set objWorkbook = Workbooks.Open(FileName:="C:\Temp.xls") 

objWorkbook.Name = 
objWorkbook.Path = 
objWorkbook.FullName = 

Opening

Workbooks.Open (??) 
Workbooks.OpenText (??) 
Workbooks.Open "C:\temp\Temp.xls" 
ActiveWorkbook.OpenText( ????? ) 

Dim wbk As Workbook 
Set wbk = Workbooks.Open(FileName:="----.xls") 

Opening using a built-in File Dialog for browsing
See - more details for an example

Running a macro automatically when it opens

In the (Microsoft Excel Objects > ThisWorkbook) folder, select Workbook in the top left drop-down and select Open from the top right drop-down.
This event is the default event used when you select "Workbook"

Private Sub Workbook_Open() 
   Call Msgbox("Welcome ") 
End Sub 

Checking if a workbook exists

Private Function BET_WorkbookExists(sWbkName As String) As Boolean 
   If Dir(sWbkName) <> "" Then 
      BET_WorkbookExists = True 
   Else 
      BET_WorkbookExists = False 
   End If 
End Function 

Is the Workbook open

Set oWbk = Workbooks(sFileName) 
If (oWbk = Nothing) Then 
   ' not open
End If 

Checking if a workbook is currently open

This function returns true if the workbook is currently open
Tries to assign a object variable to the workbook
If the assignment was successful then the workbook must be open

Private Function BET_WorkbookIsOpen(sWbkName As String) As Boolean 
   Dim wbk As Workbook 
   On Error Resume Next 
   Set wbk = Workbooks(sWbkName) 
   If Not wbk Is Nothing Then 
      BET_WorkbookIsOpen = True 
   Else 
      BET_WorkbookIsOpen = False 
   End If 
End Function 

Running a macro automatically when it opens

In the (Microsoft Excel Objects > ThisWorkbook) folder, select Workbook in the top left drop-down and select Open from the top right drop-down.
This event is the default event used when you select "Workbook"

Private Sub Workbook_Open() 
   Call Msgbox("Welcome ") 
End Sub 

Closing the Active Workbook

Application.ActiveWorkbook.Close SaveChanges:=False, _ 
                                 Filename:="", _ 
                                 RouteWorkbook:=False 
ActiveWorkbook.Close 

The SaveChanges argument is ignored if the workbook appears in another open window.
If you are trying to save a new workbook that has not yet been saved then you can use the Filename argument to specify the location and filename of the saved workbook.

If changes have been made to the workbook then the following line will prompt the user to save these changes.

ActiveWorkbook.Close 

ThisWorkbook.Close 
Workbooks("Book2.xls").Close 
Workbooks(1).Close 

Have the changes been saved ?

You can use the Saved property of a workbook to determine if any changes have not been saved.

If Application.ActiveWorkbook.Saved = True Then 
   Application.ActiveWorkbook.Close 
Else 
   Call Msgbox("changes not saved") 
End If 

Closing all Workbooks without saving

This method has no arguments.

Application.Workbooks.Close SaveChanges:=False 

Close without a prompt

You can change the Saved property of the workbook to True and Excel will think that there are no changes to be saved.

ActiveWorkbook.Saved = True 

Closing the Active Workbook

Application.ActiveWorkbook.Close SaveChanges:=False, _ 
                                 Filename:="", _ 
                                 RouteWorkbook:=False 
ActiveWorkbook.Close 

The SaveChanges argument is ignored of the workbook appears in another open window.
If you are trying to save a new workbook that has not yet been saved then you can use the Filename argument to specify the location and filename of the saved workbook.

ThisWorkbook.Close 
Workbooks("Book2.xls").Close 
Workbooks(1).Close 

Have the changes been saved

You can use the Saved property of a workbook to determine if any changes have not been saved.

If Application.ActiveWorkbook.Saved = True Then 
   Application.ActiveWorkbook.Close 
Else 
   Call Msgbox("changes not saved") 
End If 

Closing all Workbooks without saving

This method has no arguments.

Application.Workbooks.Close 

Closing a workbook automatically after 10 minutes

ActiveWorkbook.Close Savechanges:=False 

SaveAs

link - docs.microsoft.com/en-us/office/vba/api/excel.workbook.saveas 

Allows you to save the workbook in a different format or with different attributes.

ActiveWorkbook.Save 
ActiveWorkbook.SaveAs FileName:="C:\Temp\Wbk1.xls" 

ActiveWorkbook.SaveAs Filename:="filename", _ 
                      FileFormat:=xlFileFormat.xlOpenXMLWorkbookMacroEnabled, _ 
                      Password:="", _ 
                      WriteResPassword:="", _ 
                      ReadOnlyRecommended:=False, _ 
                      CreateBackup:=False, _ 
                      AccessMode:=xlSaveAsAccessMode.xlNoChange, _ 
                      ConflictResolution:=xlSaveConflictResolution.xlLocalSessionChanges, _ 
                      AddToMru:=True, _ 
                      TextCodepage:="", _ 
                      TextVisualLayout:="", _ 
                      Local:=True, _ 
                      WorkIdentity:= 

Filename - Variant. A string that indicates the name of the file to be saved. You can include a full path; if you don't, Microsoft Excel saves the file in the current folder.
FileFormat - A xlFileFormat value that specifies the file format to use. For an existing file, the default format is the last file format specified; for a new file, the default is the format of the version of Excel being used.
Password - Variant. A case-sensitive string (no more than 15 characters) that indicates the protection password to be given to the file.
WriteResPassword - Variant. A string that indicates the write-reservation password for this file. If a file is saved with the password and the password isn't supplied when the file is opened, the file is opened as read-only.
ReadOnlyRecommended - Variant. True to display a message when the file is opened, recommending that the file be opened as read-only.
CreateBackup - Variant. True to create a backup file.
AccessMode - A xlSaveAsAccessMode value that determines the type of access that the file will have including an option for a shared workbook.
If this argument is omitted, the access mode isn't changed. This argument is ignored if you save a shared list without changing the file name. To change the access mode, use the ExclusiveAccess method.
ConflictResolution - A xlSaveConflictResolution value that determines how the method resolves a conflict while saving the workbook.
If this argument is omitted, the conflict-resolution dialog box is displayed.
AddToMru - Variant. True to add this workbook to the list of recently used files. The default value is False.
TextCodePage - Variant. Not used in Microsoft Excel.
TextVisualLayout - Variant. Not used in Microsoft Excel.
Local - Variant. True saves files against the language of Microsoft Excel (including control panel settings). False (default) saves files against the language of Visual Basic for Applications (VBA) (which is typically US English unless the VBA project where Workbooks.Open is run from is an old internationalized XL5/95 VBA project).
WorkIdentity - (Added in 365)

SaveCopyAs

ActiveWorkbook.SaveCopyAs "C:\temp\Copy.xlsm" 
Workbooks("Book2.xls").SaveCopyAs ("C:\temp\Backup.xls") 

Shared Workbooks

To make an Excel file available for shared use you must save the file using the SaveAs with AccessMode = xlShared
To terminate sharing the AccessMode xlExclusive is available.

Close without a prompt

You can change the Saved property of the workbook to True and Excel will think that there are no changes to be saved.

ActiveWorkbook.Saved = True 

Save a Workspace

Saves the current workspace to the filename parameter.

Application.SaveWorkspace 

Save ActiveSheet as PDF

Sub SaveActiveSheetsAsPDF() 
Dim saveLocation As String 
   saveLocation = "C:\Users\marks\OneDrive\Documents\myPDFFile.pdf" 
   ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=saveLocation 
End Sub 

ThisWorkbook or ActiveWorkbook ?

ActiveWorkbook refers to the workbook that is currently in the active window.
ThisWorkbook refers to the workbook where the code is actually running from.

ThisWorkbook.Path 

No matter where the workbook is saved ThisWorkbook.Path always returns the current location of the file.

Activating and Selecting

Workbooks("Wbk1.xls").Activate 

Can be used if the workbook is new and has not yet been saved.

Workbooks("Book3").Activate 
Workbooks("Book3.xls").Activate 

assuming that the workbook has been saved

Workbooks.Item(2).Activate 
Workbooks(2).Activate 

This refers to the workbook that contains the code.

ThisWorkbook. 

objWorkbook.Activate 

List all the open workbooks

For Each oWbk In Application.Workbooks 
    Debug.Print oWbk.Name 
Next oWbk 

Update Values

Update the links in a single workbook

ActiveWorkbook.UpdateLink Name:="C:\Temp\Workbook1.xls", _ 
                          Type:=xlLinkType.xlLinkTypeExcelLinks 

Change Source

Change the link folder path

ActiveWorkbook.ChangeLink Name:="C:\Temp\Workbook2.xls", _ 
                          NewName:="C:\Temp\Charts1.xls", _ 
                          Type:=xlLinkType.xlLinkTypeExcelLinks 

Dim iValue As Integer 
Dim arArray As Variant() 
arArray = ActiveWorkbook.LinkSources(xlLink.xlOLELinks) 
'returns an array of links in the workbook in the format DDE link - Server|Document!Item

For icount = 1 To UBound(arArray) 
   iValue = ActiveWorkbook.LinkInfo(arArray(icount),xlLinkInfo.xlUpdateSate, xlLinkInfoType.xlLinkInfoOLELinks) 

   If (iValue = 1) Then 'link updates automatically  
   If (iValue = 2) Then 'link updates manually  
Next icount 

xlLinkStatus ??
xlUpdateLinks ??

Protecting Workbooks

It is possible to protect a worksheet but to allow it to be modified using VBA code. To protect the worksheet use VBA Code and set the "UserInterfaceOnly" parameter to True.

ActiveWorkbook.Protect Structure:=True, Windows:=True 
ActiveWorkbook.UnProtect Structure:=True, Windows:=True 
ActiveWorkbook.Unprotect 
ActiveSheet.Protect 

ActiveSheet.Unprotect 
ActiveSheet.Unprotect Password:=sPwdName 

Protect all Worksheets

Public Sub ProtectAllSheets 
Dim wshSheet as Worksheet 
   For Each wshSheet In ActiveWorkbook.Worksheets 

   Next wshSheet 
End Sub 

Within each edit range you can specify who can edit the range without unlocking the entire worksheet
You can specify that a user provide a range specific password in order to make changes to the worksheet.

Excluding Cells

This can be done using the AllowEditRanges collection
This must be used before calling the Protect Method.
Any exclusions made in this way can be given a title and will display in the Allow Users to Edit Range dialog box.
A range excluded in this way will return True from its Range.AllowEdit property

Excel.AllowEditRanges oEdits = sheet.Protection.AllowEditRanges 
oEdits.Add("-----",sheet.Range["A1"]) 
sheet.Protect 

The other way is to change to locked property for the Range

Worksheet.EnableAutofilter 
Worksheet.EnabeOutlining 
Worksheet.EnablePivotTable 
Worksheet.EnableSelection 
Worksheet.ProtectContents 
Worksheet.ProtectDrawingObjects 
Worksheet.ProtectionMode 
Worksheet.ProtectScenarios 

AllowEditRange and UserAccessList

The AllowEditRange object can be used to specify which users can edit specific ranges and whether they must use a password.
Each worksheet contains an AllowEditRange collection that contains the collection of edit ranges for that worksheet.

ActiveSheet.Protection.AllowEditRanges 

It is possible to provide different combinations of permissions to different ranges.

The list of users for each AllowEditRange object is stored in the UserAccessList collection

You just have to make do with the EnableSelection property - although the problem is this property is not saved with the workbook.
The new protection object lets us selectively control the features that are accessible to users when we protect a worksheet.
We can decide whether users can sort, alter cell formatting, or insert or delete rows and columns for example.
There is also a new AllowEditRange object that we can use to specify which users can edit specific ranges and whether they must use a password to do so.
We can apply different combinations or permissions to different ranges.

For management of this new protection option the Protect method of the worksheet has been expanded.
Also the Protection object provides information about the current protection options.
Also you can give individual users (with or without a password) access to selected groups within a protected worksheet.
This is practical when several users are allowed to access the same worksheet but not every one of those users is permitted to make changes.

The AllowEditRange object represents a range of cells on a worksheet that can still be edited after it has been protected.
Each AllowEditRange object can have permissions set for any number of users on a network and can have a separate password.

Be aware of the Locked property of the Range object when using this feature.
When you unlock cells, then protect the worksheet, you are allowing any user access to those cells, regardless of the AllowEditRange objects
When each AllowEditRange object's cells are locked any user can still edit them unless you assign a password or add users to deny them permissions without using a password.

The AllowEditRanges collection represents all AllowEditRange objects that can be edited on a protected worksheet

AllowEditRange Example

The following example loops through a list of named ranges in a worksheet and adds an AllowEditRange item for each one whose name begins with "pc".
It also denies access to the pcNetSales range to all but one user, who can only edit the range with a password.

Sub CreateAllowRanges 
Dim lPos As Long 
Dim nm As Name 
Dim objAllowEditRange As AllowEditRange 
Dim sName As String 

With wksAllowEditRange 
'loop through the worksheet level named ranges
   For Each nm In .Names 
'store the name
      sName = nm.Name 
'locate the position of the !
         lPos = InStr(1, sName, "!", vbTextCompare) 

         If lPos > 0 Then 
'is there a "Pc" just after the exclamation mark point
'if so it’s a named range we want to create an AllowEditRange object for
            If Mid(sName, lPos + 1, 2) = "pc" Then 
'make sure the cells are locked
'unlocking the cells will allow any user to access them
               nm.RefersToRange.Locked = True 

'pull out the worksheet reference (including the "!")
               sName = Right(sName, Len(sName) - lPos) 

'create the AllowEditRange object
'remove the old one if it exists
               On Error Resume Next 
               Set objAllowEditRange = Nothing 
               Set objAllowEditRange = .Protection.AllowEditRanges(sName) 
               On Error GoTo 0 

               If Not objAllowEditRange Is Nothing Then 
                  objAllowEditRange.Delete 
               End If 

               Set objAllowEditRange = .Protection.AllowEditRanges.Add(sName), nm.RefersToRange) 

'if it is the sales named range then
               If sName = "pcNetSales" Then 
   ''add a password, then
'add a user and deny them from editing the range without a password
                  objAllowEditRange.ChangePassword "pcnsw" 
                  objAllowEditRange.Users.Add "RCR\UserName", False 
               End If 
            End If 
         End If 
      Next nm 
   End With 

End Sub 

objWorkbook.Protect(Password:="mypassword", 
                             Structure:=True, 
             Windows:=True) 

Password - the case sensitive password for the workbook
Structure - (Optional) Whether to protect the structure of the workbook. The default is false
Windows - (Optional) Whether to protect the workbook windows. The default is false.

Setting structure to true will protect the worksheet order, preventing rearranging of the sheets
Setting windows to true will prevent the windows from being moved or resize. It will keep them in their tiled position.

Workbook Properties

Unlike Word, there is no concept of Workbook Variables, only Workbook Properties.

Workbook Built-in Properties

ActiveWorkbook.BuiltinDocumentProperties.Item("Subject") 
ActiveWorkbook.BuiltinDocumentProperties("Subject") 
ActiveWorkbook.BuiltinDocumentProperties.Item(2) 
ActiveWorkbook.BuiltinDocumentProperties(2) 

This returns a collection that represents all the built-in document properties for the specified workbook.
You can refer to built-in document properties either by index value (1 based) or by name.
The following list shows the available built-in document property names.
There is no enumeration like there is in Word.

Workbook Custom Properties

objDocument.CustomDocumentProperties 

Public Sub DocumentProperties() 
Dim icount As Integer 
Dim objDocumentProperty As DocumentProperty 

   For icount = 1 To ActiveWorkbook.BuiltinDocumentProperties.Count 
      Set objDocumentProperty = ActiveWorkbook.BuiltinDocumentProperties.Item(icount) 

      Debug.Print objDocumentProperty.Name 
      Debug.Print objDocumentProperty.LinkSource 
      Debug.Print objDocumentProperty.LinkToContent 
      Debug.Print objDocumentProperty.Parent 
      Debug.Print objDocumentProperty.Type 
      Debug.Print objDocumentProperty.Value 
   Next icount 
End Sub 

Name - Specifies the property name
Type - The type of the property
Value - This is current setting

With the properties LinkSource and LinkToContent the value of a custom property can be linked directly to the contents of a worksheets
LinkSource - Gets or sets the source of a linked custom document property. The LinkToContent the value of a custom property can be linked directly to the contents of a worksheets
LinkToContent - Is True if the value of the custom document property is linked to the content of the container document. False if the value is static. This must be set to True and LinkSource must be a named range to the cell.

Workbook Custom Properties

ActiveWorkbook.CustomDocumentProperties 

This property returns the entire collection of custom document properties.
You can only refer to the custom document properties by their name. You cannot use a 1-based index value.
Use the Item method to return a single member of the collection (a DocumentProperty object) by specifying either the name of the property or the collection index (as a number).

CustomDocumentProperties.Item("BetterSolutions") 
CustomDocumentProperties("BetterSolutions") 

msoPropertyTypeString

This will extract all the custom properties from a workbook

Public Sub CustomProperties_Extract() 
Dim icount As Integer 
Dim objDocumentProperty As DocumentProperty 
   For icount = 1 To ActiveWorkbook.CustomDocumentProperties.Count 
      Set objDocumentProperty = ActiveWorkbook.CustomDocumentProperties.Item(icount) 
      ThisWorkbook.Worksheets("Sheet1").Range("A" & icount).Value = objDocumentProperty.Name 
      ThisWorkbook.Worksheets("Sheet1").Range("B" & icount).Value = objDocumentProperty.Value 
      ThisWorkbook.Worksheets("Sheet1").Range("C" & icount).Value = objDocumentProperty.Type 
   Next icount 
End Sub 

This will import a list of custom properties into a workbook

Public Sub CustomProperties_Import() 
Dim lrowlast As Long 
Dim lrowno As Long 
   lrowlast = ThisWorkbook.Worksheets("Sheet1").Range("A1").SpecialCells(xlLastCell).Row 
   For lrowno = 1 To lrowlast 
      Call Workbooks("ISP.xlsx").CustomDocumentProperties.Add( _ 
         Name:=ThisWorkbook.Worksheets("Sheet1").Range("A" & lrowno).Value, _ 
         LinkToContent:=Office.MsoTriState.msoFalse, _ 
         Value:=ThisWorkbook.Worksheets("Sheet1").Range("B" & lrowno).Value, _ 
         Type:=Office.MsoDocProperties.msoPropertyTypeString) 
   Next lrowno 
End Sub 

How to Compare 2 workbooks ?

This procedure creates a new workbook which lists the comparison results for each worksheet in the workbooks.
Open the 2 workbooks you would like to compare.
In this examples the files have the names "BetterSolutions_1.xlsm" and "BetterSolutions_2.xlsm".

Public Sub CompareTwoWorkbooks() 
  Dim WS As Worksheet 
  Workbooks.Add 
  For Each WS In Workbooks("BetterSolutions_1.xlsm").Worksheets 
    Call CompareWorksheets(WS, Workbooks("BetterSolutions_2.xlsm").Worksheets(WS.Name)) 
  Next 
End Sub 

Public Sub CompareWorksheets(ByVal WS1 As Worksheet, _ 
                             ByVal WS2 As Worksheet) 
Dim iRow As Integer 
Dim iCol As Integer 
Dim R1 As Range 
Dim R2 As Range 

  ' add the corresponding worksheet for the results
  Worksheets.Add.Name = WS1.Name         
  Range("A1:D1").Value = Array("Address", "Difference", WS1.Parent.Name, WS2.Parent.Name) 
  Range("A2").Select 
  For iRow = 1 To Application.Max(WS1.Range("A1").SpecialCells(xlLastCell).Row, _ 
                      WS2.Range("A1").SpecialCells(xlLastCell).Row) 
    For iCol = 1 To Application.Max(WS1.Range("A1").SpecialCells(xlLastCell).Column, _ 
                      WS2.Range("A1").SpecialCells(xlLastCell).Column) 
      
      Set R1 = WS1.Cells(iRow, iCol) 
      Set R2 = WS2.Cells(iRow, iCol) 
      
      ' compare the types to avoid getting VBA type mismatch errors.
      If (TypeName(R1.Value) <> TypeName(R2.Value)) Then 
        Call LogDifference(R1.Address, "Type", R1.Value, R2.Value) 
      ElseIf (R1.Value <> R2.Value) Then 
        If (TypeName(R1.Value) = "Double") Then 
          If (Abs(R1.Value - R2.Value) > R1.Value * 10 ^ (-12)) Then 
            Call LogDifference(R1.Address, "Double", R1.Value, R2.Value) 
          End If 
        Else 
            Call LogDifference(R1.Address, "Value", R1.Value, R2.Value) 
        End If 
      End If 
      
      ' record formulae without leading "=" to avoid them being evaluated
      If (R1.HasFormula = True) Then 
        If (R2.HasFormula = True) Then 
          If (R1.Formula <> R2.Formula) Then 
            Call LogDifference(R1.Address, "Formula", Mid(R1.Formula, 2), Mid(R2.Formula, 2)) 
          End If 
        Else 
          Call LogDifference(R1.Address, "Formula", Mid(R1.Formula, 2), "**no formula**") 
        End If 
      Else 
        If (R2.HasFormula = True) Then 
          Call LogDifference(R1.Address, "Formula", "**no formula**", Mid(R2.Formula, 2)) 
        End If 
      End If 
      If (R1.NumberFormat <> R2.NumberFormat) Then 
        Call LogDifference(R1.Address, "NumberFormat", R1.NumberFormat, R2.NumberFormat) 
      End If 
    Next iCol 
  Next iRow 
  With ActiveSheet.UsedRange.Columns 
    .AutoFit 
    .HorizontalAlignment = xlLeft 
  End With 
End Sub 

Public Sub LogDifference(ByVal Address As String, _ 
                         ByVal What As String, _ 
                         ByVal V1 As Variant, _ 
                         ByVal V2 As Variant) 
  ActiveCell.Resize(1, 4).Value = Array(Address, What, V1, V2) 
  ActiveCell.Offset(1, 0).Select 
  If ActiveCell.Row = Rows.Count Then 
    Call MsgBox("Too many differences", vbExclamation) 
    End 
  End If 
End Sub 

Templates

This refers to the path of the local directory with add-in files.

Application.UserLibraryPath

Application.UserLibraryPath 

Options > VBA Code > Folder Paths

Application.TemplatesPath

This refers to the local Templates directory

Application.TemplatesPath 

Options > VBA Code > Folder Paths

Application.StartupPath

This refers to the local xlstart directory

Application.StartupPath 

Options > VBA Code > Folder Paths
There are no properties to determine the global templates and startup directories.

Application.NetworkTemplatesPath

Application.NetworkTemplatesPath 

Options > VBA Code > Folder Paths

Application.Repeat 

New Workbook

This creates a new empty workbook with the default number of worksheets

Workbooks.Add 

It is possible to create a new workbook that contains just a single worksheet:

Workbooks.Add(xlWBATemplate.xlWBATWorksheet) 

It is also possible to create a new workbook that contains just a single chart sheet:

Workbooks.Add(xlWBATemplate.(xlWBATChart) 

Reference to New Workbook

A better approach is to use the return value from the Add method to create an object variable referring to the workbook.

Dim wbk As Workbook 
Set wbk = Workbooks.Add 
wbk.Range("A2").Value = "some text" 

This can be useful for keeping track of temporary workbooks without the need to save them.

Specific Template

The Add method also lets you specify a template to use for your new workbook.
When the argument is a string specifying the folder location of an existing workbook the new workbook is created using this workbook as the template.

Set wbk = Workbooks.Add (Template:="C:\Temp\"wbkTemplate.xls") 

Running A Batch

Option Explicit 

Public Sub SelectSomeFiles() 
Dim fileDialog As Office.fileDialog 
Dim varFile As Variant 
Dim sFolderPath As String 
Dim sFileName As String 
Dim objWorksheet As Worksheet 
Dim objWorkbook As Workbook 
Dim lrowno As Long 

    On Error GoTo ErrorHandler 
    Set fileDialog = Application.fileDialog(MsoFileDialogType.msoFileDialogFilePicker) 
    fileDialog.Title = "Batch Runner" 
    fileDialog.InitialFileName = "C:\temp\" 
    fileDialog.AllowMultiSelect = True 
    fileDialog.Filters.Clear 
    fileDialog.Filters.Add "Excel Workbooks", "*.xls*" 
    If fileDialog.Show = False Then 
        Exit Sub 
    End If 
    If MsgBox("You have selected " & fileDialog.SelectedItems.Count & " file(s)" & _ 
              vbCrLf & vbCrLf & "Are you sure ?", _ 
              VBA.VbMsgBoxStyle.vbYesNo + vbQuestion, _ 
              "Batch Runner") = VBA.VbMsgBoxResult.vbNo Then 
        Exit Sub 
    End If 
    Set objWorksheet = Workbooks(ThisWorkbook.Name).Sheets("Results") 
    Call RemoveAllFiltering(objWorksheet) 
    objWorksheet.Cells.ClearContents 
    objWorksheet.Cells.Font.Bold = False 
    objWorksheet.Select 
    objWorksheet.Range("A1").Select 
    objWorksheet.Range("A1:B1").Value = Array("File Name", "File Size") 
    objWorksheet.Range("A1:B1").Font.Bold = True 
    lrowno = 2 
    sFolderPath = Left$(fileDialog.SelectedItems(1), InStrRev(fileDialog.SelectedItems(1), "\")) 
    sFolderPath = sFolderPath & "__Modified-" & Format(Now(), "yyyy_mmm_dd-hh_mm") & "\" 
    Call MkDir(sFolderPath) 
    For Each varFile In fileDialog.SelectedItems 
        objWorksheet.Range("A" & lrowno).Value = varFile 
        objWorksheet.Range("B" & lrowno).Value = VBA.FileLen(varFile) / 1000 & " KB" 
        sFileName = Mid$(varFile, InStrRev(varFile, "\") + 1) 
        Application.DisplayAlerts = False 
        If WbkOpenedSuccessfully(varFile, objWorkbook) = True Then 
            Application.DisplayAlerts = True 

            ' do something or check something

            Call objWorkbook.Close(False) 
        Else 
            objWorksheet.Range("D" & lrowno).Value = "Unable to Open" 
        End If 
        lrowno = lrowno + 1 
        If (lrowno Mod 10 = 0) Then 
            Workbooks(ThisWorkbook.Name).Save 
        End If 
    Next 
    objWorksheet.Range("A1").AutoFilter 
    objWorksheet.Columns("A:E").EntireColumn.AutoFit 
    Call MsgBox("Completed", vbInformation + vbOKOnly) 
    Exit Sub 
ErrorHandler: 
    Call MsgBox(Err.Number & " - " & Err.Description) End Sub 

Private Sub RemoveAllFiltering(ByVal objWorksheet As Excel.Worksheet) 
    On Error GoTo ErrorHandler 
    objWorksheet.ShowAllData 
ErrorHandler: 
End Sub 

Private Function WbkOpenedSuccessfully( _ 
    ByVal varFile As String, _ 
    ByRef objWorkbook As Excel.Workbook) As Boolean 
    On Error GoTo ErrorHandler 
    Set objWorkbook = Workbooks.Open(Filename:=varFile, UpdateLinks:=0) 
    WbkOpenedSuccessfully = True 
    Exit Function 
ErrorHandler: 
    WbkOpenedSuccessfully = False 
End Function 

Private Function WbkSavedSuccessfully( _ 
    ByVal sFolderPath As String, _ 
    ByVal sFileName As String, _ 
    ByVal objWorkbook As Excel.Workbook) As Boolean 
    On Error GoTo ErrorHandler 
    objWorkbook.SaveAs Filename:=sFolderPath & sFileName 
    WbkSavedSuccessfully = True 
    Exit Function 
ErrorHandler: 
    WbkSavedSuccessfully = False 
End Function 

Private Function PropertyExists( _ 
    ByVal objWorkbook As Workbook, _ 
    ByVal sName As String, _ 
    ByRef docProperty As Office.DocumentProperty) As Boolean 
    On Error GoTo ErrorHandler 
    Set docProperty = objWorkbook.CustomDocumentProperties.Item(sName) 
    PropertyExists = True 
    Exit Function 
ErrorHandler: 
    PropertyExists = False 
End Function