Excel VBA Macro: Creating a Chart

In this post, we will create a chart using Microsoft Excel VBA macro.

Embedded Chart

Use an embedded chart when you want the chart displayed as part of a worksheet along with the data and/or other charts. Here is the example to create an embedded chart:

Public Sub EmbeddedChart()
 Dim myChartObject As ChartObject
 Dim myChart As Chart
 Set myChartObject = Worksheets("Sheet1").ChartObjects.Add(100, 150, 300, 225)
 Set myChart = co.Chart
 myChart.SetSourceData Source:=Worksheets("Sheet1").Range("A2:E6"), PlotBy:=xlRows
End Sub

Chart Sheets

Use a Chart Sheet when you want a chart displayed in different sheet.

Public Sub ChartSheet()
  Dim mychart As Chart
  Set mychart = ActiveWorkbook.Charts.Add
  mychart.SetSourceData Source:=Worksheets("Sheet1").Range("A2:E6"), PlotBy:=xlRows
End Sub

Related posts:

• Excel VBA Macro Examples: Sub Procedure

---
If you like posts in this blog, you can to support me :)

Excel VBA: Protecting Excel VBA Macro Code

When we working with Excel VBA macro, sometimes we need to protect our VBA macro code. So nobody can change or modify VBA macro that we have create. Here are steps to protect VBA Project:

• Go to Visual Basic Editor by pressing Alt+F11 key.

• In menu toolbar, click Tools -> VBAProject Properties...

• In VBAProject - Properties dialog box, click Protection tab, and then check Lock project for viewing checkbox.

• Enter desired password to protect VBA project, then click OK button.
• Save the VBA project.

FIN.

Related Post:

• Protect & Unprotect Worksheet/Excel File

---
If you like posts in this blog, you can to support me :)

Excel VBA: Selecting A Row Or Column

To select the entire column we can use the EntireColumn property. The following excel VBA macro example demonstrates how to select the column of the active cell.

Sub SelectColumn()
ActiveCell.EntireColumn.Select
End Sub

The next following excel VBA macro example demonstrates how to perform an operation on all cells in the selected row. This following procedure changes all cells font size to 18 in the row that contains the active cell.

Sub ChangeFontSize()
 ActiveCell.EntireRow.Font.Size = 18
End Sub

Related posts:

• Excel VBA Macro Examples: Sub Procedure
• Excel VBA Macro Tutorial: Range Objects

---
If you like posts in this blog, you can to support me :)

Excel VBA: Runs An Executable Program In Excel VBA Macro

Sometimes, it's very useful to run another application from Microsoft Excel. To runs another application in Microsoft Excel, we can use VBA's Shell function. The following example, will lunch the Notepad application and if the Shell function failed to launch the application, it generates an error.

Sub ExecNotepad()
  On Error Resume Next
  AppVal = Shell("C:\WINDOWS\NOTEPAD.EXE", 1)
  If Err <> 0 Then
      MsgBox "Can't start the application.", vbCritical, "Error"
  End If
End Sub

Related posts:

• The InputBox Function
• The MsgBox Function
• Excel VBA Macro Examples: Sub Procedure
• Excel VBA Macro Tutorial: Controlling Execution

---
If you like posts in this blog, you can to support me :)

Excel VBA: The InputBox Function

In Microsoft Excel VBA Macro, to obtain a single input from the user, we can use the InputBox function.
The InputBox function is useful for obtaining a single input from the user. Here is the InputBox function syntax:

InputBox(prompt[,title][,default])

The arguments:

• prompt: Required. Text that is displayed in the input box.

• title: Optional. Text that appears in the input box’s title bar.

• default: Optional. The default value.

The following is an example of how to use the InputBox function:

Sub AskUserName
ActiveSheet.Range("A1").Value = InputBox("Your name?","Input Name")
End Sub

Related posts:

• The MsgBox Function
• Excel VBA Macro Examples: Sub Procedure
• Excel VBA Macro Tutorial: Range Objects
• Excel VBA Macro Tutorial: Controlling Execution

---
If you like posts in this blog, you can  to support me :)

Excel VBA: The MsgBox Function

In Excel VBA macro, if we want display a message to the user, we can use the MsgBox function. The syntax for the MsgBox function is as follows:


MsgBox(prompt, buttons, title, helpfile, context)

