A

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 (back to top)

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  (back to top)

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 (back to top)

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 (back to top)

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

B

Built -in Functions (back to top)

This code will use the Excel built-in function, Average, Max, Min and Standard Deviation.

Option Base 1

Sub BuiltInFunctionDemo ()

Dim MyArray(100) As Integer

For x = 1 to 100

MyArray(x) = Rnd

Next x

average = Application.Average(MyArray)

max = Application.Max(MyArray)

min = Application.Min(MyArray)

std = Application.Stdev(MyArray)

End Sub

C

Calling a Worksheet Function from Visual Basic (back to top)

In Visual Basic, the Microsoft Excel worksheet functions are available through the WorksheetFunction object. The following procedure uses the Max worksheet function to determine the largest value in a range of cells…

Sub FunctionDemo()

    Set myRange = Worksheets("Sheet1").Range("A1:B10")

    ans = Application.WorksheetFunction.Max(myRange)

    MsgBox ans

End Sub

The range A1:B10 on Sheet1 is Set as myRange. Another variable, ans, is assigned the result of applying the Max function to myRange which then show up in a message box when you execute the procedure.

Cells Method (back to top)

To enter the the value 100 into Range("B2"), the code below will do the trick...

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

We can also reference a specific cell in a Range object by using the code below...Here the value 100 is enter into Range("C4")

Set MyRange = Range("C3:C10")

MyRange.Cells(2).Value = 100

Change text to proper case

To change text in a selected range to proper case use the code below.

Sub ProperCaseDemo()
Dim cell As Range
For Each cell In Selection.Cells
If cell.HasFormula = False Then
cell = strconv(cell.text,vbProperCase)
End If
Next
End Sub

Change the name of a Worksheet

You can change the name of a worksheet by using the Name property of the Worksheet object. The macro below will show you how.

Sub ChangeNameDemo ( )

Dim wsName As String

wsName = InputBox("Enter a new worksheet name")

ActiveSheet.Name = wsName

End Sub

Clear method

This method enables you to clear the entire values, formulas and formatting from a range of cells. The procedure below use different Clear method..

Sub ClearDemo ( )

Range("A101:E200").ClearContents       'will clear the cell values and formulas from a range of cells except the formatting

Range("A201:E300").ClearFormats        'to clear the formatting

Range("A1:E100").Clear                          'will clear the entire values, formulas and formatting from a range of cells

End Sub

Copying a range (back to top)

This is how you write a simple copy and paste operation...

Sub CopyDemo ()

ActiveSheet.Range("A1:A3"). Copy (ActiveSheet.Range("B1:B3"))

End Sub

Columns properties

With this property, you can select the entire column as the Range object.

RangeSelected = Columns(3)

Here column C is selected. You can also use the Columns property to determine a specific Column within a Range. For example, look at the 

procedure below.

Sub ColumnDemo ( )

Set MyRange = Range(“B1:E100”).

MyRange.Columns(2).Select

End Sub

Here you have selected Range(“C1:C100”) as it is the second column within B1:E100.

Comparing two strings (back to top)

The Excel built-in function StrComp is use to compare whether two strings are alike. Let me show you how with the macro below.

Sub CompareString ( )

aStr = ActiveSheet.Range("A1").Value

bStr = ActiveSheet.Range("A2").Value

If StrComp(aStr,bStr) = 0 Then

MsgBox "They match"

Else

MsgBox "They are not the same"

End If

End Sub

Create Chart Sheet

To create a chart sheet, we can use the Add method of the Charts collection to create and add a new chart sheet to the workbook. The

macro below will do the trick.

Sub CreateChartDemo ( )

End Sub

Current Cell Content (back to top)

If you want to  know what the cell contains ie dates, text or formulas before a block of code is executed, you can use the code below. Replace this with the code you want to execute.

Sub ContentDemo()

If Application.IsText(ActiveCell) = True Then
MsgBox "Text"                                                  'block of code to be executed

          
ElseIf ActiveCell = "" Then
MsgBox "Blank cell"                                          'block of code to be executed

ElseIf ActiveCell.HasFormula Then
MsgBox "Formula"                                             'block of code to be executed

ElseIf IsDate(ActiveCell.Value) = True Then
MsgBox "Date"                                                  'block of code to be executed


End If

End Sub

 

Customize background color of cell

You can customize the background of cell and range of cells by using the ColorIndex property with the Interior object.

Sub ColorIndexDemo ( )

Cells(1,1).Interior.ColorIndex = 3      'set to red

Cells(1,2).Interior.ColorIndex = 5      'set to blue

Columns(3).Interior.ColorIndex = 1   'set to black

End Sub

 

Custom Functions (back to top)

I bet you're familiar with Excel built-in function like Sum, Average, Max, PMT etc. If you feel that's not enough then you can create custom functions by using Excel VBA. Let's create our own cubic function.

Public Function Cubic (num)

Cubic = num ^ 3

