To joint two string, you can use the following example...

Sub JointStringsDemo ( )

aStr = "fish"

bStr = "miles'

dStr = Left(aStr,2)

eStr = Right(bStr,3)

MsgBox dStr & eStr

End Sub

A message will display the word "files" in this case as the first two alphabets of "fish" is joint with the last three alphabets of "miles"

 

Jump to a specific location   You can jump to a specified location within your procedure by using the GoTo statement.   Sub GotoDemo ( ) Dim x As Integer   x = Range(“A1”).Value   If x < 100 then Goto addValue Else Goto decreaseValue End If   Exit Sub addValue: x = x + 100 Range(“A1”).Value = x   Exit Sub decreaseValue: x = 100 Range(“A1”).Value = x   End Sub   The above procedure will jump to the line addValue when x is less than 100 and jump to the line decreaseValue if x is more than 100.

Killing a file is equivalent to deleting file. It is easy to implement However the files you are killing must not be in used. For example you want to delete the file “Demo.xls” in your C drive stored in folder My Documents

Sub Killfile()
Dim MyFile As String   

On Error Resume Next 

MyFile = "c:\My Document\Demo.xls"    

kill MyFile

End Sub

L

Left Function (back to top)

This example below uses the Left function to return a specified number of characters from the left side of a string.

Sub LeftFunctionDemo ()

Dim aStr, bStr

aStr = "Hello World"    ' Define string.
bStr = Left(aStr, 1)    ' Returns "H".
bStr = Left(aStr, 8)    ' Returns "Hello Wo".
bStr = Left(aStr, 11)    ' Returns "Hello World".

End Sub

List Box (back to top)

This is how you populate a ListBox with items.Insert a Userform in your VBE. Add a ListBox on the UserForm. Activate the Userform_Initialize code window and enter the statement below...

Private Sub UserForm_Initialize()

'Fill the list box
With ListBox1

.AddItem "Credit Cards"
.AddItem "Personal Loan"
.AddItem "Mortgage"
.AddItem "Hire Purchase"
.AddItem "Car Loan"
.AddItem "Forex Margin"
.AddItem "Share Margin"

End With

End Sub

To determine which item you've selected then enter the following code in the ListBox_Change event code window..

Private Sub ListBox1_Change()

Msg = ListBox1.Value
MsgBox "Msg"

End Sub

Lower Case (back to top)

This example show you how to change text in a selected range to lower case .

Sub ChangeLowerCaseDemo ()

Set myRange = Selection.Cells

For Each cell In myRange

If cell.HasFormula = False And IsEmpty(cell) = False Then

cell = LCase(cell)

End If

Next

End Sub

Looping through a range of cells

Sometimes you  need to run the same block of statements on each cell in a range of cells. To do this, you must combine a looping statement and one or more methods to identify each cell, one at a time, and run the operation. Let use the statement For Each / next to do this.

Sub LoopDemo ( )

Dim myRange as Range

Set myRange = Range(“A1:A100”)

For Each cell in myRange

If Not cell.HasFormula Then

cell.Value = 100

Next cell

The above procedure will loop through every cell in Range(“A1:A100”). If the activecell contain no formula, the procedure will enter the value into 100 it.

M

Make Excel speak (back to top)

The macro below will make Excel speak ...

In Cell "C1" type text, you want Excel to speak and than run the following macro

Sub SpeakCellDemo()

Cells(1, 3).Speak

End Sub

Menus Collection (back to top)

Let me show you how to add a new menu to a menu bar. The macro below will do the trick...

Sub AddMenuDemo()
MenuBars(xlWorksheet).Menus.Add "&MyMenu", "Help"
Set bmenu = MenuBars(xlWorksheet).Menus("MyMenu")

With bmenu.MenuItems
.Add Caption:="&MyProcedure", _
OnAction:="MyProcedure"
End With

End Sub

This will add a new menu "MyMenu" to the Menu bar before the Help menu. Then a sub menu "GoToDemo" is created. When you select "GoToDemo" it will call the procedure, "MyProcedure".

Message Box

To be used to display message to the user and prompt action from the user as well.

Sub MessageBoxDemo ()

Message = "Do you want to continue? "

Ans = MsgBox(Message, vbYesNo, "Proceed?")

If Ans = vbYes Then MyProcedure

End Sub

The above macro ask the user whether to continue with code execution. If user select yes then the macro will call the procedure MyProcedure, else it will end the program.

Month Function (back to top)

This function is use to obtain the month value from a specified date...

Sub MonthDemo

MyDate = #October 15, 2007#

MyMonth = Month(MyDate)  'result is 10

End Sub

Move Method

Use this statement when you want to move around the worksheets in your workbook. The macro below will move Sheet 2 after Sheet 3

Sub MoveSheetDemo ( )

Worksheets("Sheet2").Move _

    after:=Worksheets("Sheet3")

