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