Tuesday 19 February 2013

Important concepts in VBA Macros



Essential concepts to remember

In this section, I note some additional concepts that are essential for would-be VBA gurus.

  • Objects have unique properties and methods.

Each object has its own set of properties and methods. Some objects, however, share
some properties (for example,
Name) and some methods (such as Delete).
  • You can manipulate objects without selecting them.
This might be contrary to how you normally think about manipulating objects in Excel.
The fact is that it’s usually more efficient to perform actions on objects without selecting
them first. When you record a macro, Excel generally selects the object first. This is
not necessary and may actually make your macro run more slowly.
  • It’s important that you understand the concept of collections.
Most of the time, you refer to an object indirectly by referring to the collection that it’s
in. For example, to access a
Workbook object named Myfile, reference the
Workbooks
collection as follows:Workbooks(“Myfile.xlsx”)

This reference returns an object, which is the workbook with which you are concerned.

  • Properties can return a reference to another object.

For example, in the following statement, the
Font property returns a Font object contained
in a
Range object. Bold is a property of the Font object, not the Range object.
Range(“A1”).Font.Bold = True

  • There can be many different ways to refer to the same object.
Assume that you have a workbook named

Sales, and it’s the only workbook open.
Then assume that this workbook has one worksheet, named
Summary. You can refer to
the sheet in any of the following ways:


Workbooks(“Sales.xlsx”).Worksheets(“Summary”)
Workbooks(1).Worksheets(1)
Workbooks(1).Sheets(1)
Application.ActiveWorkbook.ActiveSheet
ActiveWorkbook.ActiveSheet
ActiveSheet

The method that you use is usually determined by how much you know about the workspace.
For example, if more than one workbook is open, the second and third methods
are not reliable. If you want to work with the active sheet (whatever it may be), any of
the last three methods would work. To be absolutely sure that you’re referring to a specific
sheet on a specific workbook, the first method is your best choice.

VBA code to close all Excel workbooks except the active workbook


VBA code to close all Excel workbooks except the active workbook:

If you had worked in Excel with several workbooks at a time, you might have came across a question like this.

How to close all other workbooks except the active workbook in one shot instead of going to each workbook and close ?

Microsoft Excel Does'nt provide a direct way to close several workbooks at a time.

But we can use VBA codes to accomplish this task.

How :

  1. Select the workbook you wish to retain
  2. Go to VBE
  3. Add one Module
  4. Copy paste the below sub procedure to a module in your code
  5. Run the Macro Whenever you want to close other workbooks
  6. Remember to Save the file as MacroEnabled
Sub CloseAllSheetsExActive()
Dim WBs As Workbook
For Each WBs In Application.Workbooks
If Not WBs.Name = ThisWorkbook.Name Then WBs.Close (False)
Next WBs
End Sub


Note : Macro will close all the workbooks ignoring all the changes you have made without asking whether to save it or not. If you use TRUE instead of FALSE after close method, it will save all the changes without asking. If you don't use TRUE or FALSE, a dialog box will show to ask whether you wish to save or close without saving.

How to add formula to a worksheet range using VBA Codes

How to add formula to a worksheet range using VBA


Adding formulas to ranges is common task when you workaround with Excel VBA.

If you are good in entering formulas in Excel, you can become an expert in adding VBA formulas too.

Eg :

We have some values in a range A1:A20 and We have some values in B1:B20 also. We need to get the difference of all values in B column with corresponding Cell in Column A, in the Column C.

In Excel we will enter a formula = B1-A1 in the cell C1 and do a fill down or a copy paste till C20. It's the simplest formula when we work in Excel.

But How to get the same formulas in C1:C20 using VBA codes ?

You can use two ways.

1. Enter Activesheet.Range("C1:C20").Value = "RC[-1] + RC[-2]" in your code.
2. Enter Activesheet.Range("C1:C20").FormulaR1C1= "RC[-1] + RC[-2]" in your code.

Both codes with do the same task. Note, VBA is using R1C1 reference in entering the formulas to Excel. It's better to Get a basic idea about how R1C1 reference style works, before you start working with formulas in VBA.

Solution: If you have any doubt in VBA, first try to record the task which you want to automate and see the codes. Same way, you record the task of entering the above formulas using macro recorder. See the codes.