prompt	Required. String expression displayed as the message in the dialog box. The maximum length of prompt is approximately 1024 characters, depending on the width of the characters used. If prompt consists of more than one line, you can separate the lines using a carriage return character (Chr(13)), a linefeed character (Chr(10)), or carriage return – linefeed character combination (Chr(13) & Chr(10)) between each line.
buttons	Optional. Numeric expression that is the sum of values specifying the number and type of buttons to display, the icon style to use, the identity of the default button, and the modality of the message box. If omitted, the default value for buttons is 0.
title	Optional. String expression displayed in the title bar of the dialog box. If you omit title, the application name is placed in the title bar.
helpfile	Optional. String expression that identifies the Help file to use to provide context-sensitive Help for the dialog box. If helpfile is provided, context must also be provided.
context	Optional. Numeric expression that is the Help context number assigned to the appropriate Help topic by the Help author. If context is provided, helpfile must also be provided.

The buttons argument settings are:

Constant	Value	Description
vbOKOnly	0	Display OK button only.
vbOKCancel	1	Display OK and Cancel buttons.
vbAbortRetryIgnore	2	Display Abort, Retry, and Ignore buttons.
vbYesNoCancel	3	Display Yes, No, and Cancel buttons.
vbYesNo	4	Display Yes and No buttons.
vbRetryCancel	5	Display Retry and Cancel buttons.
vbCritical	16	Display Critical Message icon.
vbQuestion	32	Display Warning Query icon.
vbExclamation	48	Display Warning Message icon.
vbInformation	64	Display Information Message icon.
vbDefaultButton1	0	First button is default.
vbDefaultButton2	256	Second button is default.
vbDefaultButton3	512	Third button is default.
vbDefaultButton4	768	Fourth button is default.
vbApplicationModal	0	Application modal; the user must respond to the message box before continuing work in the current application.
vbSystemModal	4096	System modal; all applications are suspended until the user responds to the message box.
vbMsgBoxHelpButton	16384	Adds Help button to the message box
VbMsgBoxSetForeground	65536	Specifies the message box window as the foreground window
vbMsgBoxRight	524288	Text is right aligned
vbMsgBoxRtlReading	1048576	Specifies text should appear as right-to-left reading on Hebrew and Arabic systems

The MsgBox function returns an integer value that identifies the button the user selected
to close the dialog box.

Constant	Value	Description
vbOK	1	OK
vbCancel	2	Cancel
vbAbort	3	Abort
vbRetry	4	Retry
vbIgnore	5	Ignore
vbYes	6	Yes
vbNo	7	No

Here is an example of using MsgBox funtion:

Sub ShowMsgBox()
   Dim response As Integer
    
   response = MsgBox("Do you want to continue?", vbYesNo + vbQuestion)

If response = vbYes Then
   Range("A1").Value = "'Yes' button clicked."
Else      Range("A1").Value = "'No' button clicked."   End If
End Sub

Related posts:

• Excel VBA Macro Examples: Sub Procedure
• Excel VBA Macro Tutorial: Range Objects
• Excel VBA Macro Tutorial: Controlling Execution

---
If you like posts in this blog, you can to support me :)

Excel VBA: Looping through a range

The following microsoft excel VBA macro example demonstrates how to loop through all the cells in a range. This example uses the For Each...Next statement to search the word "Microsoft Excel VBA" in a range and changes its font style to bold. In this case, the range is from A1 to E5.

Sub ChangeFontStyle()
   Dim Cell As Range
  
   For Each Cell In Range("A1:E5")
       If LCase(Cell.Value) = "microsoft excel vba" Then
           Cell.Font.Bold = True
       End If
   Next Cell
End Sub

Related posts:

• Excel VBA Macro Tutorial: Looping
• Find A Particular Text Within Ranges And Return Its Row Position
• Excel VBA Macro Examples: Sub Procedure
• Excel VBA Macro Tutorial: Range Objects
• Excel VBA Macro Tutorial: Controlling Execution

Excel VBA: Cut and paste using macro

In this Excel VBA Macro Examples section, I will show you how to move a range. This following example will move a range A4:E4 to I15:M15 in active sheet.

Sub MoveRange()
  ActiveSheet.Range("A4:E4").Cut _
    Destination:=ActiveSheet.Range("I15")
End Sub

If Destination argument is omitted, Microsoft Excel cuts the range to the Clipboard.

Related posts:

• Excel VBA Macro Examples: Copying a range
• Excel VBA Macro Examples: Function Procedure
• Excel VBA Macro Examples: Sub Procedure
• Excel VBA Macro Tutorial: Controlling Execution

Excel VBA: How To Delete Cells That Contain Certain Word