End Function

D

Disable the Ctrl + Break and Esc key (back to top)

In order for  you to prevent user to stop a macro before it finish running by pressing the Ctrl + Break and Esc key, just insert the code below at

the top of your procedure...

Application.EnableCancelKey = False

DisplayFullScreen

The macro below show you how to display fullscreen using Excel VBA

Application.DisplayFullScreen = True

To exit full screen using VBA then

Application.DisplayFullScreen = False

Do Until loop.

When you use the Do Until loop, the code you enter between the Do and Loop will execute until the specified condition is met.

Sub DoDemo ( )

Dim x As Integer

x = 1

Do Until IsEmpty(Cells(x,1))

Cells(x,1).Font.Bold = True

x = x + 1

Loop

End Sub

The macro will execute until the cell in Column A is empty.

E

End Method  (back to top)

You can use the End of the Range object to select a particular. See this example...

Sub EndDemo ()

Range(ActiveCell, ActiveCell.End(xlDown)).Select        'select downward the activecell to last non-empty cell

Range(ActiveCell, ActiveCell.End(xlUp)).Select             'select upward the activecell to last non-empty cell

Range(ActiveCell, ActiveCell.End(xlToLeft)).Select      'select to the left of  the activecell to last non-empty cell

Range(ActiveCell, ActiveCell.End(xlToRight)).Select    'select to the right of  the activecell to last non-empty cell

End Sub

Err

The default syntax of Excel VBA. This example demonstrates error handling by jumping to a label..

Sub ErrDemo ()

Dim x As Integer

On Error GoTo ErrorHandler

x = "abc"

Exit Sub

ErrorHandler:

e = Err.Number & " ...cannot assign integer value to x"

MsgBox (e)

End Sub

Exit Do (back 
      to top)

If you need to jump out from a Do loop, you can do this by using the Exit Do statement. The macro below will do the trick.

Sub ExitDoDemo ( )

x = 1

y = 100

Do While x < y 

 If x^2 > 100 Then Exit Do

x = x + 1

Loop

End Sub

The procedure will halt when x^2 is more than 100.

Exit Sub (back to top)

The Exit Sub statement is use as a point of exiting a subroutine with running the rest of any other statement in a procedure. The example below will display a message box to prompt the user whether to continue or not. If user choose yes, the subroutine will call the macro MyProcdure and will exit the current subroutine.

Sub ExitSubDemo ( )

msg = "Do you want to continue?"

answer = MsgBox(msg,vbYesNo)

If answer = vbYes Then

Call MyProcedure

Exit Sub

Else

MsgBox "Program will end now."

End if

End Sub

F

Fill Method

You can use the Fill method to fills a range of cells. Let's see how this is implemented below.

Sub FillDemo ( )

Range("A2:A10").FillUp            'this will fill up the value contain in cell A10 to all cell above until A2

Range("A2:A10").FillDown        'this will fill down the value contain in cell A2 to all cell below until A10

End Sub

Find a File (back 
      to top)

In order to search for a file in your system, you can use the FileSearch object. The macro below will do the trick.

Sub FileSearchDemo ( )  

Dim PathInfo As String

With Application.FileSearch

   .NewSearch

.FileName = “Computer.xls”

End With

End Sub

You must make that such file exist, else Excel VBA will return an error message.

Font Object

Properties of this object includes Bold, Size, Italic, Underline and etc. To set the font properties you can enter the code as follow…

Sub FontDemo ( )

Set SelFont = ActiveSheet.Range(“A1”).Font

SelFont.Font.Size = 12

SelFont.Font.Bold = True

End Sub

The above procedure enables you to set the font of cell A1 to size 12 and bold.

For Next Loop (back to top)

Use this syntax when you want to execute for a determine number of time.

Sub ForNextDemo1 ()

For x = 1 to 10

y = x + 1

Next x

MsgBox (y)

End Sub

Here you'll get y = 11

Look at another example...The code below will for cell value that are positive to bold.

Sub ForNextDemo2 ()

Set MyRange = Range("A1:A100")

y = MyRange.Rows.Count   ' y = 100

For x = 1 to y

If MyRange.Cells(x).Value > 0 Then

MyRange.Cells(x).Font.Bold = True

End If

Next x

End Sub

Or you can use the For Each - Next syntax to do the same thing.

Sub ForNextDemo3 ()

Set MyRange = Range("A1:A100")

For Each cell in MyRange

If cell.Value > 0 Then

cell.Font.Bold = True

End if

Next cell

End Sub

G

GoTo (back to top)

When the GoTo syntax is use, you can make Excel VBA to jump to a label and execute the line of code under the label like the On Error GoTo example I've demonstrate above. Let's look at another example...

Sub GotoDemo ()

x = Int(Rnd() * (1 - 10) + 10)       'a random number between 1 and 10

If x < 5 Then

GoTo Less

Else

GoTo More

End If

Exit Sub

