Learn Excel VBA By Index (A)

Activate event

The example below shows a simple procedure that is executed whenever a particular sheet is activated, and display the name of the active worksheet in a message box. The code is enter in the Worksheet_Activate () module.

Private Sub Worksheet_Activate()

MsgBox “You are viewing “ & ActiveSheet.Name

End Sub



ActiveSheet

If you want to refer to the currently selected worksheet in a workbook, you can do so by using the ActiveSheet property. For example, the subroutine below will enter the value 100 into cell A1 in Worksheets(2) as the current selected worksheet.

Sub ActiveSheetDemo ( )

ActiveSheet.Cells(1,1).Value = 100

End Sub



Add comments to a Cell

In order to add comment to a cell, you can use the AddComment method with the Range object. The macro below will show you how.

Sub AddCommentDemo ( )

Range("A1").AddComment "Prevent Global Warming"

End Sub

Here the word "Prevent Global Warming" is added as a comment for cell A1. To display this comment all the time insert the following code..

Range("A1").Comment.Visible = True



Add Method

Add a workbook and input a value in Range A1 of workbook created.

Sub AddWorkbook ()

Workbooks.Add
intWorkbooksCount = Workbooks.Count

Set ReportWorkbook = Workbooks(intWorkbooksCount)
Set sht1 = ReportWorkbook.Worksheets(1)

sht1.Cells(1, 1) = "Welcome"

End Sub



Adding Items to a ComboBox and ListBox

The code below show how to populate a combobox or a listbox .

ComboBox1.AddItem "Boston"
ComboBox1.AddItem "New York"



Address

To specify a range reference in a style we are familiar with, like A1 or E5. The Excel VBA procedure below will find a cell that has formula content and will display the address of this cell in a message box.

Sub AddressDemo ()

Set MyRange = Range("A1:Z1000")

For Each cell in MyRange

If cell.HasFormula = True Then

MsgBox cell.Address

Exit For

Next cell

End Sub



And Operator

Perform a logical conjunction on two expression. In this case, two expression has to be satisfied in order for the macro to continuerunning. The macro below show how:

Sub AndDemo ()

x = 1

y =2

If x = 1 and y = 2 then

z = x * y

MsgBox (z)

End if

End Sub



Areas Collection

The Areas collection contains a few block of cells within a specific selection i.e. multiple ranges to create one multi-range. Let's look at an example...

Sub AreasDemo()

Dim rng1 As Range, rng2 As Range, rng3 As Range, myRange As Range
Dim AreasInMyRange As Integer

Set rng1 = Range("A1:B10")
Set rng2 = Range("D1:D10")
Set rng3 = Range("G1:G10")
Set myRange = Union(rng1, rng2, rng3)

AreasInMyRange = myRange.Areas.Count

End Sub

The above macro use the Union function to combine 3 non-contiguous ranges. AreasInMyRange will return 3 as we use the Areas.Count method to calculate the 3 areas in myRange.



Autofill Method

This method enable user to autofill a series of data on the specified range of cells. Look at the example below.

Sub AutofillDemo ( )

Range("A1:B1").Autofill Range("A1:J1")

End Sub

The above will autofills cells C1 through J1 using the source data in cells A1 and B1. If A1 contains 1 and B1 contains 2, then this code will fill the destination cells with consecutive integers starting at 3 (in cell C1), 4 (in cell D1) and so on