Sometimes, when we working with excel we need to delete cells that contain certain word. In this section, we will create excel vba macro code to delete cells that contain certain word.

Firstly, we have to create a function that we need for our next procedure. This function will count number of certain word in active sheet.

Function countText(ByVal searchText As String)
Dim rS As Range, counter As Long

counter = 0
Set rS = Cells.Find(searchText, LookIn:=xlValues, LookAt:=xlWhole)

If Not rS Is Nothing Then
    fAddress = rS.Address
    Do
        counter = counter + 1
        Set rS = Cells.FindNext(rS)
    Loop While Not rS Is Nothing And rS.Address <> fAddress
End If

countText = counter
End Function

And then, here is the main part of the procedure that will delete cells that contain certain word.

Sub DeleteCells(ByVal sText As String)
Dim r As Range

While countText(sText) > 0
    Set r = Cells.Find(sText, LookIn:=xlValues, LookAt:=xlWhole)

    If Not r Is Nothing Then
        r.Delete
    End If
Wend
End Sub

Here is an example how to use the procedure above. This example will delete cells that contain word "blablabla" in active sheet.

Sub TheExample()
DeleteCells ("blablabla")
End Sub

Related posts:

• How To Replace Data Using Excel VBA Macro Code
• Find A Particular Text Within Ranges And Return Its Row Position
• Excel VBA Macro Examples: Function Procedure
• Excel VBA Macro Examples: Sub Procedure
• Excel VBA Macro Tutorial: Controlling Execution

---
If you like posts in this blog, you can to support me :)

Excel VBA: How To Replace Data Using Excel VBA Macro Code

In my case, I often manipulating data, including but not limited to searches all data in a worksheet and makes the specified replacement.

Here's an example how replace data using excel vba macro code. This example will replace all cell value that contain word "micro excel" to "macro excel" in active sheet.

Sub ReplaceAllData()

    Cells.Replace _
        What:="micro excel", Replacement:="macro excel", _
    LookAt:=xlWhole, MatchCase:=False

End Sub

If you to make the search case sensitive you can change the MatchCase property to true. And also if you want to replace data that contain part of the searched data you can change the LookAt property to xlPart.


Related posts:

• Find A Particular Text Within Ranges And Return Its Row Position
• Excel VBA Macro Examples: Function Procedure
• Excel VBA Macro Examples: Sub Procedure
• Excel VBA Macro Tutorial: Range Objects
• Excel VBA Macro Tutorial: Controlling Execution

---
If you like posts in this blog, you can to support me :)

Excel VBA: Find A Particular Text Within Ranges And Return Its Row Position

In Microsoft Excel, sometimes we need to find the row position of particular text or even formula, to be able to insert certain of data below the corresponding text or even to delete row that contain the corresponding text.
This is an excel vba macro code to return row position that contain specific text.

Function rowPosition(ByVal searchText As Variant, _
    Optional ByVal stLookAt As XlLookAt = xlPart) As Long
    
    Dim rPos As Long, rS As Range
    
    rPos = 0
    
    Set rS = Cells.Find(searchText, LookIn:=xlValues, LookAt:=stLookAt)
    
    If Not rS Is Nothing Then rPos = rS.Row
    
    rowPosition = rPos
    
End Function

The function above have two arguments:

searchText : the text you are searching for
stLookAt : use xlWhole if you want to search the whole text, or use xlPart if otherwise

Here is example how to use the function above. This example deletes entire row that contain word "Test".

Sub RunExample()
    Dim rwPos As Long
       
    'Find row position
    rwPos = rowPosition("Test", xlPart)
    
    'If found then delete entire row
    If rwPos > 0 Then Rows(rwPos).Delete
End Sub

Related posts:

• Excel VBA Macro Examples: Function Procedure
• Excel VBA Macro Examples: Sub Procedure
• Excel VBA Macro Tutorial: Range Objects
• Excel VBA Macro Tutorial: Controlling Execution

---
If you like posts in this blog, you can to support me :)

Excel VBA: Check Whether Workbook Is Already Open Or Not

I often transfer data from current excel workbook to another excel workbook repeatedly. And I prefer creating vba macro to automate the task rather than doing it manually, which is very time consuming. So, before I open the destination excel workbook, I need to check whether the destination excel workbook is already open or not.

The following vba macro code is the function to check whether a workbook is already open or not. This function is quiet simple, it just do looping in Workbooks collection to check all opened workbook, if there is a workbook that have same name with the workbook we are looking for, then the function will return True or False if otherwise.

