Loading...

Follow Power Spreadsheets on Feedspot

Continue with Google
Continue with Facebook
Or

Valid


In this VBA Tutorial, you learn how to create message boxes and specify their most important characteristics, such as the following:

  1. How to specify the message displayed in the message box.

  2. How to customize or specify the buttons displayed by the message box.

  3. How to work with the value returned by the MsgBox function, and assign this value to a variable.

  4. How to specify the icon style used by the message box.

  5. How to specify the default button of in the message box.

  6. How to specify the modality of the message box.

This VBA Tutorial is accompanied by an Excel workbook containing the macros I use in the examples below. You can get immediate access to this example workbook by clicking on the button below.

Related VBA and Macro Tutorials

The following VBA and Macro Tutorials may help you better understand and implement the contents below:

  • General VBA constructs and structures:

    • Learn how to start working with macros here.

    • Learn about essential VBA terms here.

    • Learn how to enable or disable macros here.

    • Learn how to work with the VBE here.

    • Learn how to create and work with Sub procedures here.

    • Learn how to declare and work with variables here.

    • Learn about VBA data types here.

    • Learn how to work with functions in VBA here.

    • Learn how to work with the If… Then… Else statement here.

  • Practical VBA applications and macro examples:

    • Learn how to create UserForms here.

You can find additional VBA and Macro Tutorials in the Archives.

#1: Create MsgBox VBA code to create MsgBox

To create a basic message box with VBA, use a statement with the following structure:

MsgBox Prompt:=PromptString
Process to create MsgBox

To create a basic message box with VBA, use the MsgBox function (MsgBox …).

VBA statement explanation
  1. Item: MsgBox.

    • VBA construct: MsgBox function.

    • Description: The MsgBox function does the following:

      • Displays a message in a message box.

      • Waits for the user to click a button.

      • Returns a value of the Integer data type. This value indicates the button of the message box clicked by the user.

      When you create a basic message box using this statement structure:

      • The displayed message is PromptString.

      • The message box contains a single button: OK. For purposes of including other custom button layouts, please refer to the appropriate sections of this Tutorial.

      • The value returned by the MsgBox function is vbOK (or 1). For purposes of assigning the value returned by the MsgBox function to a variable, please refer to the appropriate section of this Tutorial.

  2. Item: Prompt:=PromptString.

    • VBA construct: Prompt argument of the MsgBox function and string expression.

    • Description: Use the Prompt argument of the MsgBox function to specify the message displayed in the message box. For these purposes:

      • You generally specify PromptString as a string expression.

      • If you explicitly declare a variable to represent PromptString, you can usually work with the String data type.

      • The maximum length of PromptString is roughly 1024 characters. However, this maximum length may vary slightly depending on the width of the characters within PromptString.

      • PromptString can be composed of multiple lines. For purposes of creating a message box with multiple lines (by including line breaks or new lines), please refer to the appropriate section of this Tutorial.
Macro example to create MsgBox

The following macro example creates a basic message box with the message “Create Excel VBA MsgBox”.

Sub createMsgBox()
    'source: https://powerspreadsheets.com/
    'creates a message box
    'for further information: https://powerspreadsheets.com/excel-vba-msgbox/

    'create a message box
    MsgBox Prompt:="Create Excel VBA MsgBox"

End Sub
Effects of executing macro example to create MsgBox

The following image illustrates the results of executing the macro example.

#2: Create MsgBox with multiple lines (new line or line break) VBA code to create MsgBox with multiple lines (new line or line break)

To create a message box with multiple lines (by including a new line or using line breaks) using VBA, use a statement with the following structure:

MsgBox Prompt:=PromptString1 & NewLineCharacter & PromptString2 & ... & NewLineCharacter & PromptString#
Process to create MsgBox with multiple lines (new line or line break)