Less:

MsgBox x & " is less than 5"

Exit Sub

More:

MsgBox x & " is more than 5"

End Sub

You can also specify a Excel macro to go to a specific range by using the GoTo method. For example, in a worksheet you have name a Range ("Credit Card"). To select this Range use the following statement...

Sub GoToDemo2 ()
Application.Goto Reference:="Credit Card"    'equivalent to  Range("Credit Card").Select
End Sub

H

Height (back to top)

To change the height userform, you can use the Height property. Look at the example below...

Private Sub UserForm_Initialize()

UserForm1.Height = 100

End Sub
 

When UserForm1 initialize, it height will be set to 100 points.

Hiding Columns

To hide a range of column the macro below will do the trick.

Sub HideColDemo ( )

StartCol = 3

EndCol = 6

For n = StartCol to EndCol

Columns(n).Hidden = True

Next n

End Sub

With the above procedure column C to F will be hidden

Hiding Rows

To hide a range of rows the macro below will do the trick.

Sub HideRowDemo ( )

StartRow = 3

EndRow = 6

For n = StartRow to EndRow

Rows(n).Hidden = True

Next n

End Sub

With the above procedure Row 3 to 6 will be hidden

Hiding Sheets (back to top)

You can use the following code to enable you to hide your worksheet from users. Name a worksheet “Pharmacy”.

Sub HideSheetDemo()

Worksheets(“Pharmacy).Visible = xlSheetVeryHidden

End Sub

If you hide your sheets this way, users will not be able to unhide them using the Excel menus. Only using VB codes will be able to display the sheets again.

Hide UserForm

However, if you want to hide a userform different statement is required. The macro below will do the trick. If you have rename a userform to “Account” then the code is

Sub HideUserformDemo ()

Account.Hide

Range(“A1”).Value = Account.TextBox1.Text

End Sub

You still can access the value store in the userform programmatically. For example, you still can retrieve value on  TextBox1 on the form.

Hyperlinks In Excel (back to top)

You may notice that Excel automatically convert URL that you enter into hyperlink. If you don't like this then you can delete the hyperlink

by entering the code below.

Sub DeleteHyperLinksDemo()

Sheet2.Hyperlinks.Delete

End Sub

Here all the hyperlinks in sheet 2 will be deleted

I

If Then statement

This statement checks to see if a specific condition is true, and if true then it will execute all the code between the Then keyword and the End If statement. Look at the example below...

Sub IfThenDemo ( )

Dim x As Integer, MySum As Integer

For x = 1 to 100

If IsNumeric(Range("A" & x)) = True Then

MySum = MySum + Range("A" & x).Value

End If

Next x

MsgBox MySum

End Sub

The above procedure will check whether the value in column A is numerical. If it is numerical, then it will add the cell value into MySum.

Input Box  (back to top)

Input Box is used to prompt user for input. The example below will ask the use to enter the year he is born and then a message box will display his age.

Sub InputBoxDemo()

MyInput = InputBox("Which year are you born?")

Age = 2007 - MyInput

MsgBox "You are " & Age &  “ years old.”

End Sub

Let's look at another example...

You can use the Input Box function to prompt specific user input during the execution of a subroutine. Look at the sample code below.

Sub InputBoxDemo2 ( )

UserInput = InputBox("Type something", ,"Enter")

End Sub

A input box will be displayed asking the user to type something. The title of this input box is "Enter"

Inserting value into an existing cell that contain value

Here you can use the Insert method. For example, cell A1 contain the word "Credit". You can insert another word "Card" by using the

subroutine below.

Sub InsertWordDemo ( )

Range("A1").Characters(8,4).Inserts("Card")

End Sub

You insert the word "Card" into cell A1 starting at character 8 with 4 character i.e "Card". As a result, cell A1 now contain the word "Credit Card".

Intersect (back to top)

You can specify the location where two ranges intersect by leaving a space between the two range definitions. For example, Range(“A2:F5 D2:G6”) specifies a range where the range of of cells A2 to F5 intersect with the range of cells D2 to G6.

Sub IntersectDemo ( )

Range(“A2:F5 D2:G6”).Select

Selection.Name = "Medicine"

End Sub

Range("D2:F5") is the intersection, will be selected and is name "Medicine".

Invoke another macro on a change event

Automatically invoke a macro after entering data into a particular column. The example below will call the macro myProcedure when there is changes in Column 3. The code is insert into the Worksheet_Change () module.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

 If Target.Column = 3 Then myProcedure

End Sub

Inserting a Worksheet Function into a Cell (back to top)

To insert a worksheet function into a cell, you specify the function as the value of the Formula property of the corresponding Range object. The following example insert the AVERAGE worksheet function into the property of range A1 on Sheet1 in the active workbook.

Sub InsertFormulaDemo()

    Worksheets("Sheet1").Range("A1 ").Formula = "=AVERAGE( )"

End Sub

If / Then / Else