Function isWorkbookOpen(bookName As String) As Boolean
Dim vbResult As Boolean
Dim wbs As Workbook

vbResult = False
For Each wbs In Workbooks
    If UCase(wbs.Name) = UCase(bookName) Then
        vbResult = True
        Exit For
    End If
Next wbs
isWorkbookOpen = vbResult
End Function

This example show you how to use the function above.

Sub openWorkbook()
If isWorkbookOpen("macroexcel.xls") = False Then
   Workbooks.Open "d:\macroexcel.xls"
Else
   MsgBox "Workbook macroexcel.xls is already open."
End If
End Sub

Related posts:

• Excel VBA Examples: Opening & closing a workbook
• Excel VBA Tutorial: Looping
• Excel VBA Tutorial: Function Procedure
• Excel VBA Tutorial: Sub Procedure

Excel VBA: Protect & Unprotect Worksheet/Excel File

Imagine this; you are working with protected excel file, so only authorized user can access the file. Let's say you are the authorized user, and you are going to create macro excel that need an access to the protected file.

This vba macro example, sets password to excel file named "excel.xls". This example assumes excel file named "excel.xls" already open.

Sub ProtectExcel()
'write password
Workbooks("excel.xls").Password = "macroexcel"

'save workbook
Workbooks("excel.xls").Save
End Sub

This example opens the protected excel file named "excel.xls" on the D:\ drive.

Sub OpenProtectedExcel()
'open protected excel file
Workbooks.Open Filename:="D:\excel.xls", _
  Password:="macroexcel"
End Sub

This example remove protection from worksheet named "Sheet1" and protect it with new password.

Sub xProtect()
'Assumes excel.xls already open
'unprotect sheet
Workbooks("excel.xls").Sheets("Sheet1").Unprotect "password"

'protect sheet with new password
Workbooks("excel.xls").Sheets("Sheet1").Protect "vbamacro"

'save changes
Workbooks("excel.xls").Save
End Sub

Related posts:

• Opening & Closing Workbook
• Sub Procedure
• Function Procedure

Excel VBA Macro Example: Opening & Closing a Workbook (Excel File)

When we're working on something using macro in excel, sometimes we need the data in a different excel file (workbook). Rather than open the file (workbook) manually, we can open excel file by using Excel VBA Macro. Following example will show you how to open & close the excel file using Excel VBA Macro.

Sub OpenCopyAndClose()
 Dim excelFile As String

 excelFile = "myfile.xls"

  'open excel file "myfile.xls" in drive D:\
  Workbooks.Open "D:\" & excelFile

  'copy value of range A1 from "myfile.xls"
  ThisWorkbook.Sheets(1).Range("D1").Value = _
    Workbooks(excelFile).Sheets(1).Range("A1").Value

  'close "myfile.xls" file and don't save any changes
  Workbooks(excelFile).Close SaveChanges:=False
End Sub

Excel VBA Macro Tutorial: Excel's Event

An event handler procedure is a specially named procedure that's executed when a specific event occurs. Following are examples of types of events that Excel can recognize:

• A workbook is opened or closed
• A worksheet is activated or deactivated
• An object is clicked
• A worksheet is changed
• A workbook is saved
• A new workbook is created
• A window is activaed or deactivated
• A window is resized
• A new worksheet is added

Excel's events can be classified as the following:

• Workbook events. Events that occur for a particular workbook. Examples for this events are Open, Close, and BeforeSave.
• Worksheet events. Events that occur for a particular worksheet. Examples include Change, and SelectionChange.
• Chart events. Events that occur for a particular chart. Examples include Select.
• Application events. Events that occur for a particular the application (Excel itself).
• UserForm events. Events that occur for a particular UserForm or an object that contained on the UserForm. For example Click event.
• Events not associated with objects. For examples OnTime and OnKey events.

There is a strict rule we must follow when naming event handler procedures, the name must be in the form of objectname_eventname. For example, the CommandButton control has the Click event, for a CommandButton whose name is cmdButton1, the event handler procedure must be named cmdButton1_Click.

Event-handling procedures should be placed in the correct location. If the procedure is placed in the wrong location, it does not respond to its event even though it is named properly. Here are some guidelines:

• Event procedures for a user form (and its controls) should always go in the user
  form module itself.
• Event procedures for a workbook, worksheet, or chart should always be placed in
  the project associated with the workbook.
• If the object and the event can be found in the object and event list at the top of
  the editing window, it is all right to place the procedure in the current module.