To create a message box with multiple lines (by including a new line or using line breaks) using VBA, follow these steps:

  1. Create a message box with the MsgBox function (MsgBox …).

  2. Specify the message displayed in the message box as an appropriately concatenated (with the & character) combination of:

    • Strings (PromptString1, PromptString2, …, PromptString#); and

    • Characters that create a new line or line break (NewLineCharacter).
VBA statement explanation
  1. Item: MsgBox.

    • VBA construct: MsgBox function.

    • Description: The MsgBox function does the following:

      • Displays a message in a message box.

      • Waits for the user to click a button.

      • Returns a value of the Integer data type. This value indicates the button of the message box clicked by the user.

      When you create a message box with multiple lines (by including a new line or using line breaks) using this statement structure:

      • The displayed message is that specified by the Prompt argument.

      • The message box contains a single button: OK. For purposes of including other custom button layouts, please refer to the appropriate sections of this Tutorial.

      • The value returned by the MsgBox function is vbOK (or 1). For purposes of assigning the value returned by the MsgBox function to a variable, please refer to the appropriate section of this Tutorial.

  2. Item: Prompt:=PromptString1 & NewLineCharacter & PromptString2 & … & NewLineCharacter & PromptString#.

    • VBA construct: Prompt argument of the MsgBox function and string expression.

    • Description: Use the Prompt argument of the MsgBox function to specify the message displayed in the message box.

      When you create a message box with multiple lines (by including a new line or using line breaks), you build the string expression assigned to Prompt (PromptString1 & NewLineCharacter & PromptString2 & … & NewLineCharacter & PromptString#) by concatenating as many strings (PromptString1, PromptString2, …, PromptString#) and newline characters (NewLineCharacter) as required.

      The maximum length of the string expression assigned to prompt is roughly 1024 characters. However, this maximum length may vary slightly depending on the width of the characters you include.

      If you explicitly declare a variable to represent this string expression, you can usually work with the String data type.

  3. Item: PromptString1, PromptString2, …, PromptString#.

    • VBA construct: Strings expressions.

    • Description: PromptStrings are the strings (excluding the new line characters) that determine the message displayed in the message box.

      If you explicitly declare variables to represent the different PromptStrings, you can usually work with the String data type.

  4. Item: &.

    • VBA construct: Concatenation (&) operator.

    • Description: The & operator carries out string concatenation. Therefore, & concatenates the different strings (PromptString1, PromptString2, …, PromptString#) and new line characters (NewLineCharacter) you use to specify the string expression assigned to the Prompt argument.

  5. Item: NewLineCharacter.

    • VBA construct: A character or character combination returning 1 of the following:

      • Carriage return.

      • Linefeed.

      • Carriage return linefeed combination.

      • New line (which is platform specific).

    • Description: Specify NewLineCharacter using any of the constants or character codes (with the Chr function) listed below.


      Constant Equivalent Chr function General Description
      vbLf Chr(10) Linefeed
      vbCr Chr(13) Carriage return
      vbCrLf Chr(13) & Chr(10) Carriage return linefeed combination
      vbNewLine Chr(13) & Chr(10) in Excel for Windows or Chr(13) in Excel for Mac New line character, which is platform specific
Macro example to create MsgBox with multiple lines (new line or line break)

The following macro example creates a message box with a message displayed in multiple lines by adding a new line as follows:

  • Line #1: “Create Excel VBA MsgBox”.

  • Line #2: “And add a new line”.
Sub MsgBoxNewLine()
    'source: https://powerspreadsheets.com/
    'creates a message box with a new line or line break
    'for further information: https://powerspreadsheets.com/excel-vba-msgbox/

    'create a message box with a new line or line break
    MsgBox Prompt:="Create Excel VBA MsgBox" & vbNewLine & "And add a new line"

End Sub
Effects of executing macro example to create MsgBox with multiple lines (new line or line break)

The following image illustrates the results of executing the macro example. Notice that, as expected, the message box contains multiple lines.

#3: Create MsgBox with title VBA code to create MsgBox with title

To create a message box with title using VBA, use a statement with the following structure:

MsgBox Prompt:=PromptString, Title:=TitleString
Process to create MsgBox with title

To create a message box with title using VBA, follow these steps:

  1. Create a message box with the MsgBox function (MsgBox …).

  2. Specify the message displayed in the message box (Prompt:=PromptString).

  3. Specify the message box title (Title:=TitleString).
VBA statement explanation
  1. Item: MsgBox.

    • VBA construct: MsgBox function.

    • Description: The MsgBox function does the following:

      • Displays a message in a message box.

      • Waits for the user to click a button.

      • Returns a value of the Integer data type. This value indicates the button of the message box clicked by the user.

      When you create a message box with title using this statement structure:

      • The displayed message is PromptString.

      • The message box contains a single button: OK. For purposes of including other custom button layouts, please refer to the appropriate sections of this Tutorial.

      • The value returned by the MsgBox function is vbOK (or 1). For purposes of assigning the value returned by the MsgBox function to a variable, please refer to the appropriate section of this Tutorial.

  2. Item: Prompt:=PromptString.

    • VBA construct: Prompt argument of the MsgBox function and string expression.

    • Description: Use the Prompt argument of the MsgBox function to specify the message displayed in the message box. For these purposes:

      • You generally specify PromptString as a string expression.

      • If you explicitly declare a variable to represent PromptString, you can usually work with the String data type.

      • The maximum length of PromptString is roughly 1024 characters. However, this maximum length may vary slightly depending on the width of the characters within PromptString.

      • PromptString can be composed of multiple lines. For purposes of creating a message box with multiple lines (by including line breaks or new lines), please refer to the appropriate section of this Tutorial.

  3. Item: Title:=TitleString.

    • VBA construct: Title argument of the MsgBox function and string expression.

    • Description: Use the Title argument of the MsgBox function to specify the title displayed in the title bar of the message box. If you omit the Title argument, the title displayed in the title bar of the message box is “Microsoft Excel”.

      You generally specify TitleString as a string expression. If you explicitly declare a variable to represent TitleString, you can usually work with the String data type.
Macro example to create MsgBox with title

The following macro example creates a message box with:

  • The message “Create Excel VBA MsgBox”; and

  • The title “Add title to MsgBox.
Sub MsgBoxTitle()
    'source: https://powerspreadsheets.com/
    'creates a message box with a title
    'for further information: https://powerspreadsheets.com/excel-vba-msgbox/

    'create a message box with a title
    MsgBox Prompt:="Create Excel VBA MsgBox", Title:="Add title to MsgBox"

End Sub
Effects of executing macro example to create MsgBox with title

The following image illustrates the results of executing the macro example. Notice that, as expected, the message box contains a custom title (Add title to MsgBox).

#4: Create MsgBox that returns value based on user input and assigns value to a variable VBA code to create MsgBox that returns value based on user input and assigns value to a variable

To create a message box that:

  • Returns a value based on the user’s input; and

  • Assigns that value to a variable;

with VBA, use a statement with the following structure:

Variable = MsgBox(Prompt:=PromptString, Buttons:=ButtonsExpression)
Process to create MsgBox that returns value based on user input and assigns value to a variable

To create a message box that:

  • Returns a value based on the user’s input; and

  • Assigns that value to a variable;

with VBA, follow these steps:

  1. Create a message box with the MsgBox function (MsgBox(…)).

  2. Specify the buttons to be displayed in the message box (Buttons:=ButtonsExpression).

  3. Assign the value returned by the MsgBox function to a variable (Variable = MsgBox(…)).
VBA statement explanation
  1. Item: Variable.

    • VBA construct: Variable.

    • Description: Variable you want to hold the value returned by the MsgBox function.

      If you explicitly declare Variable, you can usually work with the Integer data type.

  2. Item: =.

    • VBA construct: Assignment (=) operator.

    • Description: The = operator assigns the Integer value returned by the MsgBox function to Variable.

  3. Item: MsgBox.

    • VBA construct: MsgBox function.

    • Description: The MsgBox function does the following:

      • Displays a message in a message box.

      • Waits for the user to click a button.

      • Returns a value of the Integer data type. This value indicates the button of the message box clicked by the user.

      When you create a message box that returns a value based on user input (and assigns the value to a variable) using this statement structure:

      • The displayed message is PromptString.

      • The message box contains the buttons specified by ButtonsExpression. For purposes of working with the main custom button layouts, please refer to the appropriate sections of this Tutorial.

  4. Item: Prompt:=PromptString.

    • VBA construct: Prompt argument of the MsgBox function and string expression.

    • Description: Use the Prompt argument of the MsgBox function to specify the message displayed in the message box. For these purposes:

      • You generally specify PromptString as a string expression.

      • If you explicitly declare a variable to represent PromptString, you can usually work with the String data type.

      • The maximum length of PromptString is roughly 1024 characters. However, this maximum length may vary slightly depending on the width of the characters within PromptString.

      • PromptString can be composed of multiple lines. For purposes of creating a message box with multiple lines (by including line breaks or new lines), please refer to the appropriate section of this Tutorial.

  5. Item: Buttons:=ButtonsExpression.

    • VBA construct: Buttons argument of the MsgBox function.

    • Description: Use the Buttons argument of the MsgBox function to specify the following:

      • Number and type of buttons displayed in the message box.

      • Icon style for the message box.
Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

In this VBA Tutorial, you learn how to:

  • Set a cell’s or cell range’s value; and

  • Get a cell’s or cell range’s value.

This VBA Tutorial is accompanied by Excel workbooks containing the macros and data I use in the examples below. You can get immediate access to these example workbooks by clicking on the button below.


Related VBA and Macro Tutorials

The following VBA and Macro Tutorials may help you better understand and implement the contents below:

  • General VBA constructs and structures:

    • Begin working with macros here.

    • Learn about basic VBA constructs and structures here.

    • Learn how to enable or disable macros in Excel here.

    • Learn how to work with the Visual Basic Editor here.

    • Learn how to work with Sub procedures here.

    • Learn how to create object references here.

    • Learn several ways to refer to cell ranges here.

    • Learn how to work with properties here.

    • Learn how to declare and assign data to variables here.

    • Learn about VBA data types here.

    • Learn how to work with arrays here.

    • Learn how to work with R1C1-style references here.

    • Learn how to work with loops here.

  • Practical VBA applications and macro examples:

    • Learn how to copy and paste values here.

    • Learn how to find the last row in a worksheet here.

    • Learn how to find the last column in a worksheet here.

    • Learn how to specify a column’s width here.

    • Learn how to convert strings to numbers here.

You can find additional VBA and Macro Tutorials in the Archives.

#1: Set cell value VBA code to set cell value

To set a cell’s value with VBA, use a statement with the following structure:

Cell.ValueOrValue2 = CellValue
Process to set cell value

To set a cell’s value with VBA, follow these steps:

  1. Identify and return a Range object representing the cell whose value you want to set (Cell).

  2. Set the cell’s value with the Range.Value or Range.Value2 property (ValueOrValue2 = CellValue).
VBA statement explanation
  1. Item: Cell.

    • VBA construct: Range object.

    • Description: Cell is a Range object representing the cell whose value you want to set.

      For purposes of returning such a Range object, work with constructs such as the Worksheet.Range, Worksheet.Cells, Application.ActiveCell, Application.Selection, Range.Range, Range.Cells, or Range.Offset properties.

      If you explicitly declare an object variable to represent Cell, use the Range object data type.

  2. Item: Value or Value2.

    • VBA construct: Range.Value or Range.Value2 property.

    • Description: Both the Range.Value and Range.Value2 properties set the value of Cell.

      The difference between Range.Value and Range.Value2 is the data types they work with. Range.Value2 doesn’t use Currency nor Date. This difference is particularly important for purposes of getting a cell’s value. For a more detailed discussion of this topic, please refer to the appropriate section.

  3. Item: =.

    • VBA construct: Assignment operator.

    • Description: The = operator assigns the value returned by the expression on its right (CellValue) to the property on its left (Cell.ValueOrValue2).

  4. Item: CellValue.

    • VBA construct: New value of Range.Value or Range.Value2 property.

    • Description: CellValue is the new value you specify for Cell. You can specify, among others, numeric values or text strings.
Macro examples to set cell value

The following macro example sets a cell’s (myCellSetValue) value to the string “set cell value with Range.Value” with the Range.Value property.

Sub setCellValue()
    'source: https://powerspreadsheets.com/
    'sets a cell's value
    'for further information: https://powerspreadsheets.com/excel-vba-value-value2/

    'declare object variable to hold reference to cell where you write the value
    Dim myCellSetValue As Range

    'identify cell where you set the value
    Set myCellSetValue = ThisWorkbook.Worksheets("set cell value").Range("A7")

    'set cell value with Range.Value property
    myCellSetValue.Value = "set cell value with Range.Value"

End Sub

The following macro example sets a cell’s (myCellSetValue2) value to the string “set cell value with Range.Value2” with the Range.Value2 property.

Sub setCellValue2()
    'source: https://powerspreadsheets.com/
    'sets a cell's value
    'for further information: https://powerspreadsheets.com/excel-vba-value-value2/

    'declare object variable to hold reference to cell where you write the value
    Dim myCellSetValue2 As Range

    'identify cell where you set the value
    Set myCellSetValue2 = ThisWorkbook.Worksheets("set cell value").Range("A11")

    'set cell value with Range.Value2 property
    myCellSetValue2.Value2 = "set cell value with Range.Value2"

End Sub
Effects of executing macro example to set cell value

The following GIF illustrates the results of executing the first macro example, which works with the Range.Value property. The value of cell A7 is set to the string “set cell value with Range.Value”.

The following GIF illustrates the results of executing the second macro example, which works with the Range.Value2 property. The value of cell A11 is set to the string “set cell value with Range.Value2”.

#2: Set cell range value VBA code to set cell range value

To set a cell range’s value with VBA, use a statement with the following structure:

CellRange.ValueOrValue2 = CellRangeValue
Process to set cell range value

To set a cell range’s value with VBA, follow these steps:

  1. Identify and return a Range object representing the cell range whose value you want to set (CellRange).

  2. Set the cell range’s value with the Range.Value or Range.Value2 property (ValueOrValue2 = CellRangeValue).
VBA statement explanation
  1. Item: CellRange.

    • VBA construct: Range object.

    • Description: CellRange is a Range object representing the cell range whose value you want to set.

      For purposes of returning such a Range object, work with constructs such as the Worksheet.Range, Worksheet.Cells, Application.Selection, Range.Range, Range.Cells, Range.Offset or Range.Resize properties.

      If you explicitly declare an object variable to represent CellRange, use the Range object data type.

  2. Item: ValueOrValue2.

    • VBA construct: Range.Value or Range.Value2 property.

    • Description: Both the Range.Value and Range.Value2 properties set the value of CellRange.

      The difference between Range.Value and Range.Value2 is the data types they work with. Range.Value2 doesn’t use Currency nor Date. This difference is particularly important for purposes of getting a cell range’s value. For a more detailed discussion of this topic, please refer to the appropriate section.

  3. Item: =.

    • VBA construct: Assignment operator.

    • Description: The = operator assigns the value returned by the expression on its right (CellRangeValue) to the property on its left (CellRange.ValueOrValue2).

  4. Item: CellRangeValue.

    • VBA construct: New value of Range.Value or Range.Value2 property.

    • Description: CellRangeValue is the new value you specify for CellRange. You can specify, among others, numeric values or text strings.
Macro examples to set cell range value

The following macro example sets a cell range’s (myCellRangeSetValue) value to the string “set cell range value with Range.Value” with the Range.Value property.

Sub setCellRangeValue()
    'source: https://powerspreadsheets.com/
    'sets a cell range's value
    'for further information: https://powerspreadsheets.com/excel-vba-value-value2/

    'declare object variable to hold reference to cell range where you write the value
    Dim myCellRangeSetValue As Range

    'identify cell range where you set the value
    Set myCellRangeSetValue = ThisWorkbook.Worksheets("set cell value").Range("A15:C19")

    'set cell range value with Range.Value property
    myCellRangeSetValue.Value = "set cell range value with Range.Value"

End Sub

The following macro example sets a cell range’s (myCellRangeSetValue2) value to the string “set cell range value with Range.Value2” with the Range.Value2 property.

Sub setCellRangeValue2()
    'source: https://powerspreadsheets.com/
    'sets a cell range's value
    'for further information: https://powerspreadsheets.com/excel-vba-value-value2/

    'declare object variable to hold reference to cell range where you write the value
    Dim myCellRangeSetValue2 As Range

    'identify cell range where you set the value
    Set myCellRangeSetValue2 = ThisWorkbook.Worksheets("set cell value").Range("A23:C27")

    'set cell range value with Range.Value2 property
    myCellRangeSetValue2.Value = "set cell range value with Range.Value2"

End Sub
Effects of executing macro example to set cell range value

The following GIF illustrates the results of executing the first macro example, which works with the Range.Value property. The value of cells A15 to C19 is set to the string “set cell range value with Range.Value”.

The following GIF illustrates the results of executing the second macro example, which works with the Range.Value2 property. The value of cells A23 to C27 is set to the string “set cell range value with Range.Value2”.

#3: Get cell value VBA code to get cell value

To get a cell’s value with VBA, use a statement with the following structure:

myVariable = Cell.ValueOrValue2
Process to get cell value

To get a cell’s value with VBA, follow these steps:

  1. Identify and return a Range object representing the cell whose value you want to get (Cell).

  2. Get the cell’s value with the Range.Value or Range.Value2 property (ValueOrValue2).

  3. Assign the value returned by Range.Value or Range.Value to a variable (myVariable =).
VBA statement explanation
  1. Item: myVariable.

    • VBA construct: Variable.

    • Description: myVariable is the variable you want to hold Cell’s value.

      If you explicitly declare myVariable, use a data type that’s capable of holding all the potential values that Cell may hold.

  2. Item: =.

    • VBA construct: Assignment operator.

    • Description: The = operator assigns the value returned by the expression on its right (Cell.ValueOrValue2) to the variable on its left (myVariable).

  3. Item: Cell.

    • VBA construct: Range object.

    • Description: Cell is a Range object representing the cell whose value you want to get.

      For purposes of returning such a Range object, work with constructs such as the Worksheet.Range, Worksheet.Cells, Application.ActiveCell, Application.Selection, Range.Range, Range.Cells, or Range.Offset properties.

      If you explicitly declare an object variable to represent Cell, use the Range object data type.

  4. Item: ValueOrValue2.

    • VBA construct: Range.Value or Range.Value2 property.

    • Description: Both the Range.Value and Range.Value2 properties return the value of Cell.

      The difference between Range.Value and Range.Value2 is the data types they work with. Range.Value2 doesn’t use Currency nor Date. Therefore, if Cell’s number format is Date or Currency, Range.Value converts Cell’s value to the Date or Currency data type, as appropriate. Range.Value2 doesn’t carry out this conversion and, therefore, Range.Value2 generally returns such value as of the Double data type.

      The Currency data type stores numbers in an integer format scaled by 10,000. this results in a fixed-point number with 4 decimal digits. If Cell’s value contains more decimal places, Range.Value and Range.Value2 tend to return different values. Generally, if Cell’s value may contain more than 4 decimal places, Range.Value2 is more accurate.
Macro examples to get cell value

The following macro example:

  1. Gets a cell’s (A7) value with the Range.Value property.

  2. Assigns the cell’s value to a variable (myValue).

  3. Displays a message box with the value held by the variable.
Sub getCellValue()
    'source: https://powerspreadsheets.com/
    'gets a cell's value
    'for further information: https://powerspreadsheets.com/excel-vba-value-value2/

    'declare variable to hold cell value
    Dim myValue As Variant

    'get cell value with Range.Value property and assign it to variable
    myValue = ThisWorkbook.Worksheets("get cell value").Range("A7").Value

    'display cell value
    MsgBox myValue

End Sub

The following macro example:

  1. Gets a cell’s (A7) value with the Range.Value2 property.

  2. Assigns the cell’s value to a variable (myValue2).

  3. Displays a message box with the value held by the variable.
Sub getCellValue2()
    'source: https://powerspreadsheets.com/
    'gets a cell's value
    'for further information: https://powerspreadsheets.com/excel-vba-value-value2/

    'declare variable to hold cell value
    Dim myValue2 As Variant

    'get cell value with Range.Value2 property and assign it to variable
    myValue2 = ThisWorkbook.Worksheets("get cell value").Range("A7").Value2

    'display cell value
    MsgBox myValue2

End Sub
Effects of executing macro example to get cell value

The following GIF illustrates the results of executing the first macro example, which works with the Range.Value property. The message box displays the value of cell A7.

Notice that cell A7 is formatted as currency. The Range.Value property converts the cell’s value to the Currency data type, which results in a fixed-point number with 4 decimal digits. Therefore, the message box displays a value with only 4 decimal places, instead of the 10 decimal places that the original value in cell A7 has.

The following GIF illustrates the results of executing the second macro example, which works with the Range.Value2 property. The message box displays the value of cell A7.

The Range.Value2 property doesn’t work with the Currency data type. Therefore, the message box displays all the (10) decimal places that the original value in cell A7 has.

#4: Get cell range value VBA code to get cell range value

To get a cell range’s value with VBA, use a statement with the following structure:

Dim myArray() As Variant
myArray = CellRange.ValueOrValue2
Process to get cell range value

To get a cell range’s value with VBA, follow these steps:

  1. Declare an array of the Variant data type (myArray).

  2. Identify and return a Range object representing the cell range whose value you want to get (CellRange).

  3. Get the cell range’s value with the Range.Value or Range.Value2 property.

  4. Assign the value returned by Range.Value or Range.Value to the previously-declared array (myArray =).
VBA statement explanation Line #1: Dim myArray As Variant
  1. Item: Dim myArray() As Variant.

    • VBA construct: Dim statement.

    • Description: The Dim statement declares an array (myArray) as of the Variant data type.

      myArray is the array you want to hold CellRange’s values.
Line #2: myArray = CellRange.ValueOrValue2
  1. Item: myArray.

    • VBA construct: Array.

    • Description: myArray is the array you want to hold CellRange’s values.

  2. Item: =.

    • VBA construct: Assignment operator.

    • Description: The = operator assigns the values returned by the expression on its right (CellRange.ValueOrValue2) to the array on its left (myArray).

  3. Item: CellRange.

    • VBA construct: Range object.

    • Description: CellRange is a Range object representing the cell range whose values you want to get.

      For purposes of returning such a Range object, work with constructs such as the Worksheet.Range, Worksheet.Cells, Application.Selection, Range.Range, Range.Cells, Range.Offset or Range.Resize properties.

      If you explicitly declare an object variable to represent CellRange, use the Range object data type.
Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

In this VBA Tutorial, you learn how to convert strings to numbers of the following data types:

  1. String to Byte.

  2. String to Integer.

  3. String to Long.

  4. String to Single.

  5. String to Double.

  6. String to Currency.

  7. String to Decimal.


This VBA Tutorial is accompanied by an Excel workbook containing the macros, data and formulas I use in the examples below. You can get immediate access to this example workbook by clicking on the button below.

Related VBA and Macro Tutorials

The following VBA and Macro Tutorials may help you better understand and implement the contents below:

  • General VBA constructs and structures:

    • Learn about essential VBA terms here.

    • Learn how to work in the Visual Basic Editor here.

    • Learn how to create Sub procedures here.

    • Learn how to create Function procedures here.

    • Learn how to work with variables here.

    • Learn about VBA data types here.

    • Learn about the Range object here.

  • Tutorials about other useful topics:

    • Learn how to work with the IFERROR worksheet function here.

You can find additional VBA and Macro Tutorials in the Archives.

#1: Convert String to Byte VBA code to convert String to Byte

To convert a string to a number of the Byte data type, use the following statement:

CByte(String)
Process followed by VBA to convert String to Byte

To convert a string to a number of the Byte data type, use the CByte function to convert the String to a number of the Byte data type.

VBA statement explanation
  1. Item: CByte.

    • VBA construct: CByte function.

    • Description: The CByte function coerces String to the Byte data type.

      CByte is usually able to carry out internationally-aware conversions from the String to the Byte data type. In other words, CByte generally recognizes between the different decimal/thousand separators and currency options that depend on your computer’s locale.

      The Byte data type can hold numbers ranging from 0 to 255. If String is outside this range, an error occurs.

  2. Item: String.

    • VBA construct: String expression and expression argument of the CByte function.

    • Description: String is the string or numeric expression you convert to the Byte data type. If you explicitly declare a variable to represent String, use the Variant data type.
Macro example to convert String to Byte

The following macro example, a User-Defined Function, converts a string passed as argument (myString) to Byte.

Function stringToByte(myString As Variant)
    'source: https://powerspreadsheets.com/
    'converts String to Byte
    'for further information: https://powerspreadsheets.com/vba-string-to-number/

    'convert String to Byte
    stringToByte = CByte(myString)
End Function
Effects of executing macro example to convert String to Byte

The following image illustrates the results of using the macro example in a worksheet formula. For these purposes:

  • Column A contains a numeric string.

  • Column B contains a worksheet formula that uses the UDF example.

    When String is outside the range of Byte, the worksheet formula returns the #VALUE! error.

  • Column C displays the worksheet formula used in column B.

#2: Convert String to Integer VBA code to convert String to Integer

To convert a string to a number of the Integer data type, use the following statement:

CInt(String)
Process followed by VBA to convert String to Integer

To convert a string to a number of the Integer data type, use the CInt function to convert the String to a number of the Integer data type.

VBA statement explanation
  1. Item: CInt.

    • VBA construct: CInt function.

    • Description: The CInt function coerces String to the Integer data type.

      If String contains a fraction, CInt rounds it. If this fraction is precisely 0.5, CInt rounds to the nearest even number. For example:

      • 0.5 is rounded to 0.

      • Both 1.5 and 2.5 are rounded to 2.

      • Both 3.5 and 4.5 are rounded to 4.

    • CInt is usually able to carry out internationally-aware conversions from the String to the Integer data type. In other words, CInt generally recognizes between the different decimal/thousand separators and currency options that depend on your computer’s locale.

      The Integer data type can hold numbers ranging from -32,768 to 32,767. If String is outside this range, an error occurs.

  2. Item: String.

    • VBA construct: String expression and expression argument of the CInt function.

    • Description: String is the string or numeric expression you convert to the Integer data type. If you explicitly declare a variable to represent String, use the Variant data type.
Macro example to convert String to Integer

The following macro example, a User-Defined Function, converts a string passed as argument (myString) to Integer.

Function stringToInteger(myString As Variant)
    'source: https://powerspreadsheets.com/
    'converts String to Integer
    'for further information: https://powerspreadsheets.com/vba-string-to-number/

    'convert String to Integer
    stringToInteger = CInt(myString)
End Function
Effects of executing macro example to convert String to Integer

The following image illustrates the results of using the macro example in a worksheet formula. For these purposes:

  • Column A contains a numeric string.

  • Column B contains a worksheet formula that uses the UDF example.

    When String is outside the range of Integer, the worksheet formula returns the #VALUE! error. Additionally, if String contains a fraction that’s precisely 0.5, the UDF rounds to the nearest even number.

  • Column C displays the worksheet formula used in column B.

#3: Convert String to Long VBA code to convert String to Long

To convert a string to a number of the Long data type, use the following statement:

CLng(String)
Process followed by VBA to convert String to Long

To convert a string to a number of the Long data type, use the CLng function to convert the String to a number of the Integer data type.

VBA statement explanation
  1. Item: CLng.

    • VBA construct: CLng function.

    • Description: The CLng function coerces String to the Long data type.

      If String contains a fraction, CLng rounds it. If this fraction is precisely 0.5, CLng rounds to the nearest even number. For example:

      • 0.5 is rounded to 0.

      • Both 1.5 and 2.5 are rounded to 2.

      • Both 3.5 and 4.5 are rounded to 4.

        [li]CLng is usually able to carry out internationally-aware conversions from the String to the Long data type. In other words, CLng generally recognizes between the different decimal/thousand separators and currency options that depend on your computer’s locale.

        [li]The Long data type can hold numbers ranging from -2,147,483,648 to 2,147,483,647. If String is outside this range, an error occurs.

  2. Item: String.

    • VBA construct: String expression and expression argument of the CLng function.

    • Description: String is the string or numeric expression you convert to the Long data type. If you explicitly declare a variable to represent String, use the Variant data type.
Macro example to convert String to Long

The following macro example, a User-Defined Function, converts a string passed as argument (myString) to Long.

Function stringToLong(myString As Variant)
    'source: https://powerspreadsheets.com/
    'converts String to Long
    'for further information: https://powerspreadsheets.com/vba-string-to-number/

    'convert String to Long
    stringToLong = CLng(myString)
End Function
Effects of executing macro example to convert String to Long

The following image illustrates the results of using the macro example in a worksheet formula. For these purposes:

  • Column A contains a numeric string.

  • Column B contains a worksheet formula that uses the UDF example.

    When String is outside the range of Long, the worksheet formula returns the #VALUE! error. Additionally, if String contains a fraction that’s precisely 0.5, the UDF rounds to the nearest even number.

  • Column C displays the worksheet formula used in column B.

#4: Convert String to Single VBA code to convert String to Single

To convert a string to a number of the Single data type, use the following statement:

CSng(String)
Process followed by VBA to convert String to Single

To convert a string to a number of the Single data type, use the CSng function to convert the String to a number of the Single data type.

VBA statement explanation
  1. Item: CSng.

    • VBA construct: CSng function.

    • Description: The CSng function coerces String to the Single data type.

      CSng is usually able to carry out internationally-aware conversions from the String to the Single data type. In other words, CSng generally recognizes between the different decimal/thousand separators and currency options that depend on your computer’s locale.

      The Single data type can hold floating-point numbers ranging from:

      • -3.402823E38 to -1.401298E-45 for negative values; and

      • 1.401298E-45 to 3.402823E38 for positive values.

        [li]If String is outside the required range, an error occurs.

  2. Item: String.

    • VBA construct: String expression and expression argument of the CSng function.

    • Description: String is the string or numeric expression you convert to the Single data type. If you explicitly declare a variable to represent String, use the Variant data type.
Macro example to convert String to Single

The following macro example, a User-Defined Function, converts a string passed as argument (myString) to Single.

Function stringToSingle(myString As Variant)
    'source: https://powerspreadsheets.com/
    'converts String to Single
    'for further information: https://powerspreadsheets.com/vba-string-to-number/

    'convert String to Single
    stringToSingle = CSng(myString)
End Function
Effects of executing macro example to convert String to Single

The following image illustrates the results of using the macro example in a worksheet formula. For these purposes:

  • Column A contains a numeric string.

  • Column B contains a worksheet formula that uses the UDF example.

    When String is outside the range of Single, the worksheet formula returns the #VALUE! error.

  • Column C displays the worksheet formula used in column B.

#5: Convert String to Double VBA code to convert String to Double

To convert a string to a number of the Double data type, use the following statement:

CDbl(String)
Process followed by VBA to convert String to Double

To convert a string to a number of the Double data type, use the CDbl to convert the String to a number of the Double data type.

VBA statement explanation
  1. Item: CDbl.

    • VBA construct: CDbl function.

    • Description: The CDbl function coerces String to the Double data type.

      CDbl is usually able to carry out internationally-aware conversions from the String to the Double data type. In other words, CDbl generally recognizes between the different decimal/thousand separators and currency options that depend on your computer’s locale.

      The Double data type can hold floating-point numbers ranging from:

      • -1.79769313486231E308 to -4.94065645841247E-324 for negative values; and

      • 4.94065645841247E-324 to 1.79769313486232E308 for positive values.

        [li]If String is outside the required range, an error occurs.

  2. Item: String.

    • VBA construct: String expression and expression argument of the CDbl function.

    • Description: String is the string or numeric expression you convert to the Double data type. If you explicitly declare a variable to represent String, use the Variant data type.
Macro example to convert String to Double

The following macro example, a User-Defined Function, converts a string passed as argument (myString) to Double.

Function stringToDouble(myString As Variant)
    'source: https://powerspreadsheets.com/
    'converts String to Double
    'for further information: https://powerspreadsheets.com/vba-string-to-number/

    'convert String to Double
    stringToDouble = CDbl(myString)
End Function
Effects of executing macro example to convert String to Double

The following image illustrates the results of using the macro example in a worksheet formula. For these purposes:

  • Column A contains a numeric string.

  • Column B contains a worksheet formula that uses the UDF example.

    When String is outside the range of Double, the worksheet formula returns the #VALUE! error.

  • Column C displays the worksheet formula used in column B.

#6: Convert String to Currency VBA code to convert String to Currency

To convert a string to a number of the Currency data type, use the following statement:

CCur(String)
Process followed by VBA to convert String to Currency

To convert a string to a number of the Currency data type, use the CCur to convert the String to a number of the Currency data type.

VBA statement explanation
  1. Item: CCur.

    • VBA construct: CCur function.

    • Description: The CCur function coerces String to the Currency data type.

      CCur is usually able to carry out internationally-aware conversions from the String to the Currency data type. In other words, CCur generally recognizes between the different decimal/thousand separators and currency options that depend on your computer’s locale.

      The Currency data type holds integers scaled by 10,000. This results in Currency holding fixed-point numbers with 15 digits to the left of the decimal point and 4 digits to the right of the decimal point. Therefore, Currency can hold numbers ranging from -922,337,203,685,477.5808 to 922,337,203,685,477.5807. If String is outside this range, an error occurs.

  2. Item: String.

    • VBA construct: String expression and expression argument of the CCur function.

    • Description: String is the string or numeric expression you convert to the Currency data type. If you explicitly declare a variable to represent String, use the Variant data type.
Macro example to convert String to Currency

The following macro example, a User-Defined Function, converts a string passed as argument (myString) to Currency.

Function stringToCurrency(myString As Variant)
    'source: https://powerspreadsheets.com/
    'converts String to Currency
    'for further information: https://powerspreadsheets.com/vba-string-to-number/

    'convert String to Currency
    stringToCurrency = CCur(myString)
End Function
Effects of executing macro example to convert String to Currency

The following image illustrates the results of using the macro example in a worksheet formula. For these purposes:

  • Column A contains a numeric string.

  • Column B contains a worksheet formula that uses the UDF example.

    When String is outside the range of Currency, the worksheet formula returns the #VALUE! error.

  • Column C displays the worksheet formula used in column B.

#7: Convert String to Decimal VBA code to convert String to Decimal

To convert a string to a number of the Decimal data type, use the following statement:

CDec(String)
Process followed by VBA to convert String to Decimal

To convert a string to a number of the Decimal data type, use the CDec function to convert the String to a number of the Decimal data type.

VBA statement explanation
  1. Item:..
Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

In this VBA Tutorial, you learn how to create a clustered or stacked bar chart with macros.

This VBA Tutorial is accompanied by Excel workbooks containing the macros I use in the examples below. You can get immediate access to these example workbooks by clicking on the button below.


Use the following Table of Contents to navigate to the section you’re interested in.

Related VBA and Macro Tutorials

The following VBA and Macro Tutorials may help you better understand and implement the contents below:

  • General VBA constructs and structures:

    • If you’re a beginner, learn how to start working with macros here.

    • Understand essential VBA terms here.

    • Learn how to enable or disable macros here.

    • Learn about the Visual Basic Editor here.

    • Learn how to refer to objects here.

    • Learn how to create references to cell range here.

    • Learn about Sub procedures here.

    • Learn how to work with properties here.

    • Learn how to work with methods here.

    • Learn how to create and assign values to variables here.

    • Learn about data types here.

  • Practical VBA applications and macro examples:

    • Learn how to work with worksheets here.

    • Learn how to find the last row in a worksheet here.

    • Learn how to find the last column in a worksheet here.

    • Learn how to specify the width of a column here.

    • Learn how to check if a cell is empty here.

    • Learn how to delete a sheet here.

  • Tutorials about other useful topics:

    • Learn how to import and consolidate data using Power Query here.

You can find additional VBA and Macro Tutorials in the Archives.

#1: Create an embedded clustered or stacked bar chart (without selecting the source data range) VBA code to create an embedded clustered or stacked bar chart (without selecting the source data range)

To create an embedded clustered or stacked bar chart (without selecting the source data range) using VBA, use a macro with the following statement structure:

Dim myChart As Chart
Set myChart = Worksheet.Shapes.AddChart2(Style:=-1, XlChartType:=ChartType, Left:=ChartDestination.Cells(1).Left, Top:=ChartDestination.Cells(1).Top, Width:=ChartDestination.Width, Height:=ChartDestination.Height, NewLayout:=Boolean).Chart
myChart.SetSourceData Source:=SourceDataRange
Process followed by VBA to create an embedded clustered or stacked bar chart (without selecting the source data range)

To create an embedded clustered or stacked bar chart (without selecting the source data range), follow these steps within your VBA code:

  1. Declare an object variable (myChart) to represent the newly-created clustered or stacked bar chart.

  2. Create a clustered or stacked bar chart with the Shapes.AddChart2 method.

  3. Assign the Chart object representing the newly-created clustered or stacked bar chart to the myChart object variable.

  4. Specify the source data for the newly-created clustered or stacked bar chart with the Chart.SetSourceData method.

VBA statement explanation Line #1: Dim myChart As Chart
  1. Item: Dim.

    • VBA construct: Dim statement.

    • Description: The Dim statement declares the myChart object variable and allocates storage space.

  2. Item: myChart.

    • VBA construct: Object variable.

    • Description: myChart is an object variable of the Chart object data type. The purpose of myChart is to represent a reference to the newly-created clustered or stacked bar chart.

  3. Item: Chart.

    • VBA construct: type part of the Dim statement.

    • Description: Chart is the data type of the declared variable. In other words, myChart is declared as of the Chart object data type.
Line #2: Set myChart = Worksheet.Shapes.AddChart2(Style:=-1, XlChartType:=ChartType, Left:=ChartDestination.Cells(1).Left, Top:=ChartDestination.Cells(1).Top, Width:=ChartDestination.Width, Height:=ChartDestination.Height, NewLayout:=Boolean).Chart
  1. Item: Set.

    • VBA construct: Set statement.

    • Description: Set assigns an object reference to an object variable. For purposes of this macro structure:

      • myChart is the object variable to which an object reference is assigned to.

      • The object reference assigned to myChart is the Chart object representing the newly-created clustered or stacked bar chart, as returned by the Shape.Chart property.

  2. Item: myChart.

    • VBA construct: objectvar part of the Set statement, variable part of the assignment (=) operator, and object variable of the Chart object data type.

    • Description: myChart represents a reference to the newly-created clustered or stacked bar chart. Within the Set statement, myChart is the object variable to which an object reference is assigned to.

  3. Item: =.

    • VBA construct: Assignment operator.

    • Description: The assignment operator assigns a value to a variable. For purposes of this macro structure:

      • myChart is the object variable to which the value is assigned to.

      • The value assigned to myChart is the Chart object representing the newly-created clustered or stacked bar chart, as returned by the Shape.Chart property.

  4. Item: Worksheet.

    • VBA construct: Worksheet object.

    • Description: Worksheet represents the worksheet where you insert the newly-created clustered or stacked bar chart.

      Use constructs such as the Application.ActiveSheet property or the Workbook.Worksheets property to return the appropriate Worksheet object. If you explicitly declare an object variable to represent Worksheet, use the Worksheet object data type.

  5. Item: Shapes.

    • VBA construct: Worksheet.Shapes property.

    • Description: The Worksheet.Shapes property returns a Shapes collection representing all the shapes within Worksheet. Each Shape object within the Shapes collection represents an object in the drawing lawyer of the worksheet.

  6. Item: AddChart2.

    • VBA construct: Shapes.AddChart2 method.

    • Description: The Shapes.AddChart2 method creates a chart. Shapes.AddChart2 returns a Shape object representing the newly-created clustered or stacked bar chart. This Shape object is added to the Shapes collection representing all the shapes within Worksheet.

      The AddChart2 method was introduced in Excel 2013 and isn’t backward compatible. To create a chart in Excel 2007 or Excel 2010 using VBA, use the Shapes.AddChart method. For an explanation and example of how to work with the AddChart method, please refer to the appropriate section below.

  7. Item: Style:=-1.

    • VBA construct: Style parameter of the Shapes.AddChart2 method.

    • Description: The Style parameter of Shapes.AddChart2 specifies the style of the newly-created clustered or stacked bar chart.

      When you set Style to -1 (as in this macro structure), the newly-created chart gets the default style for the applicable clustered or stacked bar chart type.

  8. Item: XlChartType:=ChartType.

    • VBA construct: XlChartType parameter of the Shapes.AddChart2 method.

    • Description: The XlChartType parameter of Shapes.AddChart2 specifies the type of the newly-created clustered or stacked bar chart. You can set XlChartType to any of the built-in constants within the XlChartType enumeration. For purposes of creating a clustered or stacked bar chart, use one of the following built-in constants or values:

      • xlPyramidBarClustered (or 109) to create a clustered pyramid bar chart.

      • xlPyramidBarStacked (or 110) to create a stacked pyramid bar chart.

      • xlPyramidBarStacked100 (or 111) to create a 100% stacked pyramid bar chart.

      • xlBarClustered (or 57) to create a clustered bar chart.

      • xlBarStacked (or 58) to create a stacked bar chart.

      • xlBarStacked100 (or 59) to create a 100% stacked bar chart.

      • xlConeBarClustered (or 102) to create a clustered cone bar chart.

      • xlConeBarStacked (or 103) to create a stacked cone bar chart.

      • xlConeBarStacked100 (or 104) to create a 100% stacked cone bar chart.

      • xlCylinderBarClustered (or 95) to create a clustered cylinder bar chart.

      • xlCylinderBarStacked (or 96) to create a stacked cylinder bar chart.

      • xlCylinderBarStacked100 (or 97) to create a 100% stacked cylinder bar chart.

  9. Item: Left:=ChartDestination.Cells(1).Left.

    • VBA construct: Left parameter of the Shapes.AddChart2 method.

    • Description: The Left parameter of Shapes.AddChart2 specifies the position, in points, of the newly-created clustered or stacked bar chart’s left edge relative to the anchor which, in this case, is the left edge of column A. For purposes of this macro structure, Left’s value is determined as follows:

      • ChartDestination is a Range object representing the cells where you want the newly-created clustered or stacked bar chart to be located.

        You can usually return a Range object with constructs such as the Worksheet.Range, Worksheet.Cells (with Range.Item), Range.Offset or Range.Resize properties. If you explicitly declare an object variable to represent ChartDestination, use the Range object data type.

      • The Range.Cells and Range.Item properties (Cells(1)) return a Range object representing the first (top-left corner) cell within ChartDestination.

      • The Range.Left property returns a value representing the distance, in points, between the left edge of column A and the left edge of ChartDestination.

      Because of the above, “ChartDestination.Cells(1).Left” sets the left edge of the newly-created clustered or stacked bar chart to be at the left edge of ChartDestination.

  10. Item: Top:=ChartDestination.Cells(1).Top.

    • VBA construct: Top parameter of the Shapes.AddChart2 method.

    • Description: The Top parameter of Shapes.AddChart2 specifies the position, in points, of the newly-created clustered or stacked bar chart’s top edge relative to the anchor which, in this case, is the top edge of row 1. For purposes of this macro structure, Top’s value is determined as follows:

      • ChartDestination is a Range object representing the cells where you want the newly-created clustered or stacked bar chart to be located.

        You can usually return a Range object with constructs such as the Worksheet.Range, Worksheet.Cells (with Range.Item), Range.Offset or Range.Resize properties. If you explicitly declare an object variable to represent ChartDestination, use the Range object data type.

      • The Range.Cells and Range.Item properties (Cells(1)) return a Range object representing the first (top-left corner) cell within ChartDestination.

      • The Range.Top property returns a value representing the distance, in points, between the top edge of row 1 and the top edge of ChartDestination.

      Because of the above, “ChartDestination.Cells(1).Top” sets the top edge of the newly-created clustered or stacked bar chart to be at the top edge of ChartDestination.

  11. Item: Width:=ChartDestination.Width.

    • VBA construct: Width parameter of the Shapes.AddChart2 method.

    • Description: The Width parameter of Shapes.AddChart2 specifies the width, in points, of the newly-created clustered or stacked bar chart. For purposes of this macro structure, Width’s value is determined as follows:

      • ChartDestination is a Range object representing the cells where you want the newly-created clustered or stacked bar chart to be located.

        You can usually return a Range object with constructs such as the Worksheet.Range, Worksheet.Cells (with Range.Item), Range.Offset or Range.Resize properties. If you explicitly declare an object variable to represent ChartDestination, use the Range object data type.

      • The Range.Width property returns a value representing the width, in points, of ChartDestination.

      Because of the above, “ChartDestination.Width” sets the width of the newly-created clustered or stacked bar chart to be the width of ChartDestination.

  12. Item: Height:=ChartDestination.Height.

    • VBA construct: Height parameter of the Shapes.AddChart2 method.

    • Description: The Height parameter of Shapes.AddChart2 specifies the height, in points, of the newly-created clustered or stacked bar chart. For purposes of this macro structure, Height’s value is determined as follows:

      • ChartDestination is a Range object representing the cells where you want the newly-created clustered or stacked bar chart to be located.

        You can usually return a Range object with constructs such as the Worksheet.Range, Worksheet.Cells (with Range.Item), Range.Offset or Range.Resize properties. If you explicitly declare an object variable to represent ChartDestination, use the Range object data type.

      • The Range.Height property returns a value representing the height, in points, of ChartDestination.

      Because of the above, “ChartDestination.Height” sets the height of the newly-created clustered or stacked bar chart to be the height of ChartDestination.

  13. Item: NewLayout:=Boolean.
    • VBA construct: NewLayout parameter of the Shapes.AddChart2 method.

    • Description: The NewLayout parameter of Shapes.AddChart2 specifies whether the newly-created clustered or stacked bar chart is inserted by using certain dynamic formatting rules. The 2 main consequences of applying these dynamic formatting rules are that:

      • The chart title is displayed.

      • The legend is displayed only if the bar chart contains multiple series.

      You specify NewLayout’s value as a Boolean (True or False).

      • If you set NewLayout to True, the new dynamic formatting rules apply.

      • If you set NewLayout to False, the new dynamic formatting rules don’t apply.

  14. Item: Chart.

    • VBA construct: Shape.Chart property.

    • Description: The Shape.Chart property returns a Chart object representing the chart contained within the shape. For purposes of this macro structure, the Chart object returned by Shape.Chart represents the newly-created clustered or stacked bar chart.

  15. Item: Worksheet.Shapes.AddChart2(Style:=-1, XlChartType:=ChartType, Left:=ChartDestination.Cells(1).Left, Top:=ChartDestination.Cells(1).Top, Width:=ChartDestination.Width, Height:=ChartDestination.Height, NewLayout:=Boolean).Chart.

    • VBA construct: objectexpression part of the Set statement and value part of the assignment (=) operator.

    • Description: This expression returns a Chart object representing the newly-created clustered or stacked bar chart. This Chart object is assigned to myChart.
Line #3: myChart.SetSourceData Source:=SourceDataRange
  1. Item: myChart.

    • VBA construct: Object variable of the Chart object data type.

    • Description: myChart represents a reference to the newly-created clustered or stacked bar chart.
  2. Item: SetSourceData.

    • VBA construct: Chart.SetSourceData method.

    • Description: The Chart.SetSourceData method sets the source data range for myChart.

  3. Item: Source:=SourceDataRange.

    • VBA construct: Source parameter of the Chart.SetSourceData method.

    • Description: The Source parameter of Chart.SetSourceData specifies the cell range containing the source data for myChart.

      SourceDataRange is a Range object representing the cell range that contains the source data for myChart.

      You can usually return a Range object with constructs such as the Worksheet.Range, Worksheet.Cells (with Range.Item), Range.Offset or Range.Resize properties. If you explicitly declare an object variable to represent SourceDataRange, use the Range object data type.
Macro example to create an embedded clustered or stacked bar chart (without selecting the source data..
Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

In this VBA Tutorial, you learn how to clear cells (including clearing cells totally, their format but not their contents, their contents but not their format, and other similar combinations) with macros.

This VBA Tutorial is accompanied by Excel workbooks containing the macros I use in the examples below. You can get immediate access to these example workbooks by clicking on the button below.


Use the following Table of Contents to navigate to the section you’re interested in.

Related VBA and Macro Tutorials

The following VBA and Macro Tutorials may help you better understand and implement the contents below:

  • General VBA constructs and structures:

    • Learn about important VBA constructs here.

    • Learn how to work with the Visual Basic Editor here.

    • Learn how to work Excel Sub procedures here.

    • Learn about the Excel Object Model, and how to create object references, here.

    • Learn about the Range object, and how to refer to cells, here.

    • Learn how to work with properties here.

    • Learn how to work with methods here.

    • Learn how to declare and work with variables here.

    • Learn about data types here.

    • Learn how to work with the If… Then… Else statement here.

    • Learn how to work with loops here.

  • Practical VBA applications and macro examples:

    • Learn how to work with worksheets using VBA here.

    • Learn how to check if a cell is empty here.

    • Learn how to delete rows here.

    • Learn how to delete blank or empty rows here.

You can find additional VBA and Macro Tutorials in the Archives.

#1: Clear Cell VBA Code to Clear Cell

To clear cells using VBA, use a statement with the following structure:

Cells.Clear
Process Followed by VBA to Clear Cell

VBA Statement Explanation
  1. Item: Cells.

    • VBA Construct: Range object.

    • Description: Range object representing the cells you want to clear.

      You can usually return a Range object with constructs such as the Worksheet.Range, Worksheet.Cells (with the Range.Item), Range.Offset, Range.Resize or Application.ActiveCell properties. If you explicitly declare an object variable to represent Cells, use the Range object data type.

  2. Item: Clear.

    • VBA Construct: Range.Clear method.

    • Description: The Range.Clear method clears the Range object you specify (Cells). Range.Clear clears the entire Range object, including values, formulas and formatting.
Macro Example to Clear Cell

The following macro example clears cells A5 to C9 (myRange) in the worksheet named “Clear Cell” of the workbook containing the macro (ThisWorkbook).

Sub clearCell()

    'Source: https://powerspreadsheets.com/
    'For further information: https://powerspreadsheets.com/excel-vba-clear-cell/

    'declare object variable to hold reference to cells to clear
    Dim myRange As Range

    'identify cells to clear
    Set myRange = ThisWorkbook.Worksheets("Clear Cell").Range("A5:C9")

    'clear cells (including formatting)
    myRange.Clear

End Sub
Effects of Executing Macro Example to Clear Cell

The following images illustrate the results of executing the macro example.

  • Before macro execution: Cells A5 to C9 contain the string “data”, have a light blue fill, and the font is formatted as bold.



  • After macro execution: Cells A5 to C9 (including both data and formatting) are cleared.

#2: Clear Cell Contents and Keep Formatting VBA Code to Clear Cell Contents and Keep Formatting

To clear cell contents (but not formatting) using VBA, use a statement with the following structure:

Cells.ClearContents
Process Followed by VBA to Clear Cell Contents and Keep Formatting

VBA Statement Explanation
  1. Item: Cells.

    • VBA Construct: Range object.

    • Description: Range object representing the cells where you want to clear the contents but not the formatting.

      You can usually return a Range object with constructs such as the Worksheet.Range, Worksheet.Cells (with the Range.Item), Range.Offset, Range.Resize or Application.ActiveCell properties. If you explicitly declare an object variable to represent Cells, use the Range object data type.

  2. Item: ClearContents.

    • VBA Construct: Range.ClearContents method.

    • Description: The Range.ClearContents method clears values and formulas from the Range object you specify (Cells). Range.ClearContents leaves formatting intact.
Macro Example to Clear Cell Contents and Keep Formatting

The following macro example clears the contents (but not the formatting) of cells A10 to C14 (myRange) in the worksheet named “Clear Cell” of the workbook containing the macro (ThisWorkbook).

Sub clearCellContentsKeepFormatting()

    'Source: https://powerspreadsheets.com/
    'For further information: https://powerspreadsheets.com/excel-vba-clear-cell/

    'declare object variable to hold reference to cells to clear contents but not formatting
    Dim myRange As Range

    'identify cells to clear contents and keep formatting
    Set myRange = ThisWorkbook.Worksheets("Clear Cell").Range("A10:C14")

    'clear cell contents (but not formatting)
    myRange.ClearContents

End Sub
Effects of Executing Macro Example to Clear Cell Contents and Keep Formatting

The following images illustrate the results of executing the macro example.

  • Before macro execution: Cells A10 to C14 contain the string “data”, have a light gold fill, and the font is formatted as bold.



  • After macro execution: Cell contents of cells A10 to C14 are cleared. The formatting is kept.

#3: Clear Cell Formatting VBA Code to Clear Cell Formatting

To clear cell formatting using VBA, use a statement with the following structure:

Cells.ClearFormats
Process Followed by VBA to Clear Cell Formatting

VBA Statement Explanation
  1. Item: Cells.

    • VBA Construct: Range object.

    • Description: Range object representing the cells where you want to clear cell formatting.

      You can usually return a Range object with constructs such as the Worksheet.Range, Worksheet.Cells (with the Range.Item), Range.Offset, Range.Resize or Application.ActiveCell properties. If you explicitly declare an object variable to represent Cells, use the Range object data type.

  2. Item: ClearFormats.

    • VBA Construct: Range.ClearFormats method.

    • Description: The Range.ClearFormats method clears the formatting of the Range object you specify (Cells). Range.ClearFormats doesn’t clear values or formulas.
Macro Example to Clear Cell Formatting

The following macro clears the cell formatting of cells A15 to C19 (myRange) of the worksheet named “Clear Cell” in the workbook containing the macro (ThisWorkbook).

Sub clearCellFormatting()

    'Source: https://powerspreadsheets.com/
    'For further information: https://powerspreadsheets.com/excel-vba-clear-cell/

    'declare object variable to hold reference to cells to clear formatting
    Dim myRange As Range

    'identify cells to clear formatting
    Set myRange = ThisWorkbook.Worksheets("Clear Cell").Range("A15:C19")

    'clear cell formatting
    myRange.ClearFormats

End Sub
Effects of Executing Macro Example to Clear Cell Formatting

The following images illustrate the results of executing the macro example.

  • Before macro execution: Cells A15 to C19 contain the string “data”, have a light green fill, and the font is formatted as bold.



  • After macro execution: The formatting of cells A15 to C19 is cleared.

#4: Clear Cell Color VBA Code to Clear Cell Color

To clear cell color using VBA, use a statement with the following structure:

Cells.Interior.Color = xlColorIndexNone
Process Followed by VBA to Clear Cell Color

VBA Statement Explanation
  1. Item: Cells.

    • VBA Construct: Range object.

    • Description: Range object representing the cells where you want to clear cell formatting.

      You can usually return a Range object with constructs such as the Worksheet.Range, Worksheet.Cells (with the Range.Item), Range.Offset, Range.Resize or Application.ActiveCell properties. If you explicitly declare an object variable to represent Cells, use the Range object data type.

  2. Item: Interior.

    • VBA Construct: Range.Interior property and Interior object.

    • Description: The Range. Interior property returns an Interior object representing the interior of the cell range you specify (Cells).

  3. Item: Color.

    • VBA Construct: Interior.Color property.

    • Description: The Interior.Color property allows you to set the primary color of the cell interior represented by the Interior object returned by Range.Interior.

  4. Item: =.

    • VBA Construct: Assignment operator.

    • Description: The assignment operator assigns the xlColorIndexNone value to the Interior.Color property.

  5. Item: xlColorIndexNone.

    • VBA Construct: xlColorIndexNone constant.

    • Description: The xlColorIndexNone constant specifies that the color of the Interior object representing the interior of Cells is none.
Macro Example to Clear Cell Color

The following macro clears the cell color of cells A20 to C24 (myRange) in the worksheet named “Clear Cell” of the workbook containing the macro (ThisWorkbook).

Sub clearCellColor()

    'Source: https://powerspreadsheets.com/
    'For further information: https://powerspreadsheets.com/excel-vba-clear-cell/

    'declare object variable to hold reference to cells to clear cell color
    Dim myRange As Range

    'identify cells to clear cell color
    Set myRange = ThisWorkbook.Worksheets("Clear Cell").Range("A20:C24")

    'clear cell color
    myRange.Interior.Color = xlColorIndexNone

End Sub
Effects of Executing Macro Example to Clear Cell Color

The following images illustrate the results of executing the macro example.

  • Before macro execution: Cells A20 to C24 contain the string “data”, have a light orange fill, and the font is formatted as bold.




  • After macro execution: The fill color of cells A20 to C24 is cleared.

#5: Clear Cells with Zero VBA Code to Clear Cells with Zero

To clear cells with zero within a cell range using VBA, use a macro with the following statement structure:

For Each Cell In Range
    If Cell.Value = myValue Then Cell.Clear
Next Cell
Process Followed by VBA to Clear Cells with Zero

VBA Statement Explanation Lines #1 and #3: For Each Cell In Range | Next Cell
  1. Item: For Each… In… Next.

    • VBA Construct: For Each… Next statement.

    • Description: The For Each… Next statement repeats the statement within the loop (line #2) for each element (Cell) in the cell range (Range) you want to search for zeroes in.

  2. Item: Cell.

    • VBA Construct: Element of the For Each… Next statement and object variable of the Range object data type.

    • Description: The Element of the For Each… Next statement is an object variable used to iterate through the elements (Cell) of the cell range (Range) you want to search for zeroes in.

      If you explicitly declare an object variable to represent Cell, use the Range object data type.

  3. Item: Range.

    • VBA Construct: Group of the For Each… Next statement and Range object.

    • Description: The For Each… Next statement repeats the statements within the loop (line #2) for each element (Cell) in the Group (Range). Range is a Range object representing the cells where you want to search for zeroes.

      You can usually return a Range object with constructs such as the Worksheet.Range, Worksheet.Cells (with the Range.Item), Range.Offset, Range.Resize or Application.ActiveCell properties. If you explicitly declare an object variable to represent Range, use the Range object data type.
Line #2: If Cell.Value = myValue Then Cell.Clear
  1. Item: If… Then.

    • VBA Construct: If… Then… Else statement.

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

In this VBA Tutorial, you learn how to create named ranges (for different ranges and with different scopes) with macros.

This VBA Tutorial is accompanied by Excel workbooks containing the macros I use in the examples below. You can get immediate free access to these example workbooks by clicking on the button below.

Use the following Table of Contents to navigate to the section you’re interested in.

Related VBA and Macro Tutorials

The following VBA and Macro Tutorials may help you better understand and implement the contents below:

  • General VBA constructs and structures:

    • Learn about commonly-used VBA terms here.

    • Learn about working with Sub procedures here.

    • Learn about the Excel VBA Object Model here.

    • Learn about working with VBA methods here.

    • Learn how to identify cell ranges here.

    • Learn how to declare and work with variables here.

    • Learn about VBA data types here.

    • Learn about A1 and R1C1-style references here.

  • Practical VBA applications and macro examples:

    • Learn how to work with worksheets here.

    • Learn several ways to find the last row with data here.

    • Learn several ways to find the last column with data here.

You can find additional VBA and Macro Tutorials in the Archives.

#1: Create Named Range VBA Code to Create Named Range

To create a named range using VBA, use a statement with the following structure:

Scope.Names.Add Name:=RangeName, RefersTo:=NamedRange
Process Followed by VBA to Create Named Range

VBA Statement Explanation
  1. Item: Scope.

    • VBA Construct: Workbook or Worksheet object.

    • Description: Scope of the named range you create. The scope of a named range can generally be 1 of the following:

      • Workbook. In this case, Scope must represent a Workbook object. For these purposes, you can use VBA constructs such as the ActiveWorkbook property, the ThisWorkbook property or the Application.Workbooks property.

      • Worksheet. In this case, Scope must represent a Worksheet object. For these purposes, you can use VBA constructs such as the ActiveSheet property or the Workbook.Worksheets property.

        For a more detailed description of how to create a named range with worksheet scope, please refer to the appropriate section below.

  2. Item: Names.

    • VBA Construct: Workbook.Names property (when Scope is a Workbook object) or Worksheet.Names property (when scope is a Worksheet object).

    • Description: The Names property returns a Names collection representing all the names in the workbook (when Scope is a Workbook object) or worksheet (when Scope is a Worksheet object) represented by Scope. The Names collection returned by the Workbook.Names property includes worksheet-specific names.

  3. Item: Add.

    • VBA Construct: Names.Add method.

    • Description: The Names.Add method sets a new name for a cell range.

  4. Item: Name:=RangeName.

    • VBA Construct: Name parameter of the Names.Add method.

    • Description: The Name parameter of the Names.Add method allows you to specify the name of the named range you specify. Consider the following usual requirements when specifying RangeName:

      • RangeName must start with a letter or underscore.

      • Certain characters aren’t allowed. For example, RangeName can’t include spaces.

      • RangeName can’t conflict with an existing name.

      • RangeName can’t be formatted as a cell reference. For example, names such as “ABC1” aren’t allowed.

      • If you want to specify the Name parameter using localized text, (i) work with the NameLocal parameter of the Names.Add method, and (ii) omit the Name parameter of the Names.Add method.

      If you explicitly declare a variable to represent RangeName, use the String data type.

  5. Item: RefersTo:=NamedRange.

    • VBA Construct: RefersTo parameter of the Names.Add method.

    • Description: The RefersTo parameter of the Names.Add method allows you to specify the cell range to which the name refers to. The RefersTo parameter is of the Variant data type. Therefore, you commonly specify NamedRange as one of the following:

      • A Range object.

      • A string that uses the A1-style notation.

      When working with the Names.Add method, you can also specify the cell range to which the name refers to using the following parameters.

      • RefersToLocal: Allows you to specify the cell range in localized text using the A1-style notation.

      • RefersToR1C1: Allows you to specify the cell range in R1C1-style notation.

      • RefersToR1C1Local: Allows you to specify the cell range in localized text using the R1C1-style notation.

      When working with one of the 4 parameters I refer to (RefersTo, RefersToLocal, RefersToR1C1, or RefersToR1C1Local), specify that single parameter and omit the others.

      If you explicitly declare a variable to represent NamedRange, use the Range object data type (if specifying NamedRange as a Range object) or the String data type (if specifying NamedRange as a string).

Macro Example to Create Named Range

The following macro example creates a named range with workbook scope (ThisWorkbook) by setting the name of the cell range composed of cells A5 to C10 (myNamedRange) of the Named Range worksheet (myWorksheet) to “namedRange” (myRangeName).

Sub createNamedRange()

    'Source: https://powerspreadsheets.com/
    'For further information: https://powerspreadsheets.com/vba-create-named-range/

    'declare object variables to hold references to worksheet containing cell range, and cell range itself
    Dim myWorksheet As Worksheet
    Dim myNamedRange As Range

    'declare variable to hold defined name
    Dim myRangeName As String

    'identify worksheet containing cell range, and cell range itself
    Set myWorksheet = ThisWorkbook.Worksheets("Named Range")
    Set myNamedRange = myWorksheet.Range("A5:C10")

    'specify defined name
    myRangeName = "namedRange"

    'create named range with workbook scope. Defined name and cell range are as specified
    ThisWorkbook.Names.Add Name:=myRangeName, RefersTo:=myNamedRange

End Sub
Effects of Executing Macro Example to Create Named Range

The following image illustrates the results of executing the macro example. The cell range containing cells A5 to C10 is named as “namedRange”.

#2: Create Named Range with Worksheet Scope VBA Code to Create Named Range with Worksheet Scope

To create a named range with worksheet scope using VBA, use a statement with the following structure:

Worksheet.Names.Add Name:=RangeName, RefersTo:=NamedRangeWorksheet
Process Followed by VBA to Create Named Range with Worksheet Scope

VBA Statement Explanation
  1. Item: Worksheet.

    • VBA Construct: Worksheet object.

    • Description: You can use VBA constructs such as the ActiveSheet property or the Workbook.Worksheets property to return a Worksheet object representing the worksheet to which you want to limit the scope of the named range.

  2. Item: Names.

    • VBA Construct: Worksheet.Names property.

    • Description: The Worksheet.Names property returns a Names collection representing all the names in Worksheet.

  3. Item: Add.

    • VBA Construct: Names.Add method.

    • Description: The Names.Add method sets a new name for a cell range.

  4. Item: Name:=RangeName.

    • VBA Construct: Name parameter of the Names.Add method.

    • Description: The Name parameter of the Names.Add method allows you to specify the name of the named range you specify. Consider the following usual requirements when specifying RangeName:

      • RangeName must start with a letter or underscore.

      • Certain characters aren’t allowed. For example, RangeName can’t include spaces.

      • RangeName can’t conflict with an existing name.

      • RangeName can’t be formatted as a cell reference. For example, names such as “ABC1” aren’t allowed.

      • If you want to specify the Name parameter using localized text, (i) work with the NameLocal parameter of the Names.Add method, and (ii) omit the Name parameter of the Names.Add method.

      If you explicitly declare a variable to represent RangeName, use the String data type.

  5. Item: RefersTo:=NamedRangeWorksheet.

    • VBA Construct: RefersTo parameter of the Names.Add method.

    • Description: The RefersTo parameter of the Names.Add method allows you to specify the cell range to which the name refers to. The RefersTo parameter is of the Variant data type. Therefore, you commonly specify NamedRangeWorksheet as one of the following:

      • A Range object.

      • A string that uses the A1-style notation.

      When working with the Names.Add method, you can also specify the cell range to which the name refers to using the following parameters.

      • RefersToLocal: Allows you to specify the cell range in localized text using the A1-style notation.

      • RefersToR1C1: Allows you to specify the cell range in R1C1-style notation.

      • RefersToR1C1Local: Allows you to specify the cell range in localized text using the R1C1-style notation.

      When working with one of the 4 parameters I refer to (RefersTo, RefersToLocal, RefersToR1C1, or RefersToR1C1Local), specify that single parameter and omit the others.

      If you explicitly declare a variable to represent NamedRangeWorksheet, use the Range object data type (if specifying NamedRangeWorksheet as a Range object) or the String data type (if specifying NamedRangeWorksheet as a string).

Macro Example to Create Named Range with Worksheet Scope

The following macro example creates a named range with worksheet scope (myWorksheet) by setting the name of the cell range composed of cells D5 to F10 (myNamedRangeWorksheet) of the Named Range worksheet (myWorksheet) to “namedRangeWorksheet” (myRangeName).

Sub createNamedRangeWorksheetScope()

    'Source: https://powerspreadsheets.com/
    'For further information: https://powerspreadsheets.com/vba-create-named-range/

    'declare object variables to hold references to worksheet containing cell range, and cell range itself
    Dim myWorksheet As Worksheet
    Dim myNamedRangeWorksheet As Range

    'declare variable to hold defined name
    Dim myRangeName As String

    'identify worksheet containing cell range, and cell range itself
    Set myWorksheet = ThisWorkbook.Worksheets("Named Range")
    Set myNamedRangeWorksheet = myWorksheet.Range("D5:F10")

    'specify defined name
    myRangeName = "namedRangeWorksheet"

    'create named range with worksheet scope. Defined name and cell range are as specified
    myWorksheet.Names.Add Name:=myRangeName, RefersTo:=myNamedRangeWorksheet

End Sub
Effects of Executing Macro Example to Create Named Range with Worksheet Scope

The following image illustrates the results of executing the macro example. The cell range containing cells D5 to F10 is named as “namedRangeWorksheet”.

#3: Create Named Range from Selection VBA Code to Create Named Range from Selection

To create a named range from the current selection using VBA, use a statement with the following structure:

Scope.Names.Add Name:=RangeName, RefersTo:=Selection
Process Followed by VBA to Create Named Range from Selection

VBA Statement Explanation from Selection
  1. Item: Scope.

    • VBA Construct: Workbook or Worksheet object.

    • Description: Scope of the named range you create. The scope of a named range can generally be 1 of the following:

      • Workbook. In this case, Scope must represent a Workbook object. For these purposes, you can use VBA constructs such as the ActiveWorkbook property, the ThisWorkbook property or the Application.Workbooks property.

      • Worksheet. In this case, Scope must represent a Worksheet object. For these purposes, you can use VBA constructs such as the ActiveSheet property.

        For a more detailed description of how to create a named range with worksheet scope, please refer to the appropriate section above.

  2. Item: Names.

    • VBA Construct: Workbook.Names property (when Scope is a Workbook object) or Worksheet.Names property (when scope is a Worksheet object).

    • Description: The Names property returns a Names collection representing all the names in the workbook (when Scope is a Workbook object) or worksheet (when Scope is a Worksheet object) represented by Scope. The Names collection returned by the Workbook.Names property includes worksheet-specific names.

  3. Item: Add.

    • VBA Construct: Names.Add method.

    • Description: The Names.Add method sets a new name for a cell range.

  4. Item: Name:=RangeName.

    • VBA Construct: Name parameter of the Names.Add method.

    • Description: The Name parameter of the Names.Add method allows you to specify the name of the named range you specify. Consider the following usual requirements when specifying RangeName:

      • RangeName must start with a letter or underscore.

      • Certain characters aren’t allowed. For example, RangeName can’t include spaces.

      • RangeName can’t conflict with an existing name.

      • RangeName can’t be formatted as a cell reference. For example, names such as “ABC1” aren’t allowed.

      • If you want to specify the Name parameter using localized text, (i) work with the NameLocal parameter of the Names.Add method, and (ii) omit the Name parameter of the Names.Add method.

      If you explicitly declare a variable to represent RangeName, use the String data type.

  5. Item: RefersTo:=Selection.

    • VBA Construct: RefersTo parameter of the Names.Add method and Selection property.

    • Description: The RefersTo parameter of the Names.Add method allows you to specify the cell range to which the name refers to.

      The Selection property returns a Range object representing the current selected cells. This is the cell range you name.
Macro Example to Create Named Range from Selection

The following macro example creates a named range with workbook scope (ThisWorkbook) by setting the name of the selection (Selection) to “namedRangeFromSelection” (myRangeName).

Sub createNamedRangeFromSelection()

    'Source: https://powerspreadsheets.com/
    'For further information: https://powerspreadsheets.com/vba-create-named-range/

    'declare variable to hold defined name
    Dim myRangeName As String

    'specify defined name
    myRangeName = "namedRangeFromSelection"

    'create named range with workbook scope. Defined name is as specified. Cell range is the selection
    ThisWorkbook.Names.Add Name:=myRangeName, RefersTo:=Selection

End Sub
Effects of Executing Macro Example to Create Named Range from Selection

The following image illustrates the results of executing the macro example. The current selection, containing cells G5 to I10 is named as “namedRangeFromSelection”.

#4: Create Named Range (Dynamic) VBA Code to Create Named..
Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

In this VBA Tutorial, you learn how to delete sheets in Excel (in different ways) with macros.

This VBA Tutorial is accompanied by Excel workbooks containing the data and macros I use in the examples below. You can get immediate free access to these example workbooks by clicking the button below.


Use the following Table of Contents to navigate to the section you’re interested in.

Related VBA and Macro Tutorials

The following VBA and macro tutorials may help you better understand and implement the contents below:

  • General VBA constructs and structures:

    • Learn about commonly-used VBA terms here.

    • Learn how to work with the Visual Basic Editor (VBE) here.

    • Learn how to create and work with VBA Sub procedures here.

    • Learn how to work with object methods here.

    • Learn how to declare and work with variables here.

    • Learn how to work with data types here.

    • Learn how to work with arrays here.

  • Practical VBA applications and macro examples:

    • Learn other operations you can carry out when working with Excel worksheets here.

You can find additional VBA and Macro Tutorials in the Archives.

#1: Delete Sheet by Position VBA Code to Delete Sheet by Position

To delete a sheet by its position using VBA, use a statement with the following structure:

Sheets(SheetIndex).Delete
Process Followed by VBA Code to Delete Sheet by Position

VBA Statement Explanation
  1. Item: Sheets.

    • VBA Construct: Workbook.Sheets property.

    • Description: The Workbook.Sheets property returns a Sheets collection representing all the sheets within the workbook you deal with. Identify a single object from this Sheets collection by specifying the appropriate index number (SheetIndex).

      When deleting a worksheet, you can work with the Workbook.Worksheets property. Workbook.Worksheets represents a Sheets collection representing all worksheets within the workbook you deal with.

      When deleting a chart sheet, you can work with the Workbook.Charts property. Workbook.Charts returns a Sheets collection representing all chart sheets within the workbook you deal with.

  2. Item: SheetIndex.

    • VBA Construct: Index parameter/number of the sheet you want to delete.

    • Description: The Index parameter/number of a sheet allows you to identify a single object (worksheet or chart sheet) from the Sheets collection you work with.

      The Index parameter/number represents the position of the sheet, worksheet or chart sheet in the tab bar of the workbook you deal with, from left to right. For example, 1 is the first (leftmost) sheet/worksheet/chart sheet.

      When specifying the Index parameter/number, consider the following:

      • The count usually includes hidden sheets/worksheets/chart sheets as well.

      • If you’re working with the Workbook.Worksheets property, the count includes worksheets but not chart sheets.

      • If you’re working with the Workbook.Charts property, the count includes chart sheets but not worksheets.

      If you explicitly declare a variable to represent SheetIndex, use the Long data type.

  3. Item: Delete.

    • VBA Construct: Worksheet.Delete method or Chart.Delete method.

    • Description: The Delete method deletes the object (worksheet or chart sheet) you identify with Sheets(SheetIndex).

      When you delete a sheet with the Delete method, Excel usually displays (by default) a dialog box asking the user to confirm the deletion. Please refer to the appropriate section below to delete a sheet with no prompt.
Macro Example to Delete Sheet by Position

The following macro deletes the first sheet (Sheets(mySheetIndex)) in the workbook where the macro is stored (ThisWorkbook). The macro suppresses the prompt that asks the user to confirm the sheet deletion (Application.DisplayAlerts = False).

Sub deleteSheet()

    'Source: https://powerspreadsheets.com/
    'For further information: https://powerspreadsheets.com/excel-vba-delete-sheet/

    'declare variable to hold index number of sheet you want to delete
    Dim mySheetIndex As Long

    'specify index number of sheet you want to delete
    mySheetIndex = 1

    'suppress the dialog box that asks the user to confirm the sheet deletion
    Application.DisplayAlerts = False

    'identify sheet you want to delete, and delete it
    ThisWorkbook.Sheets(mySheetIndex).Delete

    're-enable the display of alerts and messages
    Application.DisplayAlerts = True

End Sub
Effects of Executing Macro Example to Delete Sheet by Position

The following GIF illustrates the results of executing the macro example. The first sheet in the workbook (Sheet1) is deleted.

#2: Delete Active Sheet VBA Code to Delete Active Sheet

To delete the active sheet with VBA, use a statement with the following structure:

ActiveSheet.Delete
Process Followed by VBA Code to Delete Active Sheet

VBA Statement Explanation
  1. Item: ActiveSheet.

    • VBA Construct: Application.ActiveSheet property.

    • Description: The Application.ActiveSheet property returns an object representing the active sheet.

  2. Item: Delete.

    • VBA Construct: Worksheet.Delete method or Chart.Delete method.

    • Description: The Delete method deletes the object (worksheet or chart sheet) returned by ActiveSheet (the active sheet).

      When you delete a sheet with the Delete method, Excel usually displays (by default) a dialog box asking the user to confirm the deletion. Please refer to the appropriate section below to delete a sheet with no prompt.
Macro Example to Delete Active Sheet

The following macro deletes the active sheet (ActiveSheet). The macro suppresses the prompt that asks the user to confirm the sheet deletion (Application.DisplayAlerts = False).

Sub deleteActiveSheet()

    'Source: https://powerspreadsheets.com/
    'For further information: https://powerspreadsheets.com/excel-vba-delete-sheet/

    'suppress the dialog box that asks the user to confirm the sheet deletion
    Application.DisplayAlerts = False

    'identify active sheet, and delete it
    ActiveSheet.Delete

    're-enable the display of alerts and messages
    Application.DisplayAlerts = True

End Sub
Effects of Executing Macro Example to Delete Active Sheet

The following GIF illustrates the results of executing the macro example. The active sheet (Sheet2) is deleted.

#3: Delete Sheet by Name VBA Code to Delete Sheet by Name

To delete a sheet by name using VBA, use a statement with the following structure:

Sheets(SheetName).Delete
Process Followed by VBA Code to Delete Sheet by Name

VBA Statement Explanation
  1. Item: Sheets.

    • VBA Construct: Workbook.Sheets property.

    • Description: The Workbook.Sheets property returns a Sheets collection representing all the sheets within the workbook you deal with. Identify a single object from this Sheets collection by specifying the appropriate name (SheetName).

      When deleting a worksheet, you can work with the Workbook.Worksheets property. Workbook.Worksheets represents a Sheets collection representing all worksheets within the workbook you deal with.

      When deleting a chart sheet, you can work with the Workbook.Charts property. Workbook.Charts returns a Sheets collection representing all chart sheets within the workbook you deal with.

  2. Item: SheetName.

    • VBA Construct: Name of the sheet you want to delete.

    • Description: The name of a sheet allows you to identify a single object (worksheet or chart sheet) from the Sheets collection you work with.

      For these purposes, the sheet name is that displayed in the tab of the worksheet or chart sheet. If you explicitly declare a variable to represent SheetName, use the String data type.

  3. Item: Delete.

    • VBA Construct: Worksheet.Delete method or Chart.Delete method.

    • Description: The Delete method deletes the object (worksheet or chart sheet) you identify with Sheets(SheetName).

      When you delete a sheet with the Delete method, Excel usually displays (by default) a dialog box asking the user to confirm the deletion. Please refer to the appropriate section below to delete a sheet with no prompt.
Macro Example to Delete Sheet by Name

The following macro deletes the sheet named “delete Sheet” (Sheets(mySheetName)) in the workbook where the macro is stored (ThisWorkbook). The macro suppresses the prompt that asks the user to confirm the sheet deletion (Application.DisplayAlerts = False).

Sub deleteSheetByName()

    'Source: https://powerspreadsheets.com/
    'For further information: https://powerspreadsheets.com/excel-vba-delete-sheet/

    'declare variable to hold name of sheet you want to delete
    Dim mySheetName As String

    'specify name of sheet you want to delete
    mySheetName = "delete sheet"

    'suppress the dialog box that asks the user to confirm the sheet deletion
    Application.DisplayAlerts = False

    'identify sheet you want to delete, and delete it
    ThisWorkbook.Sheets(mySheetName).Delete

    're-enable the display of alerts and messages
    Application.DisplayAlerts = True

End Sub
Effects of Executing Macro Example to Delete Sheet by Name

The following GIF illustrates the results of executing the macro example. The sheet named “delete sheet” is deleted.

#4: Delete Sheet Without Prompt or Warning VBA Code to Delete Sheet Without Prompt or Warning

To delete a sheet without Excel displaying the usual prompt (warning) with VBA, use a macro with the following statement structure:

Application.DisplayAlerts = False
Sheets(SheetName).Delete
Application.DisplayAlerts = True
Process Followed by VBA Code to Delete Sheet Without Prompt or Warning

VBA Statement Explanation Lines #1 and #3: Application.DisplayAlerts = False | Application.DisplayAlerts = True
  1. Item: Application.DisplayAlerts.

    • VBA Construct: Application.DisplayAlerts property.

    • Description: The Application.DisplayAlerts property allows you to specify whether Excel displays alerts and messages while the macro is running. When you delete a sheet, the main alert that Excel usually displays (and you want to handle) is the dialog box that prompts the user to confirm the sheet deletion.



      The default value of the Application.DisplayAlerts property is True. In such cases, Excel displays the dialog box prompting the user to confirm the sheet deletion.

  2. Item: False.

    • VBA Construct: New property value of Application.DisplayAlerts property.

    • Description: When you delete a sheet, you can suppress the dialog box that prompts the user to confirm the sheet deletion, by setting the Application.DisplayAlerts property to False.

      When you set Application.DisplayAlerts to False and Excel requires a response, Excel chooses the default response. When you delete a sheet, the default response to the dialog box prompting the user to confirm the sheet deletion is “Delete”. This results in Excel deleting the sheet.

  3. Item: True.

    • VBA Construct: New property value of Application.DisplayAlerts property.

    • Description: The default value of the Application.DisplayAlerts property is True. This results in Excel displaying alerts and messages while a macro is running.

      Generally, when you set Application.DisplayAlerts to False, Excel sets the property back to True upon finishing macro execution. Since there are exceptions (such as executing cross-process code), you can explicitly set Application.DisplayAlerts back to True after deleting the sheet.
Line #2: Sheets(SheetName).Delete
  1. Item: Sheets.

    • VBA Construct: Workbook.Sheets property.

    • Description: The Workbook.Sheets property returns a Sheets collection representing all the sheets within the workbook you deal with. Identify a single object from this Sheets collection by specifying the appropriate name (SheetName).

      When deleting a worksheet, you can work with the Workbook.Worksheets property. Workbook.Worksheets represents a Sheets collection representing all worksheets within the workbook you deal with.

      When deleting a chart sheet, you can work with the Workbook.Charts property. Workbook.Charts returns a Sheets collection representing all chart sheets within the workbook you deal with.

  2. Item: SheetName.

    • VBA Construct: Name of the sheet you want to delete.

    • Description: The name of a sheet allows you to identify a single object (worksheet or chart sheet) from the Sheets collection you work with.

      For these purposes, the sheet name is that displayed in the tab of the worksheet or chart sheet. If you explicitly declare a variable to represent SheetName, use the String data type.

  3. Item: Delete.

    • VBA Construct: Worksheet.Delete method or Chart.Delete method.

    • Description: The Delete method deletes the object (worksheet or chart sheet) you identify with Sheets(SheetName).
Macro Example to Delete Sheet Without Prompt or Warning

The following macro deletes the sheet named “delete sheet no prompt” (Sheets(mySheetName)) in the workbook where the macro is stored (ThisWorkbook) without displaying the prompt that asks the user to confirm the sheet deletion (Application.DisplayAlerts = False).

Sub deleteSheetNoPrompt()

    'Source: https://powerspreadsheets.com/
    'For further information: https://powerspreadsheets.com/excel-vba-delete-sheet/

    'declare variable to hold name of sheet you want to delete
    Dim mySheetName As String

    'specify name of sheet you want to delete
    mySheetName = "delete sheet no prompt"

    'suppress the dialog box that asks the user to confirm the sheet deletion
    Application.DisplayAlerts = False

    'identify sheet you want to delete, and delete it
    ThisWorkbook.Sheets(mySheetName).Delete

    're-enable the display of alerts and messages
    Application.DisplayAlerts = True

End Sub
Effects of Executing Macro Example to Delete Sheet Without Prompt or Warning

The following GIF illustrates the results of executing the macro example. The sheet named “delete sheet no prompt” is deleted without a prompt or warning.

#5: Delete Sheet if it Exists VBA Code to Delete Sheet if it Exists

To delete a sheet if it exists with VBA, use a macro with the following statement structure:

On Error Resume Next
Sheets(SheetName).Delete
On Error GoTo 0
Process Followed by VBA Code to Delete Sheet if it Exists

VBA Statement Explanation Line #1: On Error Resume Next
  1. Item: On Error Resume Next.

    • VBA Construct: On Error Resume Next statement.

    • Description: The On Error Resume Next statement specifies that, if an error occurs, control goes to the statement immediately following the statement where the error occurs. Execution continues at that statement that follows that where the error occurs.

      “Sheets(SheetName).Delete” usually returns run-time error 9 (subscript out of range) when the sheet identified by Sheets(SheetName) doesn’t exist. Without the On Error Resume Next statement, such error results in Excel displaying an error message and stopping macro execution.

      Therefore:

      • If the sheet named SheetName exists, Excel deletes the sheet as specified by “Sheets(SheetName).Delete”.

      • If the sheet named SheetName..
Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

In this VBA Tutorial, you learn how to check if a cell or range is empty.

This VBA Tutorial is accompanied by an Excel workbook containing the data and macros I use in the examples below. You can get immediate free access to this example workbook by clicking the button below.

Use the following Table of Contents to navigate to the section you’re interested in.

Related VBA and Macro Tutorials

The following VBA and Macro Tutorials may help you better understand and implement the contents below:

  • Learn about commonly-used VBA terms here.

  • Learn about the Excel Object Model and how to refer to objects here.

  • Learn how to create references to cell ranges here.

  • Learn how to declare and work with variables here.

  • Learn about data types here.

  • Learn how to work with worksheet functions within VBA here.

  • Learn how to use the If… Then… Else statement here.

You can find additional VBA and Macro Tutorials in the Archives.

#1: Check if Cell is Empty VBA Code to Check if Cell is Empty

To check if a cell is empty with VBA, use a macro with the following statement structure:

If IsEmpty(Cell) Then
    StatementsIfCellIsEmpty
Else
    StatementsIfCellIsNotEmpty
End If
Process Followed by VBA Code to Check if Cell is Empty

VBA Statement Explanation Line #1: If IsEmpty(Cell) Then
  1. Item: If… Then.

    • VBA Construct: Opening statement of If… Then… Else statement.

    • Description: The If… Then… Else statement conditionally executes a group of statements depending on the value of an expression. For these purposes:

      • The If… Then… Else statement tests the specified condition (IsEmpty(Cell)).

      • If the condition is met and returns True: StatementsIfCellIsEmpty are executed.

      • If the condition isn’t met and returns False: StatementsIfCellIsNotEmpty are executed.

  2. Item: IsEmpty(…).

    • VBA Construct: IsEmpty function.

    • Description: Generally, the IsEmpty function indicates whether a variable has been initialized. Nonetheless, you can also use IsEmpty to check if a cell is empty.

      The IsEmpty function:

      • Takes one parameter (expression) of the Variant data type. Within this macro structure, the parameter is a Range object (Cell).

      • Returns True if the variable is uninitialized or explicitly set to Empty. Otherwise, IsEmpty returns False.

  3. Item: Cell.

    • VBA Construct: Range object.

    • Description: Range object representing the cell you work with.

      You can usually return a Range object with constructs such as the Worksheet.Range, Worksheet.Cells (with the Range.Item) or Range.Offset properties. If you explicitly declare an object variable to represent Cell, use the Range object data type.

  4. Item: IsEmpty(Cell).

    • VBA Construct: Condition of If… Then… Else statement.

    • Description: This condition is an expression that evaluates to True or False. The IsEmpty function (IsEmpty(Cell)) returns True or False, as follows:

      • True: Cell is empty.

      • False: Cell is not empty.
Line #2: StatementsIfCellIsEmpty
  1. Item: StatementsIfCellIsEmpty.

    • VBA Construct: Statements within If… Then… Else statement.

    • Description: One or more VBA statements that are executed if the condition tested in the opening statement of the If… Then… Else statement (IsEmpty(Cell)) returns True. Within this macro structure, IsEmpty(Cell) returns True if Cell is empty.
Line #3: Else
  1. Item: Else.

    • VBA Construct: Else clause of If… Then… Else statement.

    • Description: The statements below the Else clause (StatementsIfCellIsNotEmpty) are executed if the condition tested in the opening statement of the If… Then… Else statement (IsEmpty(Cell)) returns False. Within this macro structure, IsEmpty(Cell) returns False if Cell is not empty.

Line #4: StatementsIfCellIsNotEmpty
  1. Item: StatementsIfCellIsNotEmpty.

    • VBA Construct: Else Statements within If… Then… Else statement.

    • Description: One or more VBA statements that are executed if the condition tested in the opening statement of the If… Then… Else statement (IsEmpty(Cell)) returns False. Within this macro structure, IsEmpty(Cell) returns False if Cell is not empty.
Line #5: End If
  1. Item: End If.

    • VBA Construct: Closing statement of If… Then… Else statement.

    • Description: The End If clause marks the end of the If… Then… Else block.
Macro Example to Check if Cell is Empty

The following macro example checks if cell A5 of the worksheet named “Check if Cell is Empty” (myCell) is empty and displays a message box confirming whether the cell is empty or not empty.

Sub checkIfCellIsEmpty()

    'Source: https://powerspreadsheets.com/
    'For further information: https://powerspreadsheets.com/excel-vba-cell-empty/

    'declare object variable to hold reference to cell you work with
    Dim myCell As Range

    'identify cell you work with
    Set myCell = ThisWorkbook.Worksheets("Check if Cell is Empty").Range("A5")

    'check if cell is empty. Depending on result, display message box indicating whether cell is empty (True) or not empty (False)
    If IsEmpty(myCell) Then
        MsgBox myCell.Address & " is empty"
    Else
        MsgBox myCell.Address & " is not empty"
    End If

End Sub
Effects of Executing Macro Example to Check if Cell is Empty

The following GIF illustrates the results of executing the macro example. Cell A5 (This cell isn’t empty) is not empty and the message box displayed confirms that this is the case.

#2: Check if Active Cell is Empty VBA Code to Check if Active Cell is Empty

To check if the active cell is empty with VBA, use a macro with the following statement structure:

If IsEmpty(ActiveCell) Then
    StatementsIfActiveCellIsEmpty
Else
    StatementsIfActiveCellIsNotEmpty
End If
Process Followed by VBA Code to Check if Active Cell is Empty

VBA Statement Explanation Line #1: If IsEmpty(ActiveCell) Then
  1. Item: If… Then.

    • VBA Construct: Opening statement of If… Then… Else statement.

    • Description: The If… Then… Else statement conditionally executes a group of statements depending on the value of an expression. For these purposes:

      • The If… Then… Else statement tests the specified condition (IsEmpty(ActiveCell)).

      • If the condition is met and returns True: StatementsIfActiveCellIsEmpty are executed.

      • If the condition isn’t met and returns False: StatementsIfActiveCellIsNotEmpty are executed.

  2. Item: IsEmpty(…).

    • VBA Construct: IsEmpty function.

    • Description: Generally, the IsEmpty function indicates whether a variable has been initialized. Nonetheless, you can also use IsEmpty to check if a cell is empty.

      The IsEmpty function:

      • Takes one parameter (expression) of the Variant data type. Within this macro structure, the parameter is a Range object (ActiveCell).

      • Returns True if the variable is uninitialized or explicitly set to Empty. Otherwise, IsEmpty returns False.

  3. Item: ActiveCell.

    • VBA Construct: Application.ActiveCell property.

    • Description: The Application.ActiveCell property returns a Range object representing the active cell.

  4. Item: IsEmpty(ActiveCell).

    • VBA Construct: Condition of If… Then… Else statement.

    • Description: This condition is an expression that evaluates to True or False. The IsEmpty function (IsEmpty(ActiveCell)) returns True or False, as follows:

      • True: Active cell is empty.

      • False: Active cell is not empty.
Line #2: StatementsIfActiveCellIsEmpty
  1. Item: StatementsIfActiveCellIsEmpty.

    • VBA Construct: Statements within If… Then… Else statement.

    • Description: One or more VBA statements that are executed if the condition tested in the opening statement of the If… Then… Else statement (IsEmpty(ActiveCell)) returns True. Within this macro structure, IsEmpty(ActiveCell) returns True if the active cell is empty.
Line #3: Else
  1. Item: Else.

    • VBA Construct: Else clause of If… Then… Else statement.

    • Description: The statements below the Else clause (StatementsIfActiveCellIsNotEmpty) are executed if the condition tested in the opening statement of the If… Then… Else statement (IsEmpty(ActiveCell)) returns False. Within this macro structure, IsEmpty(ActiveCell) returns False if the active cell is not empty.

Line #4: StatementsIfActiveCellIsNotEmpty
  1. Item: StatementsIfActiveCellIsNotEmpty.

    • VBA Construct: Else Statements within If… Then… Else statement.

    • Description: One or more VBA statements that are executed if the condition tested in the opening statement of the If… Then… Else statement (IsEmpty(ActiveCell)) returns False. Within this macro structure, IsEmpty(ActiveCell) returns False if the active cell is not empty.
Line #5: End If
  1. Item: End If.

    • VBA Construct: Closing statement of If… Then… Else statement.

    • Description: The End If clause marks the end of the If… Then… Else block.
Macro Example to Check if Active Cell is Empty

The following macro example checks if the active cell is empty and displays a message box confirming whether the active cell is empty or not empty.

Sub checkIfActiveCellIsEmpty()

    'Source: https://powerspreadsheets.com/
    'For further information: https://powerspreadsheets.com/excel-vba-cell-empty/

    'check if active cell is empty. Depending on result, display message box indicating whether active cell is empty (True) or not empty (False)
    If IsEmpty(ActiveCell) Then
        MsgBox "The active cell is empty"
    Else
        MsgBox "The active cell is not empty"
    End If

End Sub
Effects of Executing Macro Example to Check if Active Cell is Empty

The following GIF illustrates the results of executing the macro example. The active cell (A6) is empty and the message box displayed confirms that this is the case.

#3: Check if Range is Empty VBA Code to Check if Range is Empty

To check if a range is empty with VBA, use a macro with the following statement structure:

If WorksheetFunction.CountA(CellRange) = 0 Then
    StatementsIfRangeIsEmpty
Else
    StatementsIfRangeIsNotEmpty
End If
Process Followed by VBA Code to Check if Range is Empty

VBA Statement Explanation Line #1: If WorksheetFunction.CountA(CellRange) = 0 Then
  1. Item: If… Then.

    • VBA Construct: Opening statement of If… Then… Else statement.

    • Description: The If… Then… Else statement conditionally executes a group of statements depending on the value of an expression. For these purposes:

      • The If… Then… Else statement tests the specified condition (WorksheetFunction.CountA(CellRange) = 0).

      • If the condition is met and returns True: StatementsIfRangeIsEmpty are executed.

      • If the condition isn’t met and returns False: StatementsIfRangeIsNotEmpty are executed.

  2. Item: WorksheetFunction.CountA(…).

    • VBA Construct: WorksheetFunction.CountA method.

    • Description: The WorksheetFunction.CountA method counts the number of cells that are not empty within the argument list (CellRange). For these purposes, a cell is deemed to not be empty if, for example, it contains an error value or empty text (“”).

  3. Item: CellRange.

    • VBA Construct: Range object.

    • Description: Range object representing the cell range you work with.

      You can usually return a Range object with constructs such as the Worksheet.Range property. If you explicitly declare an object variable to represent CellRange, use the Range object data type.

  4. Item: =.

    • VBA Construct: = comparison operator.

    • Description: The = comparison operator compares the 2 expressions to determine whether they’re equal:

      • The expression to the left of the = comparison operator (WorksheetFunction.CountA(CellRange)).

      • The expression to the right of the = comparison operator (0).

  5. Item: WorksheetFunction.CountA(CellRange) = 0.

    • VBA Construct: Condition of If… Then… Else statement.

    • Description: The condition is an expression that evaluates to True or False. The = comparison operator returns True or False as follows:

      • True: If WorksheetFunction.CountA(CellRange) returns 0. This occurs when CellRange is empty.

      • False: If WorksheetFunction.CountA(CellRange) returns a value other than 0. This occurs when CellRange isn’t empty.
Line #2: StatementsIfRangeIsEmpty
  1. Item: StatementsIfRangeIsEmpty.

    • VBA Construct: Statements within If… Then… Else statement.

    • Description: One or more VBA statements that are executed if the condition tested in the opening statement of the If… Then… Else statement (WorksheetFunction.CountA(CellRange) = 0) returns True. Within this macro structure, (WorksheetFunction.CountA(CellRange) = 0) returns True if CellRange is empty.
Line #3: Else
  1. Item: Else.

    • VBA Construct: Else clause of If… Then… Else statement.

    • Description: The statements below the Else clause (StatementsIfRangeIsNotEmpty) are executed if the condition tested in the opening statement of the If… Then… Else statement (WorksheetFunction.CountA(CellRange) = 0) returns False. Within this macro structure, (WorksheetFunction.CountA(CellRange) = 0) returns False if CellRange is not empty.

Line #4: StatementsIfRangeIsNotEmpty
  1. Item: StatementsIfRangeIsNotEmpty.

    • VBA Construct: Else Statements within If… Then… Else statement.

    • Description: One or more VBA statements that are executed if the condition tested in the opening statement of the If… Then… Else statement (WorksheetFunction.CountA(CellRange) = 0) returns False. Within this macro structure, (WorksheetFunction.CountA(CellRange) = 0) returns False if CellRange is not empty.
Line #5: End If
  1. Item: End If.

    • VBA Construct: Closing statement of If… Then… Else statement.

    • Description: The End If clause marks the end of the If… Then… Else block.
Macro Example to Check if Range is Empty

The following macro example checks if the range composed of cells A7 through A11 of the worksheet named “Check if Cell is Empty” (myCellRange) is empty and displays a message box confirming whether the range is empty or not empty.

Sub checkIfRangeIsEmpty()

    'Source: https://powerspreadsheets.com/
    'For further information: https://powerspreadsheets.com/excel-vba-cell-empty/

    'declare object variable to hold reference to cell range you work with
    Dim myCellRange As Range

    'identify cell range you work with
    Set myCellRange = ThisWorkbook.Worksheets("Check if Cell is Empty").Range("A7:A11")

    'check if number of non-empty cells in range is 0. Depending on result, display message box indicating whether cell range is empty (True) or not empty (False)
    If WorksheetFunction.CountA(myCellRange) = 0 Then
        MsgBox myCellRange.Address & " is empty"
    Else
        MsgBox myCellRange.Address & " is not empty"
    End If

End Sub
Effects of Executing Macro Example to Check if Range is Empty

The following..

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

In this VBA Tutorial, you learn how to replace or substitute substrings or characters within strings.

This VBA Tutorial is accompanied by Excel workbooks containing the data and macros I use in the examples below. You can get immediate free access to these example workbooks by clicking the button below.


Use the following Table of Contents to navigate to the section you’re interested in.

Related VBA and Macro Tutorials

The following VBA and Macro Tutorials may help you better understand and implement the contents below:

  • General VBA constructs and structures:

    • Learn about commonly-used VBA terms here.

    • Learn about the Excel Object Model here.

    • Learn about working with variables here.

    • Learn about data types here.

    • Learn about working with arrays here.

  • Practical VBA applications and macro examples:

    • Learn about referring to cell ranges here.

    • Learn about working with worksheet functions within VBA here.

You can find additional VBA and Macro Tutorials in the Archives.

#1: Replace String in Cell VBA Code to Replace String in Cell

To replace a string in a cell with VBA, use a statement with the following structure:

Cell.Value = Replace(Expression:=Cell.Value, Find:=StringToReplace, Replace:=ReplacementString, Count:=NumberOfReplacements)
Process Followed by VBA Code to Replace String in Cell

VBA Statement Explanation
  1. Item: Cell.

    • VBA Construct: Range object.

    • Description: Range object representing the cell you work with.

      You can usually return a Range object with constructs such as the Worksheet.Range, Worksheet.Cells (with the Range.Item) or Range.Offset properties.

  2. Item: Value.

    • VBA Construct: Range.Value property.

    • Description: The Range.Value property specifies the value (in this case string) within Cell.

  3. Item: =.

    1. VBA Construct: Assignment operator.

    2. Description: The = operator assigns the string returned by the Replace function to the Range.Value property of Cell.

  4. Item: Replace(…).

    • VBA Construct: Replace function.

    • Description: The Replace function returns a string where a specific substring (StringToReplace) is replaced by another substring (ReplacementString) a specific number of times (NumberOfReplacements).

  5. Item: Expression:=Cell.Value.

    • VBA Construct: Expression parameter of the Replace function, Range object and Range.Value property.

    • Description: The Expression parameter of the Replace function specifies the string expression containing the substring you want to replace (StringToReplace). Within this macro structure, Expression is the value (string) within Cell, as returned by the Range.Value property.

  6. Item: Find:=StringToReplace.

    • VBA Construct: Find parameter of the Replace function.

    • Description: The Find parameter of the Replace function specifies the substring you search for and replace.

      If you explicitly declare a variable to represent StringToReplace, use the String data type.

  7. Item: Replace:=ReplacementString.

    • VBA Construct: Replace parameter of the Replace function.

    • Description: The Replace parameter of the Replace function specifies the substring you want to use as replacement for StringToReplace.

      If you explicitly declare a variable to represent ReplacementString, use the String data type.

  8. Item: Count:=NumberOfReplacements.

    • VBA Construct: Count parameter of the Replace function.

    • Description: The Count parameter of the Replace function specifies the number of substitutions you want to carry out. In other words, the number of times you want to replace StringToReplace with ReplacementString.

      If you want VBA to replace all occurrences of StringToReplace with ReplacementString, omit the Count parameter. In such case, Count defaults to -1 and VBA carries out all possible substitutions. Please refer to the appropriate section (Replace All Occurrences of String in Cell) below for further information about this scenario.
Macro Example to Replace String in Cell

The following macro replaces the string “replace” (myStringToReplace) with the string “substitute” (myReplacementString) one time (myNumberOfReplacements) within the string in cell A5 of the worksheet named “Excel VBA Replace” (myCell).

Sub replaceStringInCell()

    'Source: https://powerspreadsheets.com/
    'For further information: https://powerspreadsheets.com/excel-vba-replace-substitute/

    'declare object variable to hold reference to cell you work with
    Dim myCell As Range

    'declare variables to hold parameters for string replacement (string to replace, replacement string, and number of replacements)
    Dim myStringToReplace As String
    Dim myReplacementString As String
    Dim myNumberOfReplacements As Long

    'identify cell you work with
    Set myCell = ThisWorkbook.Worksheets("Excel VBA Replace").Range("A5")

    'specify parameters for string replacement (string to replace, replacement string, and number of replacements)
    myStringToReplace = "replace"
    myReplacementString = "substitute"
    myNumberOfReplacements = 1

    'replace string in cell you work with, and assign resulting string to Range.Value property of cell you work with
    myCell.Value = Replace(Expression:=myCell.Value, Find:=myStringToReplace, Replace:=myReplacementString, Count:=myNumberOfReplacements)

End Sub
Effects of Executing Macro Example to Replace String in Cell

The following GIF illustrates the results of executing this macro example. As expected, the macro replaces the string “replace” with the string “substitute” one time within the string in cell A5.

#2: Replace String in Cell Specifying a Starting Position for Search VBA Code to Replace String in Cell Specifying a Starting Position for Search

To replace a string in a cell and specify the starting position to search for the string with VBA, use a statement with the following structure:

Cell.Value = Left(String:=Cell.Value, Length:=StartPosition - 1) & Replace(Expression:=Cell.Value, Find:=StringToReplace, Replace:=ReplacementString, Start:=StartPosition, Count:=NumberOfReplacements)
Process Followed by VBA Code to Replace String in Cell Specifying a Starting Position for Search

VBA Statement Explanation
  1. Item: Cell.

    • VBA Construct: Range object.

    • Description: Range object representing the cell you work with.

      You can usually return a Range object with constructs such as the Worksheet.Range, Worksheet.Cells (with the Range.Item) or Range.Offset properties.

  2. Item: Value.

    • VBA Construct: Range.Value property.

    • Description: The Range.Value property specifies the value (in this case string) within Cell.

  3. Item: =.

    • VBA Construct: Assignment operator.

    • Description: The = operator assigns the string returned by the Replace function to the Range.Value property of Cell.

  4. Item: Left(…).

    • VBA Construct: Left function.

    • Description: The Left function returns a string containing the number of characters specified by the Length parameter (StartPosition – 1) from the left side of the string specified by the String parameter (Cell.Value).

      Within this macro structure, you use the Left function to return the substring containing the first characters of the string within the cell you work with. This substring goes from the first character of the string to the character immediately before the position within the string where you start searching for the substring you want to replace (StringToReplace).

      You need to do this because the Replace function doesn’t return a copy of the string (with substitutions) from start to finish. The string that Replace returns starts at the position within the string where you start searching for the substring you want to replace (StartPosition). Therefore, VBA truncates the string and the characters to the left of StartPosition aren’t part of the string returned by Replace.

  5. Item: String:=Cell.Value.

    • VBA Construct: String parameter of the Left function, Range object and Range.Value property.

    • Description: The String parameter of the Left function specifies the string expression containing the substring you want to replace (StringToReplace).

      Within this macro structure, String is the value (string) within Cell, as returned by the Range.Value property. The value of the String parameter of the Left function is the same as the value of the Expression parameter of the Replace function.

  6. Item: Length:=StartPosition – 1.

    • VBA Construct: Length parameter of the Left function.

    • Description: The Length parameter of the Left function specifies the number of characters the Left function returns from the string you work with. StartPosition is the position within the string where you start searching for the substring you want to replace (StringToReplace). (StartPosition – 1) is the position of the character immediately before StartPosition. Therefore, the Left function returns the substring containing the first characters of the string within the cell you work with, up until the character located in position (StartPosition – 1).

      If you explicitly declare a variable to represent StartPosition, use the Long data type. The value of StartPosition within the Length parameter of the Left function is the same as the value of the Start parameter of the Replace function.

  7. Item: &.

    • VBA Construct: Concatenation operator.

    • Description: The & operator concatenates the strings returned by the Left and Replace functions.

  8. Item: Replace(…).

    • VBA Construct: Replace function.

    • Description: The Replace function returns a string where a specific substring (StringToReplace) is replaced by another substring (ReplacementString) a specific number of times (NumberOfReplacements).

  9. Item: Expression:=Cell.Value.

    • VBA Construct: Expression parameter of the Replace function, Range object and Range.Value property.

    • Description: The Expression parameter of the Replace function specifies the string expression containing the substring you want to replace (StringToReplace). Within this macro structure, Expression is the value (string) within Cell, as returned by the Range.Value property.

  10. Item: Find:=StringToReplace.

    • VBA Construct: Find parameter of the Replace function.

    • Description: The Find parameter of the Replace function specifies the substring you search for and replace.

      If you explicitly declare a variable to represent StringToReplace, use the String data type.

  11. Item: Replace:=ReplacementString.

    • VBA Construct: Replace parameter of the Replace function.

    • Description: The Replace parameter of the Replace function specifies the substring you want to use as replacement for StringToReplace.

      If you explicitly declare a variable to represent ReplacementString, use the String data type.

  12. Item: Start:=StartPosition.

    • VBA Construct: Start parameter of the Replace function.

    • Description: The Start parameter of the Replace function specifies the position within the string you work with where you start searching for StringToReplace.

      The default value of the Start parameter is 1. In such case, the Replace function doesn’t truncate the string. Therefore, you generally don’t have to work with the Left function and concatenation operator. Please refer to the appropriate section (Replace String in Cell) above for further information about this scenario.

  13. Item: Count:=NumberOfReplacements.

    • VBA Construct: Count parameter of the Replace function.

    • Description: The Count parameter of the Replace function specifies the number of substitutions you want to carry out. In other words, the number of times you want to replace StringToReplace with ReplacementString.

      If you want VBA to replace all occurrences of StringToReplace after StartPosition with ReplacementString, omit the Count parameter. In such case, Count defaults to -1 and VBA carries out all possible substitutions. Please refer to the appropriate section (Replace All Occurrences of String in Cell) below for further information about this scenario.
Macro Example to Replace String in Cell Specifying a Starting Position for Search

The following macro replaces the string “replace” (myStringToReplace) with the string “substitute” (myReplacementString) one time (myNumberOfReplacements) within the string in cell A6 of the worksheet named “Excel VBA Replace” (myCell). The search for myStringToReplace begins in position 14 (myStartPosition) of the string in myCell.

Sub replaceStringInCellWithStartPosition()

    'Source: https://powerspreadsheets.com/
    'For further information: https://powerspreadsheets.com/excel-vba-replace-substitute/

    'declare object variable to hold reference to cell you work with
    Dim myCell As Range

    'declare variables to hold parameters for string replacement (string to replace, replacement string, start position for search of string to replace, and number of replacements)
    Dim myStringToReplace As String
    Dim myReplacementString As String
    Dim myStartPosition As Long
    Dim myNumberOfReplacements As Long

    'identify cell you work with
    Set myCell = ThisWorkbook.Worksheets("Excel VBA Replace").Range("A6")

    'specify parameters for string replacement (string to replace, replacement string, start position for search of string to replace, and number of replacements)
    myStringToReplace = "replace"
    myReplacementString = "substitute"
    myStartPosition = 14
    myNumberOfReplacements = 1

    'return and concatenate the following strings, and assign the resulting (concatenated) string to Range.Value property of cell you work with
        '(i) string containing the first characters within the cell you work with (from first position up to the character before the start position for search of string to replace)
        '(ii) string resulting from working with the Replace function and the parameter for string replacement you specify
    myCell.Value = Left(String:=myCell.Value, Length:=myStartPosition - 1) & Replace(Expression:=myCell.Value, Find:=myStringToReplace, Replace:=myReplacementString, Start:=myStartPosition, Count:=myNumberOfReplacements)

End Sub
Effects of Executing Macro Example to Replace String in Cell Specifying a Starting Position for Search

The following GIF illustrates the results of executing this macro example. As expected, the macro replaces the string “replace” with the string “substitute” one time within the string in cell A6. The search for myStringToReplace begins in position 14 of the string in cell A6. This matches with the second occurrence of the “replace” string.

#3: Replace All Occurrences of String in Cell VBA Code to Replace All Occurrences of String in Cell

To replace all occurrences of a string in a cell with VBA, use a statement with the following structure:

Cell.Value = Replace(Expression:=Cell.Value, Find:=StringToReplace, Replace:=ReplacementString)
Process Followed by VBA Code to Replace All Occurrences of String in Cell

VBA Statement Explanation
  1. Item: Cell.

    • VBA Construct: Range object.

    • Description: Range object representing the cell you work with.

      You can usually return a Range object with constructs such as the Worksheet.Range, Worksheet.Cells (with the Range.Item) or Range.Offset properties.

  2. Item: Value.

    • VBA Construct: Range.Value property.

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

In this VBA Tutorial, you learn how to create a Pivot Table with different destinations (both worksheet or workbook) and from both static and dynamic data ranges.

This VBA Tutorial is accompanied by Excel workbooks containing the data and macros I use in the examples below. You can get immediate free access to these example workbooks by clicking the button below.


Use the following Table of Contents to navigate to the section you’re interested in.

Related VBA and Macro Tutorials

The following VBA and Macro Tutorials may help you better understand and implement the contents below:

  • General VBA constructs and structures:

    • Learn about commonly-used VBA terms here.

    • Learn about the Excel VBA Object Model here.

    • Learn how to work with variables here.

    • Learn about data types here.

    • Learn about the R1C1 reference-style here.

  • Practical VBA applications and macro examples:

    • Learn how to create a new workbook here.

    • Learn how to find the last column with data here.

    • Learn about working with worksheets here.

You can find additional VBA and Macro Tutorials in the Archives.

#1: Create Pivot Table in Existing Sheet VBA Code to Create Pivot Table in Existing Sheet

To create a Pivot Table in an existing sheet with VBA, use a statement with the following structure:

Workbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=SourceWorksheetName & "!" & SourceDataAddress).createPivotTable TableDestination:=DestinationWorksheetName & "!" & DestinationRangeAddress, TableName:="NewPivotTable"
Process Followed by VBA Code

VBA Statement Explanation
  1. Item: Workbook.

    • VBA Construct: Workbook object.

    • Description: Represents the Excel workbook containing the source (SourceWorksheet) and destination worksheets (DestinationWorksheet) you work with. For purposes of this structure, both the source and destination worksheet are in the same workbook.

      Use properties such Application.Workbooks, Application.ThisWorkbook and Application.ActiveWorkbook to return this Workbook object.

  2. Item: PivotCaches

    • VBA Construct: Workbook.PivotCaches method.

    • Description: Returns the PivotCaches collection representing all the Pivot Table caches within Workbook.

  3. Item: Create.

    • VBA Construct: PivotCaches.Create method.

    • Description: Creates a new PivotCache object representing the memory cache for the Pivot Table you create.

  4. Item: SourceType:=xlDatabase

    • VBA Construct: SourceType parameter of the PivotCaches.Create method.

    • Description: Sets the data source of the Pivot Table you create to an Excel list or database (xlDatabase).

      Use the constants within the xlPivotTableSourceType enumeration for purposes of specifying a different data source. Nonetheless, setting SourceType to xlPivotTable (representing the same data source as another Pivot Table) or xlScenario (representing scenarios created using the Scenario Manager) generally results in a run-time error.

  5. Item: SourceData:=SourceWorksheetName & “!” & SourceDataAddress.

    • VBA Construct: SourceData parameter of the PivotCaches.Create method.

    • Description: Specifies the data source for the Pivot Table cache.

      If you use the statement structure specified within this VBA Tutorial and explicitly declare variables to represent SourceWorksheetName and SourceDataAddress, use the String data type. Within this structure, SourceData is specified as follows:

      • SourceWorksheetName: Name of the worksheet containing the source data.

        If necessary, use the Worksheet.Name property to return a string representing the worksheet’s name.

      • &: Concatenation operator.

      • SourceDataAddress: Address of the cell range containing the source data.

        If necessary, use the Range.Address property to return a string representing the cell range reference.

      SourceData is of the Variant data type. However, Microsoft’s documentation recommends the following:

      • Either (i) using a string to specify the worksheet and cell range (as above), or (ii) setting up a named range and passing the name as a string.

      • Avoid passing a Range object, as this may result in unexpected “type mismatch” errors.

  6. Item: createPivotTable

    • VBA Construct: PivotCache.CreatePivotTable method.

    • Description: Creates a Pivot Table based on the PivotCache created by the PivotCaches.Create method.

  7. Item: TableDestination:=DestinationWorksheetName & “!” & DestinationRangeAddress

    • VBA Construct: TableDestination parameter of PivotCache.CreatePivotTable method.

    • Description: Specifies the cell in the upper-left corner of the cell range where the Pivot Table you create is located.

      If you use the statement structure specified within this VBA Tutorial and explicitly declare variables to represent DestinationWorksheetName and DestinationRangeAddress, use the String data type. Within this structure, TableDestination is specified as follows:

      • DestinationWorksheetName: Name of the destination worksheet where the Pivot Table you create is located.

        If necessary, use the Worksheet.Name property to return a string representing the worksheet’s name.

      • &: Concatenation operator.

      • DestinationRangeAddress: Address of the cell in the upper-left corner of the cell range where the Pivot Table you create is located.

        If necessary, use the Range.Address property to return a string representing the cell range reference.

  8. Item: TableName:=”NewPivotTable”

    • VBA Construct: TableName parameter of the PivotCache.CreatePivotTable method.

    • Description: Specifies the name of the Pivot Table you create as “NewPivotTable”.

      If you explicitly declare a variable to represent NewPivotTable, use the String data type and omit the quotes included above (” “).
Macro Example

The following macro creates a new Pivot Table in an existing worksheet (PivotTable).

Sub createPivotTableExistingSheet()

    'Source: https://powerspreadsheets.com/
    'For further information: https://powerspreadsheets.com/vba-create-pivot-table/

    'declare variables to hold row and column numbers that define source data cell range
    Dim myFirstRow As Long
    Dim myLastRow As Long
    Dim myFirstColumn As Long
    Dim myLastColumn As Long

    'declare variables to hold source and destination cell range address
    Dim mySourceData As String
    Dim myDestinationRange As String

    'declare object variables to hold references to source and destination worksheets, and new Pivot Table
    Dim mySourceWorksheet As Worksheet
    Dim myDestinationWorksheet As Worksheet
    Dim myPivotTable As PivotTable

    'identify source and destination worksheets
    With ThisWorkbook
        Set mySourceWorksheet = .Worksheets("Data")
        Set myDestinationWorksheet = .Worksheets("PivotTable")
    End With

    'obtain address of destination cell range
    myDestinationRange = myDestinationWorksheet.Range("A5").Address(ReferenceStyle:=xlR1C1)

    'identify row and column numbers that define source data cell range
    myFirstRow = 5
    myLastRow = 20005
    myFirstColumn = 1
    myLastColumn = 6

    'obtain address of source data cell range
    With mySourceWorksheet.Cells
        mySourceData = .Range(.Cells(myFirstRow, myFirstColumn), .Cells(myLastRow, myLastColumn)).Address(ReferenceStyle:=xlR1C1)
    End With

    'create Pivot Table cache and create Pivot Table report based on that cache
    Set myPivotTable = ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=mySourceWorksheet.Name & "!" & mySourceData).CreatePivotTable(TableDestination:=myDestinationWorksheet.Name & "!" & myDestinationRange, TableName:="PivotTableExistingSheet")

    'add, organize and format Pivot Table fields
    With myPivotTable
        .PivotFields("Item").Orientation = xlRowField
        With .PivotFields("Units Sold")
            .Orientation = xlDataField
            .Position = 1
            .Function = xlSum
            .NumberFormat = "#,##0.00"
        End With
        With .PivotFields("Sales Amount")
            .Orientation = xlDataField
            .Position = 2
            .Function = xlSum
            .NumberFormat = "#,##0.00"
        End With
    End With

End Sub
Effects of Executing Macro Example

The following GIF illustrates the results of executing this macro example. As expected, the macro creates a Pivot Table in the “PivotTable” worksheet.

#2: Create Pivot Table in New Sheet VBA Code to Create Pivot Table in New Sheet

To create a Pivot Table in a new sheet with VBA, use a macro with the following statement structure:

Dim DestinationWorksheet As Worksheet
Set DestinationWorksheet = Worksheets.Add
Workbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=SourceWorksheetName & "!" & SourceDataAddress).createPivotTable TableDestination:=DestinationWorksheet.Name & "!" & DestinationRangeAddress, TableName:="NewPivotTable"
Process Followed by VBA Code

VBA Statement Explanation Line #1: Dim DestinationWorksheet As Worksheet
  1. Item: Dim DestinationWorksheet As Worksheet.

    • VBA Construct: Dim statement.

    • Description: Declares the DestinationWorksheet object variable as of the Worksheet object data type.

      DestinationWorksheet represents the new worksheet (line #2 below) where the Pivot Table you create (line #3 below) is located.
Line #2: Set DestinationWorksheet = Worksheets.Add
  1. Item: Set.

    • VBA Construct: Set statement.

    • Description: Assigns the reference to the Worksheet object returned by the Worksheets.Add method to the DestinationWorksheet object variable.

  2. Item: DestinationWorksheet.

    • VBA Construct: Object variable of the Worksheet object data type.

    • Description: Represents the new worksheet where the Pivot Table you create (line #3 below) is located.

  3. Item: =.

    • VBA Construct: Assignment operator.

    • Description: Assigns the reference to the Worksheet object returned by the Worksheets.Add method to the DestinationWorksheet object variable.

  4. Item: Worksheets.

    • VBA Construct: Worksheets collection.

    • Description: The collection containing all the Worksheet objects (each representing a worksheet) within the workbook your work with.

  5. Item: Add.

    • VBA Construct: Worksheets.Add method.

    • Description: Creates a new worksheet. This is the worksheet where the Pivot Table you create (line #3 below) is located.
Line #3: Workbook.PivotCaches.Create( SourceType:=xlDatabase, SourceData:=SourceWorksheetName & “!” & SourceDataAddress).createPivotTable TableDestination:=DestinationWorksheetName & “!” & DestinationRangeAddress, TableName:=”NewPivotTable”
  1. Item: Workbook.

    • VBA Construct: Workbook object.

    • Description: Represents the Excel workbook containing the source (SourceWorksheet) and destination worksheets (DestinationWorksheet) you work with. For purposes of this structure, both the source and destination worksheet are in the same workbook.

      Use properties such Application.Workbooks, Application.ThisWorkbook and Application.ActiveWorkbook to return this Workbook object.

  2. Item: PivotCaches

    • VBA Construct: Workbook.PivotCaches method.

    • Description: Returns the PivotCaches collection representing all the Pivot Table caches within Workbook.

  3. Item: Create.

    • VBA Construct: PivotCaches.Create method.

    • Description: Creates a new PivotCache object representing the memory cache for the Pivot Table you create.

  4. Item: SourceType:=xlDatabase

    • VBA Construct: SourceType parameter of the PivotCaches.Create method.

    • Description: Sets the data source of the Pivot Table you create to an Excel list or database (xlDatabase).

      Use the constants within the xlPivotTableSourceType enumeration for purposes of specifying a different data source. Nonetheless, setting SourceType to xlPivotTable (representing the same data source as another Pivot Table) or xlScenario (representing scenarios created using the Scenario Manager) generally results in a run-time error.

  5. Item: SourceData:=SourceWorksheetName & “!” & SourceDataAddress.

    • VBA Construct: SourceData parameter of the PivotCaches.Create method.

    • Description: Specifies the data source for the Pivot Table cache.

      If you use the statement structure specified within this VBA Tutorial and explicitly declare variables to represent SourceWorksheetName and SourceDataAddress, use the String data type. Within this structure, SourceData is specified as follows:

      • SourceWorksheetName: Name of the worksheet containing the source data.

        If necessary, use the Worksheet.Name property to return a string representing the worksheet’s name.

      • &: Concatenation operator.

      • SourceDataAddress: Address of the cell range containing the source data.

        If necessary, use the Range.Address property to return a string representing the cell range reference.

      SourceData is of the Variant data type. However, Microsoft’s documentation recommends the following:

      • Either (i) using a string to specify the worksheet and cell range (as above), or (ii) setting up a named range and passing the name as a string.

      • Avoid passing a Range object, as this may result in unexpected “type mismatch” errors.

  6. Item: createPivotTable

    • VBA Construct: PivotCache.CreatePivotTable method.

    • Description: Creates a Pivot Table based on the PivotCache created by the PivotCaches.Create method.

  7. Item: TableDestination:=DestinationWorksheet.Name & “!” & DestinationRangeAddress

    • VBA Construct: TableDestination parameter of PivotCache.CreatePivotTable method.

    • Description: Specifies the cell in the upper-left corner of the cell range where the Pivot Table you create is located.

      If you use the statement structure specified within this VBA Tutorial and explicitly declare a variable to represent DestinationRangeAddress, use the String data type. Within this structure, TableDestination is specified as follows:

      • DestinationWorksheet.Name: Worksheet.Name property.

        Returns a string representing the name of DestinationWorksheet. DestinationWorksheet is the new worksheet where the Pivot Table you create is located.

      • &: Concatenation operator.

      • DestinationRangeAddress: Address of the cell in the upper-left corner of the cell range where the Pivot Table you create is located.

        If necessary, use the Range.Address property to return a string representing the cell range reference.

  8. Item: TableName:=”NewPivotTable”

    • VBA Construct: TableName parameter of the PivotCache.CreatePivotTable method.

    • Description: Specifies the name of the Pivot Table you create as “NewPivotTable”.

      If you explicitly declare a variable to represent NewPivotTable, use the String data type and omit the quotes included above (” “).
Macro Example

The following macro creates a new Pivot Table in a new worksheet.

Sub createPivotTableNewSheet()

    'Source: https://powerspreadsheets.com/
    'For further information: https://powerspreadsheets.com/vba-create-pivot-table/

    'declare variables to hold row and column numbers that define source data cell range
    Dim myFirstRow As Long
    Dim myLastRow As Long
    Dim myFirstColumn As Long
    Dim myLastColumn As Long

    'declare variables to hold source and destination cell range address
    Dim mySourceData As String
    Dim myDestinationRange As String

    'declare object variables to hold references to source and destination worksheets, and new Pivot Table
    Dim mySourceWorksheet As Worksheet
    Dim myDestinationWorksheet As Worksheet
   ..
Read Full Article
Visit website

Read for later

Articles marked as Favorite are saved for later viewing.
close
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

Separate tags by commas
To access this feature, please upgrade your account.
Start your free year
Free Preview