Unit 6 – Excel VBA Methods

This Excel VBA tutorial explains how to use Range.PasteSpecial Method to paste special such as paste values.

Excel VBA Range PasteSpecial Method

In Excel worksheet, if you copy a Cell and then Paste Special, you can see a list of options. The most commonly used Paste Special is Paste Values, in order to remove all the formula.

In Excel VBA, Paste Speical is done through Range.PasteSpecial Method.

Syntax of Range.PasteSpecial Method

Range.PasteSpecial(Paste, Operation, SkipBlanks, Transpose)

Example 1 – Paste Value

The below VBA copy the whole active worksheet and paste as value.

Public Sub pasteVal()
    ActiveSheet.Cells.Copy
    ActiveSheet.Cells.PasteSpecial Paste:=xlPasteValues
End Sub

Example 2 – Multiply

Suppose we want to multiply Range A1:C2 by 2.

Public Sub pasteMultiply()
    Range("E1").Copy  'Suppose E1 contains value 2
    Range("A1:C2").PasteSpecial Operation:=xlPasteSpecialOperationMultiply
End Sub

Result

Copy and Paste in one line

Incidentally, if you just want to copy and paste normally, you can use Copy Method with Destination argument.

The below example copy Range A1:C2 and paste in A10.

Public Sub cpy()
    Range("A1:C2").Copy Destination:=Range("A10")
End Sub

This Excel VBA tutorial explains how to use Worksheet.Select Method to select a single worksheet or multiple worksheets.

Select worksheets in Excel

When you click on a worksheet tab, the worksheet is highlighted.

To select multiple worksheets, you can hold down Ctrl and then left click the mouse on each worksheet tab.

To select all worksheets at once, right click on one of the sheet, and then click on Select All Sheets

One practical use of selecting multiple worksheets is to print selected worksheets.

In this tutorial, I will explain how to perform the same tasks in the above scenarios using Excel VBA Worksheet.Select Method.

Excel VBA Worksheet.Select Method

In Excel VBA, it is not necessary to select worksheets in order to run a Macro on selected worksheets, because you can use VBA to loop through worksheets with specific name.

Syntax of Worksheet.Select Method

Worksheet.Select(Replace)

Example 1 – Select a single worksheet

To select Sheet1 only

Sheets("Sheet1").Select

Example 2 – Select multiple worksheets

To select Sheet1 and Sheet2, use the False Property in Sheet2

you can also add the False argument for the first Worksheet

Sheets("Sheet1").Select False
Sheets("Sheet2").Select False

Example 3 – Select all worksheets in the workbook

The below example selects all worksheets in current workbook

Public Sub selectAllWS()
   For Each ws In ThisWorkbook.Sheets
      ws.Select flase
   Next
End Sub

After you have selected all worksheets, you can deselect them by selecting anyone of the worksheet. To avoid specifying which worksheet, I use ActiveSheet in the below example.

In multiple selection, ActiveSheet refers to the first selected worksheet.

Public Sub deselectWS()
   ActiveSheet.Select
End Sub

You can also select multiple worksheets using Array.

Outbound References

https://msdn.microsoft.com/en-us/library/office/ff194988.aspx

This Excel VBA tutorial explains how to use Workbooks.Open Method to open another workbook.

Excel VBA Workbooks.Open Method

Workbooks.Open Method is useful when you try to open another Workbook using VBA. For example, you can open a workbook > format the spreadsheet > close the workbook automatically. There are a lot of rarely used arguments for Workbooks.Open Method. As most of them are self explanatory, I will demonstrate some common uses of the Method.

Syntax of Workbooks.Open Method

expression .Open(FileName, UpdateLinks, ReadOnly, Format, Password, WriteResPassword, IgnoreReadOnlyRecommended, Origin, Delimiter, Editable, Notify, Converter, AddToMru, Local, CorruptLoad)

Example 1 – Open a workbook

The below code opens workbook 1.xls and set the workbook as “wb” so that you can use wb to access other workbook Methods.

Public Sub openWB()
    Set wb = Workbooks.Open("C:\Users\WYMAN\Desktop\excelfolder\1.xls")
End Sub

Example 2 – Open a workbook with password

There are two kinds of password protection – password to open the workbook, and password to modify the workbook.

In either case, if a workbook is password protected, opening the file will pop up a password box.

You can add the password argument to open the file with password automatically. If you open a non-password protected workbook but you add the password argument, you can still open the workbook.

Lets say you have a workbook 3.xlsx which is non-password protected, using the below code will still be able to open the workbook.

Public Sub openWBpw()
    Set wb = Workbooks.Open(Filename:="C:\Users\WYMAN\Desktop\excelfolder\3.xlsx", Password:="yourpassword")
End Sub

Example 3 – Open a workbook without alert

There are many kinds of alerts that may pop up when you open a workbook, preventing you from running the subsequent procedures. Turn off all alerts ensures you to run all the procedures successfully.

Public Sub openWB2()
    With Application
        .DisplayAlerts = False
        .ScreenUpdating = False
        .EnableEvents = False
        .AskToUpdateLinks = False
    End With
    Set wb = Workbooks.Open(Filename:="C:\Users\WYMAN\Desktop\excelfolder\3.xlsx", Password:="yourpassword")
    With Application
        .DisplayAlerts = True
        .ScreenUpdating = True
        .EnableEvents = True
        .AskToUpdateLinks = True
    End With
