Accessing and Manipulating Spreadsheet Cells in Excel Macros

Showing the Value/format of the Current (active) Cell in a Message Box

1

Launch Excel.

2

Use the Tools | Macro | Visual Basic Editor command.

3

In the Project - VBAProject window, right-click the Microsoft Excel Objects folder.

4

From the Shortcut menu, select options Insert and Module options.

5

Enter the following Macro:

Sub ShowThisCellValue()

MsgBox "The value in the active cell is " & ActiveCell.Value & "."

End Sub

6

Through the Taskbar, switch to Excel and Sheet1.

7

In any cell, enter $100 (make sure the cell is active).

8

Invoke the ShowThisCellValue macro. Execute Tools | Macro | Macros | ShowThisCellValue | Run command. You should see this message box:

9

Other variations of the macro:

Sub ShowThisCellValue()

Dim thisCellValue

thisCellValue = ActiveCell.Value

MsgBox "The value in the active cell is " & thisCellValue & "."

MsgBox "The format of the cell is " & ActiveCell.NumberFormat

End Sub

Sub ShowThisCellValue()

Dim thisCellValue

thisCellValue = ActiveCell.Value

MsgBox "The value in the active cell is " & thisCellValue & "."

MsgBox "The format of the cell is " & ActiveCell.NumberFormat

End Sub

Note:

Dim is a variable declartion statement. 

ActiveCell is an object representing the active/current cell. 

& is a text concatenation (glue) operator. 

"" are fixed text (character string) delimiters. 

.Value is the value property of the ActiveCell object.  

.NumberFormat is the numeric format property of the ActiveCell object.

10

More similar examples:

Sub ShowThisCellValue()

Dim thisCellValue

thisCellValue = ActiveCell.Value

MsgBox "The value in the active cell is " & thisCellValue & "."

MsgBox "The format of the cell is " & ActiveCell.NumberFormat

End Sub

Sub ShowThisCellValue()

Dim thisCellValue

thisCellValue = ActiveCell.Value

MsgBox "The value in the active cell is " & thisCellValue & "."

MsgBox "The format of the cell is " & ActiveCell.NumberFormat

End Sub

Sub ShowThisCellValue()

Dim thisCell As Object

Set thisCell = ActiveCell

MsgBox "The value in the active cell is " & thisCell.Value & "."

MsgBox "The format of the cell is " & thisCell.NumberFormat & "."

End Sub

Sub ShowThisCellValue()

Dim thisCell As Object

Dim txt As String

Dim dot As String

Set thisCell = ActiveCell

txt = "The value in the active cell is "

dot = "."

MsgBox txt & thisCell.Value & dot

txt = "The format of the cell is "

MsgBox txt & thisCell.Value & dot

End Sub

Note:

Set is an object assignment statement (in Set thisCell = ActiveCell, thisCell becomes and alias of ActiveCell.

As Object is an object type specification.

As String is a text (character string) type specification.

Inserting Values into Cells

1

Switch to the Module 1 window.

2

Enter the following Macro:

Sub EnterFewNumbers()

Dim x As Object

Set x = ActiveCell

x.Offset(0, 0).Value = 0

x.Offset(1, 0).Value = 1

x.Offset(0, 1).Value = 2

x.Offset(1, 1).Value = 3

End Sub

3

Switch to Sheet1.

4

Select an empty cell.

5

Invoke the EnterFewNumbers macro. Execute Tools | Macro | Macros | EnterFewNumbers | Run command. Here is the output:

6

An Alternative Solution:

Sub InsertFewNumbers()

Dim x As Object

Dim r As Integer, c As Integer

Set x = ActiveCell

r = x.Row

c = x.Column

Cells(r, c).Value = 0

Cells(r + 1, c).Value = 1

Cells(r, c + 1).Value = 2

Cells(r + 1, c + 1).Value = 3

End Sub

7

Generate 100 random letters into a 10 by 10 matrix, starting from the active cell:

Sub GenerateRandomLetters()

Dim x As Object

Dim startRow As Integer, startCol As Integer

Dim r As Integer, c As Integer

Set x = ActiveCell

startRow = x.Row

startCol = x.Column

For r = 0 to 9

For c = 0 to 9

Cells(startRow + r, startCol + c).Value = Chr(65+Int(26*Rnd))

Next c

Next r

End Sub

Note:

Rnd is a function that generates uniformly distributed pseudo-random numbers between 0 and 1.

Int is a function that returns the integer portion of a number (e.g.: Int(2.5)=2, Int(-1.7)=2 ).

Chr is a function that converts an ASCII code into a character (e.g.: Chr(65) = "A", Chr(97) = "a").