• Never place event procedures in a code module (those project modules listed under
  the Modules node in the Project window).

Following example will puts word "Excel VBA Macro" when worksheet activated:

Private Sub Worksheet_Activate()
  ActiveSheet.Cells(1, 1).Value = "Excel VBA Macro"
End Sub

Related post: Excel VBA Macro Tutorial: Sub procedure

Excel VBA Macro Tutorial: Looping

What is looping? In simple term, looping is a process of repeating tasks. There are three types of loops, For-Next, Do-Loop, and While-Wend. Which we will use depends on the objectives and conditions.


For-Next loops

Repeats a group of statements a specified number of times.

Syntax:

For counter = start To end [Step step]
  [statements]
  [Exit  For]
  [statements]
Next [counter]

The following example, we will puts number words "Excel VBA Macro" to range A1:A10 in active sheet.

Sub putWords()
  For myNum = 1 To 10
     ActiveSheet.Cells(myNum, 1).Value = "Excel VBA Macro"
  Next myNum
End Sub

Do-Loop loops

Repeats a block of statements while a condition is True or until a condition becomes True.

Syntax:

Do [{While | Until}  condition]
  [statements]
  [Exit  Do]
  [statements]
Loop

Or, you can use this syntax:

Do
  [statements]
  [Exit  Do]
  [statements]
Loop [{While | Until} condition]

The output of this examples exactly same as example above.

Sub putWords2()
  myNum = 0
  Do
     myNum = myNum + 1
     ActiveSheet.Cells(myNum, 1).Value = "Excel VBA Macro"
  Loop While myNum <>
End Sub  

Sub putWords3()   
   myNum = 0
   Do Until myNum = 10
      myNum = myNum + 1
      ActiveSheet.Cells(myNum, 1).Value = "Excel VBA Macro"   
   Loop
End Sub

While-Wend loops

Executes a series of statements as long as a given condition is True.

Syntax:

While condition
  [statements]
Wend

Example:

Sub putWords4()
  myNum = 0
  While myNum < 10
     myNum = myNum + 1
     ActiveSheet.Cells(myNum, 1).Value = "Excel VBA Macro"
  Wend
End Sub 

Excel VBA Macro Example: Copying a range

In this Excel VBA Macro Example section, I will show you how to copy a range from C4:E4 to G10:H10.


Sub CopyRange()
   Sheets("Sheet1").Range("C4:E4").Copy _
     Destination:=Sheets("Sheet1").Range("G10")
End Sub

If Destination argument is omitted, Microsoft Excel copies the range to the Clipboard.

If you only want to copies value of the range (simulate copy paste specials value), you may use the following code:

Sub CopyPasteValue()
   Sheets("Sheet1").Range("C4:E4").Copy
   Sheets("Sheet1").Range("G10").PasteSpecial _
     Paste:=xlPasteValues
End Sub

VBA Macro Excel Tutorial: Function Procedure

The difference between Sub procedure and Function procedure is Function procedure usually return a single value or an array. Function procedures can be used in two ways:

• As part of an expression in VBA Macro Excel procedure.
• In formulas that you create in a worksheet.

The following is a custom function named DiscountPrice. This function returns the discount price as currency.


Function DiscountPrice(Price as Currency) as Currency
   DiscountPrice = Price * 0.95
End Function


Here's an example how to use the function in a formula:

=DiscountPrice(200)

And this is an example how to use the function in a VBA Macro Excel procedure:

Sub DiscountIt()
   inputPrice = InputBox("Enter price: ")
   MsgBox "New price: " & DiscountPrice(inputPrice)
End Sub

VBA Macro Excel Tutorial: Sub Procedure

A procedure is a series of VBA Macro Excel statements that resides in a VBA module. Sub procedure syntax:

[Private | Public] [Static] Sub name ([arglist])
[instructions]
[Exit Sub]
[instructions]
End Sub

Private (optional) indicates that the procedure is accessible only to other procedures in the same module.

Public (optional) indicates that the procedure is accessible to any procedures in any modules.

Static (optional) indicates that the procedure's variables are preserved when the procedure ends.

Sub (required) indicates the beginning of a procedure.

instructions (optional) represents valid VBA code.

Exit Sub (optional) a statement that forces an immediate exit from the procedure.

End Sub (required) indicates the end of a procedure.


Scope of Sub procedure

A procedure with a Private scope only accessible by procedures in the same module, whereas procedures with a Public scope can be accessed by any procedure in any module. By default, every procedure is a Public procedure.