End Sub

Other examples

Add password to all Excel workbook in folder

Excel VBA refresh closed workbook

Use Excel Workbooks Open Method to check if workbook open

Outbound References

https://msdn.microsoft.com/en-us/library/office/ff194819.aspx

This Excel VBA tutorial explains how to use Workbook.SaveAs Method.

You may also want to read:

Excel VBA convert CSV to Excel

Excel VBA Workbook.SaveAs Method

VBA Workbook.SaveAs Method is same as the action Save As

The options under Tools are also available in VBA Workbook.SaveAs Method.

For example, we can add password to workbook.

To understand what Workbook.SaveAs Method is capable of, let’s see what arguments it has.

Syntax – Excel VBA Workbook.SaveAs Method

expression .SaveAs(FileName, FileFormat, Password, WriteResPassword, ReadOnlyRecommended, CreateBackup, AccessMode, ConflictResolution, AddToMru, TextCodepage, TextVisualLayout, Local)

Example – Excel VBA Workbook.SaveAs Method

When you save as a workbook in a location where the workbook name already exists, you will receive the following prompt box.

In order for the Macro to continue to run without prompting the box, the first thing to do is to temporarily turn the alert box off using Application.DisplayAlerts = False, and all answers will become Yes.

The second thing is the Filname argument. Although the documentation says if you omit the Filename, the current folder will be used. I tried with Excel 2013 but it saves to My Document folder, so I use Application.ActiveWorkbook.FullName to make sure the file really saves to the current folder.

The third thing is FileFormat. I choose xlOpenXMLWorkbookMacroEnabled (xlsm) to ensure the Macro is saved successfully.

The below procedure uses SaveAs Method to add password (yourpassword) and then overwrite the existing xlsm workbook.

Sub add_password()
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs Filename:=Application.ActiveWorkbook.FullName, FileFormat:=xlOpenXMLWorkbookMacroEnabled, Password:="yourpassword"
'OR using specific path
    'ActiveWorkbook.SaveAs "C:\folderA\test.xlsx", Password:="yourpassword"
    Application.DisplayAlerts = True End Sub

Outbound References

https://msdn.microsoft.com/en-us/library/office/ff841185.aspx

This Excel VBA tutorial explains how to use VBA to protect worksheet with password.

You may also want to read:

Excel VBA hide worksheet with password (xlVeryHidden)

Excel VBA Add password to Excel workbook in folder

Protect VBA code with password

Excel VBA copy contents of protected worksheet

Different kinds of workbook protection

There are several kinds of protection in Excel.

1. Add password to workbook. Users are asked to open the Excel file with password.
2. Protect workbook with password. Protect the structure of all worksheets (do not allow insert, delete, rename, move, copy, hide or unhide worksheets)
   
3. Hide worksheet with password. Hide the worksheet and add a password to unhide the worksheet.
4. Protect worksheet (cells) with password. Restrict users to edit the cell

Lock Cell and Unlock Cell

Before doing any worksheet Protection, the first thing to do is to ensure the Cell you want to protect is locked.

Right click on a Cell > Format Cells > Protection > check Locked

To lock a Cell in VBA

Range("A1").Locked = TRUE

To unlock a Cell in VBA

Range("A1").Locked = FALSE

Similarly, Hidden can also be enabled in VBA

Range("A1").FormulaHidden = TRUE

Protect worksheet

Now the next step is to do the actual protection using Worksheet.Protect Method.

Syntax of Worksheet.Protect

Worksheet.Protect(Password, DrawingObjects, Contents, Scenarios, UserInterfaceOnly, AllowFormattingCells, AllowFormattingColumns, AllowFormattingRows, AllowInsertingColumns, AllowInsertingRows, AllowInsertingHyperlinks, AllowDeletingColumns, AllowDeletingRows, AllowSorting, AllowFiltering, AllowUsingPivotTables)

The simplest way to set password is to leave everything default and just set the password.

Sub proct()
    ActiveSheet.Protect (Password = "abc123")
End Sub

To protect all worksheets in workbook

Sub proctAll()
    For Each sht In ActiveWorkbook.Sheets
       sht.Protect (Password = "abc123")
    Next
End Sub

Since everyone can press ALT+F11 to see your password, you should lock the project view of VBE.

Press ALT+F11 > Navigate to Tools > VBAProject Properties > Protection > check the box “Lock project for viewing” > enter password (close the workbook and reopen to take effect)

Unprotect worksheet

Unprotect is more simple, unprotect Method has only one optional argument – the password.

Sub unproct()
    ActiveSheet.Unprotect (Password = "abc123")
End Sub

If the sheet or workbook isn’t protected with a password, this argument is ignored.

If you omit this argument for a sheet that’s protected with a password, you’ll be prompted for the password.

If you omit this argument for a workbook that’s protected with a password, the method fails.

If you are trying to modify a protected worksheet, you should run Macro to unprotect > modify > protect

Unprotect worksheet > do something > protect worksheet

Because you cannot manipulate a worksheet while it is protected, when you write a Macro for a protected worksheet, it is best to unprotect a protected worksheet, then run a Macro, finally protect it  back.

Sub proct()
    pwd = "abc123"
    Sheets("Sheet1").Unprotect (Password = pwd)
    'Do something
    Sheets("Sheet1").Protect (Password = pwd)
End Sub