Learn Excel VBA By Index (G)

GoTo

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

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.

Learn Excel VBA By Index (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

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

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

Learn Excel VBA By Index (E)

End Method

You can use the End of the Range object to select a particular cell. 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

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

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

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

Learn Excel VBA By Index (D)

Delete

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



Disable the Ctrl + Break and Esc key

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.

Learn Excel VBA By Index (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


Calling a Worksheet Function from Visual Basic

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

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

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

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

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. The macro below set cell to black color

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

End Sub



Custom Functions

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

Learn Excel VBA By Index (B)

Built -in Functions

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

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