Executing Sub procedures

There are many ways to execute Sub procedure:

• With the Run -> Run Sub/UserForm command. Or by pressing the F5 shortcut key.
• Form Excel's macro dialog box, which you can open by clicking Tools -> Macro -> Macros. Or by pressing the Alt+F8 shortcut key to access the Macro dialog box.
• By clicking a button or shape on a worksheet. You must have the procedure assigned to the button first.
• Form another procedure you write. To call a Sub procedure from another procedure you must type the name of the procedure and include arguments values, if any.
• When an event occurs. For example, when you open a workbook, saving a workbook, or closing a workbook, etc.
• From a toolbar button.

Related posts:

• Excel VBA Macro Tutorial: Function Procedure
• Excel VBA Macro Tutorial: Controlling Execution
• Find A Specific Text Within Range And Return Its Row Position
• Check Whether Workbook Is Already Open Or Not

---
If you like posts in this blog, you can to support me :)

VBA Macro Excel Tutorial: Controlling Execution

This section introduces a few of the more common programming constructs that are used to control the flow of execution: If-Then construct, Select Case construct, and For-Next loops.

The If-Then construct
One of the most important control structures in VBA Macro Excel is the If-Then construct. The basic syntax of the If-Then structure is as follows:

If condition Then true_statements [Else false_statements]

Following is the example:

Sub Hello()
   inputName = InputBox("Enter your name: ")
   If inputName = "" then
     MsgBox "Hello, Anonymous."
   Else
     MsgBox "Hello, " & inputName & "."
End Sub


The Select Case construct
The Select Case construct is useful for choosing among two or more options. The syntax for Select Case is as follows:

Select Case testexpression
   [Case expressionlist-n
     [instructions-n]]
   [Case Else
     [default_instructions]]
End Select

Following example is an alternative to If-Then-Else:

Sub Hello()
   inputName = InputBox("Enter your name: ")
     Select Case inputName = ""
       MsgBox "Hello, Anonymous."
     Case Else
       MsgBox "Hello, " & inputName & "."
End Sub

This is another example of Select Case with three options:

Sub TheNumber()
   inputName = InputBox("Enter number (0-9): ")
     Select Case 0
       MsgBox "Zero Number"
     Case 1, 3, 5, 7, 9
       MsgBox "Odd Number"
     Case 2, 4, 6, 8
       MsgBox "Even Number"
     Case Else Exit Sub
End Sub


For-Next loops
You can use a For-Next loop to process a series of items. Following is an example of a For-Next loop:

Sub SumNumber()
   Total = 0
   For Num = 1 To 10
     Total = Total + (Num)
   Next Num
   MsgBox Total
End Sub

VBA Macro Excel Tutorial: Range Objects

Range object is the heart of VBA Macro Excel programming, because much of the work you do in VBA Macro Excel involves ranges and cells in worksheets. A range object consists of a single cell or a range of cells on a single worksheet. There are three ways of referring to range objects:

• Range property
• Cells property
• Offset property

The Range property
The Range property has two syntaxes:

object.Range(cell)
object.Range(cell1, cell2)

Following example puts words "VBA Macro Excel" into a range A1:E1 on active sheet of the active workbook:

ActiveSheet.Range("A1:E1").Value = "VBA Macro Excel"

The next example produce the same result as the preceding example:

ActiveSheet.Range("A1", "E1").Value = "VBA Macro Excel"


The Cells property
The Cells property has three syntaxes:

object.Cells(rowIndex, columnIndex)
object.Cells(rowIndex)
object.Cells

This following example puts the value 5 into cell B5 on Sheet1 of the active workbook:

Sheets("Sheet1").Cells(5, 2).Value = 5

The second syntax of the Cells method uses a single argument that can range from 1 to 16,777,216 (256 columns x 65,536 rows). The cells are numbered from A1 continuing right then down to the next row.

For example, to enters value 2 into cells B2 of active sheet:

ActiveSheet.Cells(258).Value = 2

The third syntax for the Cells property returns all cells on the referenced worksheet. The following example will erase all cells value and its format:

ActiveSheet.Cells.Clear


The Offset property
The Offset property takes two arguments that correspond to the relative position from the upper-left cell of the specified Range object. The Offset property syntax is follow:

object.Offset(rowOffset, columnOffset)

The following example puts words "VBA Macro Excel" into the cell above the active cell:

ActiveCell.Offset(-1, 0).Value = "VBA Macro Excel"