End Sub

To move it back then enter this line of code

Worksheets("Sheet2").Move _

    before:=Worksheets("Sheet3")

N

Name (back to top)

The name properties is very easy to implement. Add a worksheet and change the name of a worksheet programmatically with the following code...

Sub NameDemo ()

Worksheets.Add.Name = "Personal"

End Sub

NOW Function

This Excel VBA default function to show the Date and time. You can assign the result of this function to a variable, worksheet cell or a message box

like the one below...

Sub NowDemo()

MsgBox "The Date and Time is " & Now

End Sub

Not

The Not statement is used when you to toggle a certain properties for example...

Sub NotDemo()

ActiveWindow.DisplayFormulaBar = Not ActiveWindow.DisplayFormulaBar  
ActiveWindow.DisplayGridlines = Not ActiveWindow.DisplayGridlines

End Sub

Nothing (back to top)

The Nothing keyword is used to free an object variable. By doing so you free up the memory used to store the object in the first place.

Sub NothingDemo ( )

Set MyRange = Range(“A1:A100”)

Set MyRange = Nothing

End Sub

First you allocated the range A1:A100 as MyRange to your system memory. After that you free the object from the system memory.

Numeric Formatting

You can custom format a numeric expression of your choice by using the FormatNumber, FormatCurrency and FormatPercentage functions. 

The procedure below show you how to do this.

Sub FormatNumberDemo ( )

x = 50/100

NumNormal = FormatNumber(x,2)

NumPercent = FormatPercent(x,2)

NumCurrency = FormatCurrency(x,2)

MsgBox NumNormal &  vbCrLf  & NumPercent &  vbCrLf  & NumCurrency

End Sub

O

OffSet (back to top)

The OffSet method enable you to define a range that is a specific offset from another range with the offset being the distance, in row and column.

Sub OffSetDemo ()

Range("C2").Select

ActiveCell.OffSet(1,1).Value = 100                              'This will enter the value 100 into cell D3

ActiveCell.OffSet(-1,-1).Value = 200                           'This will enter the value 200 into cell B1

Range(ActiveCell, ActiveCell.OffSet(2,2)).Select      'C2:E4 is selected

End Sub

OnKey Method

The OnKey method enable you to assign a specific key to a certain procedure. The following example demonstrate this...

Sub OnKeyDeom ( )

Application.OnKey "^p", "MyProcedure"

End Sub

When you press Ctrl + p, Excel VBA will execute the subroutine MyProcedure.

OnTime (back to top)

You can create a macro that execute at a specific time by using this OnTime event. With the example below, the code will execute "MyProcedure" after 10 seconds.

Private Sub Worksheet_Activate()

Application.OnTime Now + TimeValue("00:00:10"), "Module1.MyProcedure"

End Sub

Open Method

The Open method is very useful whenever you need to run a specify macro when the workbook is open. The procedure below will run the macro Test

in module 1 whenever the workbook is open.

Private Sub Workbook_Open()

    Module1.Test

End Sub

Opening a document (back to top)

To open document with Excel VBA, the following example using the Open Method will do the trick…

Workbooks.Open Filename:="C:\My Documents\Bet.doc"

This statement above opens the file "C:\My Documents\Bet.doc" for editing. To open a Excel file as read only, enter the code below...

Workbooks.Open FileName:="Health.xls", ReadOnly:=True

P

Password (back to top)

The procedure below allows you to password protect a specific worksheet so that other user cannot modify it.

Sub PasswordDemo()

Worksheets("Health").Protect Password:="xlpert", AllowFormattingCells:=True

End Sub

User will need to enter the correct password to edit the Worksheet "Health".

Perform mathematical calculation with Excel VBA

You can perform many types of mathematical calculations with Excel VBA

Sub CalcDemo ( )

Dim x As Integer, y As Integer, z As Single

x = 3

y = 4

z = x + y       'addition - here z = 7

z = x * y       'multiplication  - here z = 12

z = y - x        'subtraction - here z = 1

z = y / z         'division

z = y^x         'exponential, here z = 64

End Sub

Placing formula in cell

To insert a formula in a cell, the formula must be expressed in A1-style notation, and must include a leading equal sign. Look at the example below...

Sub FormulaDemo ( )

Protecting Worksheets (back to top)

The procedure below demonstrate how you protect all the worksheets in a workbook. A For/Next statement is used to loop through all the worksheets...

Sub ProtectSheetDemo()
                    
For i = 1 To Application.Sheets.Count  'Determine how many worksheets

Worksheets(i).Select
ActiveSheet.Protect

Next i

End Sub

And to unprotect simply...

Sub UnprotectSheetDemo()
                    
For i = 1 To Application.Sheets.Count

Worksheets(i).Select
ActiveSheet.Unprotect

Next i

End Sub

Protecting A Chart

