http://www.xlpert.com/vba_index.htm
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 (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

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



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


Calculate a specific cell

Excel will calculate that worksheet. When applied to a specific range, as in:

Worksheets(1).Rows(2).Calculate

With the above syntax, Excel VBA will calculate only row 2 in worksheet 1. If you want to calculate all the formula in worksheet 1 then insert

the following code...

Worksheets(1).Calculate



Your Ad Here
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 ( )

Dim ch As Chart

Set ch = ThisWorkbook.charts.Add()

ch.Name = "Account"

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

Delete (back to top)

To delete a worksheet, use the following code..Worksheet(2) will be deleted

Sub DeleteSheetDemo1 ()

Worksheets(2).Delete

End Sub

When you delete a sheet, Excel will display a message to warn user whether they want to delete the sheet or not. To disable this message, use the code below

Sub DeleteSheetDemo2 ()

Application.DisplayAlerts = False

Worksheets(2).Delete

End Sub

Your Ad Here



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.




Your Ad Here



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



Goto Reference cell

When you want to go to a specific cell or range, you can instruct Excel to do so by entering the code below...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Application.Goto Reference:=Worksheets("Loan").Range("B100"), _
Scroll:=True

End Sub

As the Worksheet ("Loan") is activated, cell B100 will be at the top right window.

Your Ad Here



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

You use the If-Then structure when you want to execute one or more statements conditionally. The optional Else clause, enable you to execute one or more statements if the condition you¡¯re testing is false. See the example below...

Sub IfThenElseDemo ()

If Time < 0.5 Then

MsgBox "Good Morning"

Else

MsgBox "Good Afternoon"

End Sub

When Time is less than 0.5, it mean that it is before 12 pm.



IsNumeric (back to top)

This syntax enable to check a specified varaibles whether it is a numerical data. The subroutine below will check each cell in range A1:A100 whether it is a numerical value. If yes then it will be added to the total sum.

Sub IsNumericDemo ( )

Total = 0

For Each cell in Range("A1:A100")

If IsNumeric(cell.Value) Then

Total = Total + cell.Value

End If

Next cell

MsgBox Total

End Sub
**************************
Joint two strings

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.






K
Killing A File

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


Label (back to top)

You can change the caption of a label during design time by typing the text you want to change on its properties window and programatically by using the code below...

Sub LabelDemo ()

UserForm1.Label1.Caption = "Home Loan'

UserForm.Label2.Caption = "Mortgage"

End Sub



Your Ad Here

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








Your Ad Here





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 ( )

Range("A1").Formula = "=Sum(A2:A3)"

Range("B2").Formula = "=Avearage(C1:C100)"

End Sub

You can also put the exact same formula in each cell, by using an array, as in:

Range("A1:C1").Formula = _

Array("=Sum(A2:A3)", "=Sum(A2:A3)", "=Sum(A2:A3)")



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



PrintArea Method (back to top)

You can set a print area for a worksheet with the PrintArea property. Let's look at an example...

Sub PrintAreaDemo ()

ActiveSheet.PageSetup.PrintArea = "$A$1 : $F$100" 'Range("A1:F100") to be printed

ActiveSheet.PrintOut

End Sub

Your Ad Here



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.
*********************
SaveAs

Use the Save or SaveAs statement when you want to save the currently selected Excel workbook. This is how you do it programatically...

Sub SaveAsDemo ()

Workbooks(1).SaveAs "Sports"

End Sub


Saving a Document as a Web Page
With Excel VBA, you can save a workbook, worksheet, chart, range, print area to a Web page. The procedure below use the SaveAs method to do so. It saves the active workbook as C:\My Documents\mortgage.htm.

Sub SaveAsWebpage ( )

ActiveWorkbook.SaveAs _

Filename:="C:\My Documents\mortgage.htm", _

FileFormat:=xlHTML



End Sub




Searching wildcard matches

To find cells that match more complicated search criteria, such as those involving wildcard matches, you can use a more manual approach, such as cycling through the cells in the range with a For Each loop and using the Like operator. For instance, the

following code searches for all cells in the range A1:C100 whose contents begin with an "D" and sets the font for these cells to bold (note the use of the evaluation operator to denote the range A1:C100):

Sub WildcardSearchDemo ( )

Dim cell As Range

For Each cell In Range("A1:C100")

If c.Value Like "D*" Then

c.Font.Bold = True

End If

Next cell

End Sub



Send Mail

When you want to email to someone the workbook, just enter the code below in the current workbook VBE module.

SendMailDemo ()

ActiveWorkbook.SendMail Recipients:="admin@xlpert.com"


End Sub


Select (back to top)

You can use the select method with a Worksheet, Range object to highlight your selection. Look at the example below...

Sub SelectDemo ()

Dim MyRange As Range

x = 10

y = 2

Worksheet(1).Select

Set MyRange = Range("A1:C100")

MyRange.Cells(x,y).Select 'here you've selected Cell B10

End Sub



Select Case (back to top)

You can create code to execute a specific block of code based upon the value of a statement using the Select Case syntax. Look at the example below...

Sub GradeDemo

Points = InputBox("How many points?")

Select Case Points

Case 1 to 40

MsgBox ("Failed")

Case 41 to 70

MsgBox ("Passed")

Case 71 to 90

MsgBox ("Good")

Case Else

MsgBox ("Very Good!")

End Select

End Sub

The above procedure will prompt user for the points score and will display a message box on the grade achieved.



Selecting non - contiguous multiple rows

We use the Union method when we want to select a few rows that are non-contiguous. The example below will show you how.

Sub SelectRowsDemo ( )

Dim rng As Range

Set rng = Union(Rows(1), Rows(3), Rows(7))

rng.Select

Selection.Font.Bold = True

End Sub

The above will select row 1.3 and 7 and set the font to bold.



ScreenUpdating

The Application.ScreenUpdating statement is very useful in speeding up your macro. All you need to do is to add this statement at the beginning of your macro...see below

Sub ScreenDemo ( )

Application.ScreenUpdating = False

'Block of codes to be executed

Application.ScreenUpdating = True

End Sub

At the end of your macro, you reverse the effect of the statement by setting it to True again.



SelectionChange Event (back to top)

If you want to add a value into a specified cell when the cell is click, the macro below will do the trick.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = Range("C10").Address Then _
Range("C10").Value = 100
End Sub

When you click on cell C10, the value 100 will be entered on it automatically.



Set Statement (back to top)



This statement assigns the range of cells to the specified Range object. The macro below show you how…



Sub SetDemo ( )



Dim MyRange As Range



Set MyRange = Range(“B1:C100”)



MyRange.Cells(1,2).Value = 100



End Sub



This will enter the value 100 into cell C1.



Setting replacement text properties

To do this enter the following code...

Sub TextDemo ( )

With Application.ReplaceFormat.Font

.Name = "Times New Roman"

.FontStyle = "Bold"

.Size = 10

End Sub

Here your Excel application text format is change accordingly to the above


Your Ad Here

Shapes

The following example adds an oval object to myWs, gives it the name Red Circle, and then sets its foreground color to red and line style.

Sub ShapeDemo ( )

Set myWs = Worksheets(1)



With myWs.Shapes.AddShape(msoShapeOval, _

100, 100, 50, 50)

.Name = "Red Circle"

.Fill.ForeColor.RGB = RGB(255, 0, 0)

.Line.DashStyle = msoLineDashDot

End With

End Sub

As Excel assigned each shape a default name when you add it to the Shapes collection, the macro above give the shape a more meaningful name by using the Name property.



SpecialCells Method (back to top)

A very useful statement when you want to select a cell that contain formula, numerical, non-empty etc. This is how you implement the statement...

Sub SpecialCellsDemo ()

On Error Resume Next

Selection.SpecialCells(xlFormulas, xlNumbers).Select

Selection.Font.Bold = True

End Sub

The above macro will change the font to bold if the cell contain formula.



Sort Worksheets By Name (back to top)

You can use Excel VBA the order of the worksheets in a workbook based upon the worksheet name. The macro below show you how...

Sub SortSheetsDemo ( )

Dim numSht As Integer

Dim shtName As String

numSht = Sheets.Count

For x = 1 to numSht

shtName = Sheets(x).Name

For y = x to numSht

If Sheets(y).Name < shtName Then

shtName = Sheets(y).Name

End If

Next

Sheets(shtName).Move Before:= Sheets(x)

Next

End Sub

The above macro will attempt to move the sheet with the smallest name to the front (left). For example, before sorting you have 3 sheets name Hire Purchase, Mortgage and Bet. After sorting the sheets become Bet, Mortgage and Hire Purchase.



Space function

Below is how you add space to a string.

Sub AddSpacesDemo()

Dim MyString As String

MyString = "Hello" & Space(8) & "World"

MsgBox MyString




End Sub



T

ThisWorkbook

This statement refer to the current active workbook. For example, you want to close the active workbook without saving any changes you can enter this code...

ThisWorkbookDemo ()

ThisWorkbook.Close SaveChanges:=False

End Sub



Timer Function (back to top)

This is also an Excel built-in function. The example below will demonstrate how to implement the timer function to find out how long a program has run.

Sub TimerDemo ()

Dim start as Date, finish As Date

start = Timer

For x = 1 to 1000

For y = 1 To 1000

'execute some code here

Next y

Next x

finish = Timer

MsgBox Format(finish - start, "000")

End Sub



Top of the screen (back to top)
The macro below enable the activecell to be at the top of the screen and to the left on the screen.

Sub TopLeftDemo()
ActiveCell.Select


With ActiveWindow
.ScrollColumn = ActiveCell.Column
.ScrollRow = ActiveCell.Row
End With


End Sub



TypeName (back to top)

The TypeName is a built-in Excel function. It return a string that describes its argument.

Sub TypeNameDemo ()

Set MyObject = Selection 'a range, chart, rectangle is selected

MsgBox (TypeName(MyOject)) 'will return the object type

MyString = "Travel"

MsgBox (TypeName(MyString))

End Sub

Let see how you can use this function to more practical purpose..

Sub TypeNameDemo2()


If TypeName(Selection) = "Rectangle" Then
ActiveSheet.Rectangles.Delete 'if selected object is a rectangle, then it will be deleted
ElseIf TypeName(Selection) = "Range" Then
Selection.Font.Bold = True
End If


End Sub



U

Unload (back to top)

When you want to unload a userform

Sub UnloadDemo ()

Unload UserFrom1

End Sub
Buy Jewellery @ Rs.1
India's No.1 Jewellery Auctions Site..Bid Now :)

Your Ad Here



Union

Use the Union method when you want to create a multiple area Range, where the block of cells are non - contiguous and which may not be connected. This example will combine 2 block of cells together and loop through each cell. If the cell contain formula then it will set its font color to red.

Sub UnionDemo ()

Dim MyUnion As Range

Set MyUnion = Union(Range("A1:A10), Range("D4:E5")

For Each cell in MyUnion

If cell.HasFormula = True Then

cell.Font.ColorIndex = 3

End If

Next cell

End Sub



URL (back to top)

To select a link and embed it on the worksheet cell, the procedure below will do the trick...

LocateURLDemo ()

Range("A1").Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:= _
"http://www.xlpert.com", TextToDisplay:="xlpert.com"


End Sub

When you click on cell A1, you will be able to connect to the website, "http://www.xlpert.com"



Unload

The Unload statement is use to remove the user form from memory. There are two type of statement where you can unload an active userform. For example if you’ve an active UserForm1. To unload enter the code below…

Unload Me

or

Unload UserForm1

But if you need to retain programmatic control, use the Hide() method instead. You still can access value in the hide userform using this Hide method. For example, you need to refer to a value on a textbox embed on the hide userform. This is how you use the Hide method...

UserForm1.Hide



V

Value (back to top)

You can assign value to variables after declaring them using the Dim statement. The correct data type will need to be assigned to the variables declared else there will be a type mismatch error. See the example below that cause an type mismatch error

Sub ValueDemo ()

Dim Int as Integer

x = "abc"

Int = x

End Sub

Sometimes you need to check a value before assign them to the correct variables. Here you only want to sum all the numeric value in a selected Range. If the value in a cell is a string it will jump to the next cell without adding the non-numeric cell.

Sub ValueDemo2 ()

Dim Total As Single

Range("B1:B100").Select

For i = 1 To Selection.Cells.Count

If IsNumeric(Range("B" & i)) Then

Total = Total + Range("B" & i).Value

End If

Next i

Range("B101").Value = Total

End Sub



Visible

The "Visible" property can take 3 different values. The first two are True or False meaning the a certain sheet is or is nor visible that it is either hidden or not.


Worksheets("Good ").Visible= True
Worksheets("Good 1").Visible= False

Remember that formulas in cells are calculated even if the sheet is hidden but before you can do anything programmatically on the sheet you must unhide it:


Worksheets("Good ").Visible = True
Worksheets("Good "). Select

Range(“A1”).Value = 10


The third value that the property "Visible" can take is very interesting. A sheet can be very hidden " Worksheets("Good ").Visible = xlVeryHidden". In this state not only the sheet is hidden but you can't see its name when in Excel you go to "Format/Sheets/Unhide". The value xlVeryHidden can only be changed programmatically. That means that only users that have access to the VBA code can unhide this sheet. If your code is protected by a password only users with the password can access the code and modify the "xlVeryHidden" value. You can use this value of the property "Visible" to hide confidential information like credit card details and personal info or to hide parameters that you don't want modified by the user.


Worksheets("Good ").Visible = True
Worksheets("Good "). Select

Range(“A1”).Value = 10

And hide it again…

Worksheets("Good ").Visible = xlVeryHidden

Remember also that formulas on other sheets referring to cells of a hidden or very hidden sheet work even if the sheet is hidden or very hidden.



W

Wait method

Pauses a running macro until a specified time. This is very useful when you want to display a splash to welcome the user. Create a userform and rename to "Welcome". Enter this statement on the Workbook Open event. The Welcome userform will be display for 5 seconds and unload after that.

Sub Workbook_Open ()

Welcome.Show

Application.Wait Now + (TimeValue("00:00:05")

Unload Welcome

End Sub



While Wend Statement (back to top)

Excel VBA has a few syntax that we can use when we want to continue to execute a procedure as long as certain condition is evaluates to true. The While...Wend statement is appropriate to be used when you want to loop through a block of code until a condition is satisfied. Let's look at an example below...

Sub WhileWendDemo ()

start = Timer

finish = Timer

elapsed = finish - start

While elapsed < 10 'condition to be evaluate

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

finish = Timer

elapsed = finish - start

Wend 'condition to be satisfied

Msg elapsed & " second has passed."

End Sub

The procedure above use the Timer function and the While...Wend loop to execute. It will stop after 10 seconds has passed.



Width Property (back to top)

You can specify the width of columns with the width property. Below is a procedure to set the width of a few columns.

Sub ColWidthDemo ( )

Colwidth = 20

For n = 1 to 10

ActiveSheet.Column(n).Width = 15

Next n

End Sub

Here the width of Column A to J is set to 20 points.



With statement

The With statement enables you to specified statements that refer to the same object.

Sub WithDemo ()

Set MyRange = Range("C1:C100")

With MyRange

.Font.Bold = True

.Font.ColorIndex = 6

.Font.Size = 12

End With

End Sub



X

XoR (back to top)

Perform a logical exclusions on two expressions. If both expressions are True or both are False then the result is False. If one of the expressions is True or False then it will return True. The code below will return False as both expression are correct. The second example will return True as one of the expression is True.

Sub xorDemo ()

x = 4

y = 5

If x = 4 Xor y = 5 Then Exit Sub 'result is False as both expression are correct

End Sub

Sub xorDemo2 ()

x = 4

y = 5

If x = 4 Xor y = 6 Then 'result is True as one of the expression is True

MsgBox "This is True

End If

End Sub

The above macro will return True as one is True and the other is False.

To put it simply : exp1 = True; exp2 = True then False

exp1 = False; exp2 = False then False

exp1 = True; exp2 = False then True



Y

Year Function (back to top)

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

Sub YearDemo()

MyDate = #10/15/2007#
MyYear = Year(MyDate) 'result is 2007

End Sub



Yes / No Message box

Sometime you need to get user response on whether to continue on a particular action. The Excel VBA code below will show a Yes / No message box to prompt the user.

Sub YesNoDemo ( )

Dim Ans As String

Dim Question As String

'Place your text here

Question = "Do you want to continue?"

'Display MessageBox

Answer = MsgBox(Question, vbQuestion + vbYesNo, "Continue?")

If Ans = vbNo Then

'No Action

Else

'Execute code here

End If

End Sub



Your Ad Here

Z

ZOrder Method (back to top)

You can moves the specified shape in front of or behind other shapes in the Excel shapes collection. The example below adds a rectangle to myWS and then places the rectangle to the back in the z-order if there is at least one other shape on the document.

Sub ZOrderDemo()
Set myWS = Worksheets(2)
Set myRec = myWS.Shapes.AddShape(msoShapeRectangle, 100, 100, 150, 200)

Do Until myRec.ZOrderPosition < 2
myRec.ZOrder msoSendBackward
Loop

End Sub