Excel VBA enable user to protect a chart to prevent errors caused by someone attempting to change a protected chart. You can also password protect them. Let me show you an example.

Sub ProtectChartDemo ( )

Charts(1).Protect Password = "Sweet Home",  DrawingObjects:=False

End Sub

This will prevent anybody from modifying Chart 1 until the password is enter correctly. To unprotect insert the following code.

Sub UnProtectChart()
Password = "Sweet Home"
Charts(1).Unprotect Password
End Sub

Q

Quit

When you close all workbooks, you may notice that Excel is still running. If you want to close Excel altogether, then you can use the Quit method with the Application object.

Sub QuitDemo ()

Application.Quit

End Sub

R

Random Number (back to top)

The procedure below will generate a random with the upper and lower value determine by the user, something like the Excel built-in function RANDBETWEEN( ). The code in this procedure takes this format : Int ((upperbound - lowerbound +1) * Rnd + lowerbound). Where the Upperbound is the largest number random number to be generated and Lowerbound is the lowest.

Sub RandDemo ( )

UpperValue = 10

LowerValue = 0

r = Int((UpperValue - LowerValue + 1) * Rnd + LowerValue)

Range("A1").Value = r

End Sub

Here a random number between 0 to 10 will be generated.

Range (back to top)

You can use the Range object represents a group of one or more contiguous cells in an Excel worksheet. It is extremely useful, as it allows us to manipulate the properties of an individual cell or collection of cells in a worksheet. You will probably find yourself using the Range object in every program you write using VBA for the Excel application.

Consider the following code examples that use properties of the Range object.

Range(“B1”).Value=”Column B”

Range(“B1:G1”).Columns.AutoFit

Range(“B1:C1”, “E1:F1”).Font.Bold = True

Let's combine 2 ranges together by using the Union method. The code below will combine 2 ranges into one and will set the font size to 8 if the cell value is a number...

Sub RangeDemo()
Dim rng1 As Range, rng2 As Range, myRange As Range, cell As Range

Worksheets(1).Activate

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

For Each cell In myRange
If IsNumeric(cell) = True Then
cell.Font.Size = 8
End If
Next cell

End Sub
 

Rectangle (back to top)

The following statement adds a rectangle to the Worksheet named "Personal"...

Sub RectangleDemo ()

WorkSheets("Personal").Rectangles.Add 150, 50, 50, 100

End Sub

Redimension An Array

You can redimension an array using the ReDim Statement. Look at the example below…

Sub RedimDemo ( )

Dim Arr() As Integer

Set MyRange = Selection

x = MyRange.Rows.Count

ReDim Arr(x) As Integer

End Sub

The procedure above will determine the number of rows in the selected area and then redimension it accordingly to the Arr()

Replace (back to top)

Sometimes we need to replace certain words or a number that we have entered on a worksheet. It may be troublesome to look for the word or number one by one. You can actually write an Excel VBA procedure to do this and the macro below will do the trick.

Sub ReplaceDemo ()

Dim sht As Worksheet

For Each Sht In Worksheets

    sht.Cells.Replace What:="Home Loan", _ 

        Replacement:="Mortgage", LookAt:=xlPart, MatchCase:=False

Next

End Sub

The procedure will loop through all the worksheets in the specify workbook, look for the word "Home Loan" and replace it with the word "Mortgage'

Resizing the Height of Rows (back to top)
     

You can specify the height of row/s with the Height property. Below is a procedure to set the height of a few rows.

Sub RowHeightDemo ( )

RowHeight = 15

For n = 1 to 10

ActiveSheet.Rows(n).Height = RowHeight

Next n

End Sub

Here the height of row 1 to 10 is set to 15 points.

Resume execution if an Error is encountered

You can ask Excel VBA to continue running even if an error is encounter. Just insert the code at the top of your subroutine like below.

Sub ResumeOnError ( )

Dim x As Integer

On Error Resume Next

x = "abc"

End Sub

Here x is assigned as an integer value. As "abc" is a string valuable, this subroutine will encounter an error. However since we have enter the syntax, 

"On Error Resume Next" at the top, the subroutine will continue running.

Retrieve Current Date And Time (back to top)

You can assign the results of the Data or Time function to another variable, a worksheet cell, or another function. The following example assign it to a 

message box.

Sub NowDemo ( )

MsgBox(“Current Data and Time:” & Now())

End Sub

Or to a cell just enter this code…

Range(“C1”) = Now( )

Rows property 

With this property, you can select the entire row as the Range Object. Just enter the code below.

RangeSelected = Rows(5)

Here you have selected the entire row 5. You can also use the Rows property to determine a specific row within a Range. For example, look at the macro 

below.

Sub RowDemo ( )

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

MyRange.Rows(2).Select

End Sub

Here you have selected Range(“B2:E2”) as it is the second row within B1:E100.