Loading...

Follow Power Spreadsheets on Feedspot

Continue with Google
Continue with Facebook
or

Valid

My main purpose at Power Spreadsheets is to help you become an Excel Power User. I work (and will continue to work) very hard to achieve this purpose.

However, Excel is a massive topic. Therefore, I'll probably won't be able to cover absolute everything about Excel.

I'm also aware that I don't have all the answers. I'm constantly learning new things about Excel.

This Resources List is my attempt to gather and organize as many useful Excel resources as possible. Much of my knowledge of Excel comes from some of the materials and experts I share here. I hope they help you as much as they've helped me.

This post includes values, such as Alexa and Amazon Best Sellers Rank. These values change from time to time. I specify the period during which these values were gathered at the beginning of each section

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

Some of the links in this Excel Resources List are affiliate links, which means that if you choose to make a purchase, I will earn a commission. This commission comes at no additional cost to you.

I visit websites before including them in this Resources List. I try to avoid listing spam, low quality or dangerous websites.

However, due to the sheer amount of resources I list below, I haven't thoroughly audited and/or used all of them. Furthermore, resources may have been modified since the last time I visited the relevant website. The titles, names, descriptions or similar that appear below are, generally, those set by the creators of the relevant resource (not me) within the relevant HTML code or similar. Please make sure to carry out your own due diligence prior to taking action, such as visiting, buying or downloading anything. For further information regarding this, please refer to the applicable legal documents (including without limitation the Terms and Conditions and the Limit of Liability and Disclaimer of Warranty) at the Footer below.

Official Microsoft Resources Blogs, Forums and Websites Social Media Accounts and Groups Facebook Twitter LinkedIn Google+ YouTube Books

Books are organized according to their Amazon Best Sellers Rank in Books or in Paid Kindle Store (if Books isn't applicable) as of 5 June 2018.

Use the links below to visit the book's webpage in Amazon. PowerSpreadsheets.com is a participant in the Amazon Services LLC Associates Program, an affiliate advertising program designed to provide a means for sites to earn advertising fees by advertising and linking to Amazon.

Blogs and Websites

Blogs and websites are organized according to their Alexa Rank gathered between the 10 and the 12 of June 2018.

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

In this Excel XOR Tutorial, you learn how to use the XOR function in your worksheet formulas for purposes of returning a logical Exclusive Or of the applicable arguments.

The Excel XOR Tutorial is accompanied by an Excel workbook containing the data and formulas I use in the examples below. You can get immediate free access to this example workbook by entering your email address below. Alternatively, you can access all the files that accompany my Tutorials here.

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

Related Excel Tutorials

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

  • Formulas and functions:
    • Learn how to work with the IFERROR function here.
  • Macros and VBA:
    • Learn how to use worksheet functions in macros here.

You can find additional Tutorials in the Archives.

#1: XOR with 2 conditions XOR with 2 conditions formula

To use the XOR function with 2 conditions in Excel, use a formula with the following structure:

=XOR(Condition1,Condition2)
XOR with 2 conditions process

To use the XOR function with 2 conditions in Excel, specify 2 conditions (Condition1 and Condition2).

XOR with 2 conditions formula explanation Item: XOR

The XOR function returns an Exclusive Or of the 2 conditions (Condition1 and Condition2). In other words, the XOR function does the following:

  1. Carries out 2 conditional tests (Condition1 and Condition2); and
  2. Returns TRUE if a single argument (Condition1 or Condition2) is TRUE, and FALSE otherwise. The following truth table illustrates the possible results returned by the XOR function when working with 2 conditions:
    Condition1 Condition2 XOR(Condition1,Condition2)
    TRUE TRUE FALSE
    TRUE FALSE TRUE
    FALSE TRUE TRUE
    FALSE FALSE FALSE
Item: Condition1 and Condition2

The Logical1 and Logical2 arguments of the XOR function (Condition1 and Condition2) are the conditions you want to test. The following are the main requirements that apply to these arguments:

  • The arguments may be logical values, expressions, arrays, or references.
  • The arguments must return a logical value (either TRUE or FALSE). When an argument of the XOR function is an array or reference, the applicable array or reference should contain the logical values or the expressions that are evaluated to logical values.
  • If you use an array or reference as argument(s), and it contains text or empty cells, those value are ignored.
  • If the arguments don't contain logical values, the XOR function returns an error (#VALUE!).
XOR with 2 conditions formula example

The worksheet formulas below return an Exclusive Or (XOR) of 2 conditions as follows:

  • Condition1: The logical value specified in column A (A6 to A9).
  • Condition2: The logical value specified in column B (B6 to B9).
No. XOR formula
1 =XOR(A6:B6)
2 =XOR(A7:B7)
3 =XOR(A8:B8)
4 =XOR(A9:B9)
Effects of using XOR with 2 conditions formula example

The following image illustrates the results returns by the XOR formula when working with 2 conditions. As expected, the formulas (in cells C6 to C9) return the following logical values:

  • TRUE: If a single argument (Condition1 or Condition2) is TRUE (cells C7 and C8).
  • False: Otherwise, as follows:
    • If both arguments are TRUE (cell C6); or
    • If both arguments are FALSE (cell C9).

#2: XOR with more than 2 conditions XOR with more than 2 conditions formula

To use the XOR function with more than 2 conditions in Excel, use a formula with the following structure:

=XOR(ConditionList)
XOR with more than 2 conditions process

To use the XOR function with more than 2 conditions in Excel, specify the conditions you want to consider (ConditionList).

XOR with more than 2 conditions formula explanation Item: XOR

The XOR function returns an Exclusive Or of the conditions you pass as arguments (ConditionList). In other words, the XOR function does the following:

  1. Carries out as many conditional tests as required (ConditionList); and
  2. Returns TRUE or FALSE as follows:
    • TRUE: If an odd number of conditional tests return TRUE.
    • FALSE: If an even number of conditional tests return TRUE.
Item: ConditionList

The Logical1, Logical2, … Logical# arguments of the XOR function (ConditionList) are the conditions you want to test. The following are the main requirements that apply to these arguments:

  • The first argument (Logical1) is required. Subsequent arguments (Logical2, …, Logical#) are optional. XOR accepts a maximum of 254 arguments.
  • The arguments may be logical values, expressions, arrays, or references.
  • The arguments must return a logical value (either TRUE or FALSE). When an argument of the XOR function is an array or reference, the applicable array or reference should contain the logical values or the expressions that are evaluated to logical values.
  • If you use an array or reference as argument(s), and it contains text or empty cells, those value are ignored.
  • If the arguments don't contain logical values, the XOR function returns an error (#VALUE!).
XOR with more than 2 conditions formula example

The worksheet formulas below return an Exclusive Or (XOR) of 5 conditions as follows:

  • Condition1: The logical value specified in column A (A6 to A37).
  • Condition2: The logical value specified in column B (B6 to B37).
  • Condition3: The logical value specified in column C (C6 to C37).
  • Condition4: The logical value specified in column D (D6 to D37).
  • Condition5: The logical value specified in column E (E6 to E37).
No. XOR formula
1 =XOR(A6:E6)
2 =XOR(A7:E7)
3 =XOR(A8:E8)
4 =XOR(A9:E9)
5 =XOR(A10:E10)
32 =XOR(A37:E37)
Effects of using XOR with more than 2 conditions formula example

The following image illustrates the results returns by the XOR formula when working with more than 2 conditions. As expected, the formulas (in cells F6 to F37) return the following logical values:

  • TRUE: If an odd number of arguments (columns A through E) is TRUE.
  • False: If an even number of arguments (columns A through E) is FALSE.

The image below contains a helper column (H) which displays the number of arguments that are TRUE in each row.

#3: XOR alternative to return TRUE when only 1 condition is TRUE XOR alternative to return TRUE when only 1 condition is TRUE formula

Excel's XOR function returns TRUE if an odd number of conditional tests returns TRUE. In some contexts, however, XOR is defined as a function that returns TRUE if only 1 of its arguments returns TRUE.

To create an alternative to the XOR function that returns TRUE when only 1 condition is TRUE (regardless of the number of conditions you test), use a formula with the following structure:

=COUNTIF(ConditionListRange,TRUE)=1
XOR alternative to return TRUE when only 1 condition is TRUE process

To create an alternative to the XOR function that returns TRUE when only 1 condition is TRUE (regardless of the number of conditions you test), follow these steps:

  1. Refer to the cell range containing the conditions you want to consider (ConditionListRange).
  2. Count the number of cells that return TRUE (COUNTIF(ConditionListRange,TRUE)).
  3. Test whether the number of conditions that return TRUE equals 1 (COUNTIF(…)=1)
XOR alternative to return TRUE when only 1 condition is TRUE formula explanation Item: COUNTIF

The COUNTIF function counts the number of cells in a range that meet a single criterion.

Item: ConditionListRange

The Range argument of the COUNTIF function (ConditionListRange) is the cell range containing the conditions you want to test.

When you create an alternative to the XOR function that returns TRUE when only 1 condition is TRUE, the cells in ConditionListRange should contain:

  • Logical values (either TRUE or FALSE); or
  • Expressions that evaluate to logical values.
Item: TRUE

The Criteria argument of the COUNTIF function (TRUE) is the condition that defines the cells in ConditionListRange that COUNTIF counts.

When you set the Criteria argument of the COUNTIF function to TRUE, COUNTIF returns the number of cells in ConditionListRange that return TRUE.

Item: =1

The equal to operator (=) does the following:

  1. Compares 2 values; and
  2. Returns TRUE or FALSE as follows:
    • TRUE: If the compared values are equal.
    • FALSE: If the compared values aren't equal.

When you create an alternative to the XOR function that returns TRUE when only 1 condition is TRUE, = compares the following 2 values:

  • The number of cells in ConditionListRange that return TRUE, as returned by the COUNTIF function (COUNTIF(ConditionListRange,TRUE)); and
  • The number 1.

Therefore, = returns TRUE or FALSE as follows:

  • TRUE: If only 1 cell in ConditionListRange returns TRUE.
  • FALSE: Otherwise. In other words, if either:
    • No cell in ConditionListRange returns TRUE; or
    • 2 or more cells in ConditionListRange return TRUE.
XOR alternative to return TRUE when only 1 condition is TRUE formula example

The worksheet formulas below return TRUE when only 1 out of 5 conditions is TRUE as follows:

  • Condition1: The logical value specified in column A (A6 to A37).
  • Condition2: The logical value specified in column B (B6 to B37).
  • Condition3: The logical value specified in column C (C6 to C37).
  • Condition4: The logical value specified in column D (D6 to D37).
  • Condition5: The logical value specified in column E (E6 to E37).
No. XOR alternative formula
1 =COUNTIF(A6:E6,TRUE)=1
2 =COUNTIF(A7:E7,TRUE)=1
3 =COUNTIF(A8:E8,TRUE)=1
4 =COUNTIF(A9:E9,TRUE)=1
5 =COUNTIF(A10:E10,TRUE)=1
32 =COUNTIF(A37:E37,TRUE)=1
Effects of using XOR alternative to return TRUE when only 1 condition is TRUE formula example

The following image illustrates the results returns by the alternative to the XOR formula that returns TRUE when only 1 condition is TRUE. As expected, the formulas (in cells I6 to I37) return the following logical values:

  • TRUE: If only 1 argument (columns A through E) is TRUE.
  • FALSE: Otherwise. In other words, if either:
    • No argument (columns A through E) is TRUE; or
    • 2 or more arguments (columns A through E) are TRUE.

The image below contains a helper column (H) which displays the number of arguments that are TRUE in each row.

Learn more about working with the XOR function in Excel Workbook example used in this Excel XOR Tutorial

You can get immediate free access to the example workbook that accompany this Excel XOR Tutorial by entering your email address below. Alternatively, you can access all the files that accompany my Tutorials here.

References to constructs used in this Excel XOR Tutorial

Use the following links to visit the appropriate webpage in the Excel help center:

  1. Work with the XOR function.
  2. Count the number of cells in a cell range that return TRUE with the COUNTIF function.
  3. Test whether the number of cells (that are TRUE) returned by the COUNTIF function is equal to 1 with the equal to (=) operator.
Books about Excel and the Excel XOR function

Use the following links to visit the book's webpage in Amazon. PowerSpreadsheets.com is a participant in the Amazon Services LLC Associates Program, an affiliate advertising program designed to provide a means for sites to earn advertising fees by advertising and linking to Amazon.

The post Excel XOR Function Tutorial: Step-by-Step Guide and 3 Examples to Use Exclusive Or in Formulas appeared first on Power Spreadsheets.

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

In this Excel VLOOKUP Tutorial, you learn how to use the VLOOKUP function in your worksheet formulas to:

  1. Look for a value in the first column of a table; and
  2. Return a value in the same row but from another column you specify.

This Excel VLOOKUP Tutorial is accompanied by Excel workbooks containing the data and formulas I use in the examples below. You can get immediate free access to these example workbooks by entering your email address below. Alternatively, you can access all the files that accompany my Tutorials here.

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

Related Excel Tutorials

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

  • Formulas and functions:
    • Learn how to work with the IFERROR function here.
    • Learn how to work with the IFNA function here.
  • Macros and VBA:
    • Learn how to work with worksheet functions in VBA here.
    • Learn how to use the VLookup function in VBA here.

You can find additional Tutorials in the Archives.

#1: VLOOKUP exact match VLOOKUP exact match formula

To carry out a VLookup that looks up for an exact match, use a formula with the following structure:

=VLOOKUP(LookupValue,LookupTable,ColumnIndexNumber,FALSE)
VLOOKUP exact match process

To carry out a VLookup that looks up for an exact match, follow these steps:

  1. Specify the value you want to look up (LookupValue) in the first (leftmost) column of a table (LookupTable).
  2. Identify the cell range (a table array) containing the lookup table (LookupTable).
  3. Specify the index number of the column (within LookupTable) from which you want to obtain a value (ColumnIndexNumber).
  4. Specify that VLOOKUP searches for an exact match of LookupValue (FALSE).
VLOOKUP exact match formula explanation Item: VLOOKUP

The VLOOKUP function does the following:

  1. Looks for a value (LookupValue) in the first (leftmost) column of a table (LookupTable); and
  2. Returns a value in the same row but from another column you specify (ColumnIndexNumber).
Item: LookupValue

The lookup_value argument of the VLOOKUP function (LookupValue) is the value you look up in the first (leftmost) column of LookupTable. In other words, LookupValue must be in the first column of the cell range you specify as LookupTable.

You can specify LookupValue as either:

  • A value;
  • A text string; or
  • A cell reference.
Item: LookupTable

The table_array argument of the VLOOKUP function (LookupTable) is the cell range in which VLOOKUP searches for the following:

  1. The LookupValue in the first column of LookupTable; and
  2. The value to return in the column you specify (ColumnIndexNumber).

Therefore, the cell range you specify as LookupTable must include both of the following columns:

  • The first column, which must contain the LookupValue; and
  • The column from which VLOOKUP should return a value.
Item: ColumnIndexNumber

The col_index_num argument of the VLOOKUP function (ColumnIndexNumber) is the column number within the LookupTable from which VLOOKUP returns a value, as follows:

Column ColumnIndexNumber Comments
First 1 Must contain the LookupValue.
Second 2
Third 3
#th #
Item: FALSE

The range_lookup argument of the VLOOKUP function specifies whether VLOOKUP searches for an approximate or an exact match for LookupValue in the first column of LookupTable.

Set the range_lookup argument to FALSE when searching for an exact match.

VLOOKUP exact match formula example

The worksheet formulas below carry out a series of exact match VLookups as follows:

  • LookupValue: The value specified in cell I8 ($I$8).
  • LookupTable: The lookup table in cells A8 to E57 ($A$8:$E$57).
  • ColumnIndexNumber: The column number specified in column G (G9, G10, G11 and G12).
>No. VLookup formula
1 =VLOOKUP($I$8,$A$8:$E$57,G9,FALSE)
2 =VLOOKUP($I$8,$A$8:$E$57,G10,FALSE)
3 =VLOOKUP($I$8,$A$8:$E$57,G11,FALSE)
4 =VLOOKUP($I$8,$A$8:$E$57,G12,FALSE)
Effects of using VLOOKUP exact match formula example

The following image illustrates the results returned by the exact match VLookup formulas. As expected, the formulas (in cells I9 to I12) do the following:

  1. Look for the value (LookupValue) specified in cell I8 (Marie Kelly) in the first column (column A) of the lookup table (LookupTable).
  2. Return the values in the same row but from the columns specified in column G (ColumnIndexNumber), as follows:
    • Cell I9: The value in the second (G9) column of the lookup table (Surface Laptop).
    • Cell I10: The value in the third (G10) column of the lookup table (Chicago).
    • Cell I11: The value in the fourth (G11) column of the lookup table (300).
    • Cell I12: The value in the fifth (G12) column of the lookup table ($1,499).

#2: VLOOKUP approximate match VLOOKUP approximate match formula

To carry out a VLookup that looks up for an approximate match, use a formula with the following structure:

=VLOOKUP(LookupValue,LookupTable,ColumnIndexNumber,TRUE)
VLOOKUP approximate match process

To carry out a VLookup that looks up for an approximate match, follow these steps:

  1. Specify the value you want to look up (LookupValue) in the first (leftmost) column of a table (LookupTable).
  2. Identify the cell range (a table array) containing the lookup table (LookupTable).
  3. Specify the index number of the column (within Lookup Table) from which you want to obtain a value (ColumnIndexNumber).
  4. Specify that VLOOKUP searches for an approximate match of LookupValue (TRUE).
VLOOKUP approximate match formula explanation Item: VLOOKUP

The VLOOKUP function does the following:

  1. Looks for a value (LookupValue) in the first (leftmost) column of a table (LookupTable); and
  2. Returns a value in the same row but from another column you specify (ColumnIndexNumber).
Item: LookupValue

The lookup_value argument of the VLOOKUP function (LookupValue) is the value you look up in the first (leftmost) column of LookupTable. In other words, LookupValue must be in the first column of the cell range you specify as LookupTable.

You can specify LookupValue as either:

  • A value;
  • A text string; or
  • A cell reference.

When looking for an approximate match with VLOOKUP:

  • VLOOKUP searches for the closest match that is less than or equal to LookupValue in the first column of LookupTable; and
  • Therefore, the data in the first column of LookupTable must be sorted in ascending order
Item: LookupTable

The table_array argument of the VLOOKUP function (LookupTable) is the cell range in which VLOOKUP searches for the following:

  1. The closest match that is less than or equal to LookupValue in the first column of LookupTable; and
  2. The value to return in the column you specify (ColumnIndexNumber).

Therefore, the cell range you specify as LookupTable must include both of the following columns:

  • The first column, which must be sorted in ascending order; and
  • The column from which VLOOKUP should return a value.
Item: ColumnIndexNumber

The col_index_num argument of the VLOOKUP function (ColumnIndexNumber) is the column number within the LookupTable from which VLOOKUP returns a value, as follows:

Column ColumnIndexNumber Comments
First 1 Must be sorted in ascending order.
Second 2
Third 3
#th #
Item: TRUE

The range_lookup argument of the VLOOKUP function specifies whether VLOOKUP searches for an approximate or an exact match for LookupValue in the first column of LookupTable.

Set the range_lookup argument to TRUE when searching for an approximate match.

VLOOKUP approximate match formula example

The worksheet formulas below carry out a series of approximate match VLookups as follows:

  • LookupValue: The value specified in column F (F8, F9, F10, F11, F12, …, F57).
  • LookupTable: The lookup table in cells K8 to L14 ($K$8:$L$14).
  • ColumnIndexNumber: 2.
No. VLookup formula
1 =VLOOKUP(F8,$K$8:$L$14,2,TRUE)
2 =VLOOKUP(F9,$K$8:$L$14,2,TRUE)
3 =VLOOKUP(F10,$K$8:$L$14,2,TRUE)
4 =VLOOKUP(F11,$K$8:$L$14,2,TRUE)
5 =VLOOKUP(F12,$K$8:$L$14,2,TRUE)
50 =VLOOKUP(F57,$K$8:$L$14,2,TRUE)
Effects of using VLOOKUP approximate match formula example

The following image illustrates the results returned by the approximate match VLookup formulas. As expected, the formulas (in cells G8 to G57) do the following:

  1. Look for an approximate match (the closest match that is less than or equal) to the value (LookupValue) specified in column F (Total Sales) in the first column (column K) of the lookup table (LookupTable).
  2. Return the value in the same row as the approximate match but from the second column (2) of the lookup table (Commission).

#3: VLOOKUP with multiple criteria VLOOKUP with multiple criteria formula

To carry out a VLookup that considers multiple criteria, use a formula with the following structure:

=VLOOKUP(ConcatenatedLookupValue,LookupTableWithHelperColumn,ColumnIndexNumber,FALSE)

When carrying out a VLookup with multiple criteria, do the following:

  1. Add a helper column as the first (leftmost) column of the lookup table.
  2. In this helper column, concatenate the contents of the cells containing the multiple criteria your VLookup considers.

To (i) concatenate the contents of the cells containing the multiple criteria and (ii) build the concatenated value you look up for, use a formula with the following structure:

=Criteria1&Criteria2&...&Criteria#
VLOOKUP with multiple criteria process

To carry out a VLookup that considers multiple criteria, follow these steps:

  1. Identify the cell range (a table array) containing the lookup table.
  2. Add a helper column as the first (leftmost) column of the lookup table.
  3. Use the helper column to concatenate the contents of the cells containing the multiple criteria your VLookup considers (Criteria1&Criteria2&…&Criteria#).
  4. Concatenate the multiple criteria you want to look up (Criteria1&Criteria2&…&Criteria#) and use this single concatenated string to specify the value you want to look up (ConcatenatedLookupValue) in the helper column of the lookup table.
  5. Identify the lookup table (LookupTableWithHelperColumn) including the helper column.
  6. Specify the index number of the column (within LookupTableWithHelperColumn) from which you want to obtain a value (ColumnIndexNumber).
  7. Specify that VLOOKUP searches for an exact match of ConcatenatedLookupValue (FALSE).
VLOOKUP with multiple criteria formula explanation Formula #1: VLOOKUP(ConcatenatedLookupValue,LookupTableWithHelperColumn,ColumnIndexNumber,FALSE) Item: VLOOKUP

The VLOOKUP function does the following:

  1. Looks for a value built by concatenating multiple criteria (ConcatenatedLookupValue) in a helper (the first or leftmost) column of a table (LookupTableWithHelperColumn); and
  2. Returns a value in the same row but from another column you specify (ColumnIndexNumber).
Item: ConcatenatedLookupValue

The lookup_value argument of the VLOOKUP function (ConcatenatedLookupValue) is the value you look up in the helper column of LookupTableWithHelperColumn. Therefore:

  • Build ConcatenatedLookupValue by concatenating the multiple criteria.
  • ConcatenatedLookupValue must be in the first column of the cell range you specify as LookupTableWithHelperColumn. For these purposes, do the following:
    • Add a helper column as the first (leftmost) column of LookupTableWithHelperColumn.
    • In this helper column, concatenate the contents of the cells containing the multiple criteria.

You can use the text concatenation operator (&) (as explained below) to both:

  • Specify ConcatenatedLookupValue; and
  • Build the helper column in LookupTableWithHelperColumn.
Item: LookupTableWithHelperColumn

The table_array argument of the VLOOKUP function (LookupTableWithHelperColumn) is the cell range in which VLOOKUP searches for the following:

  1. The ConcatenatedLookupValue in the helper (first) column of LookupTableWithHelperColumn; and
  2. The value to return in the column you specify (ColumnIndexNumber).

Therefore, the cell range you specify as LookupTableWithHelperColumn must include both of the following columns:

  • The helper (first) column, which must contain the ConcatenatedLookupValue; and
  • The column from which VLOOKUP should return a value.
Item: ColumnIndexNumber

The col_index_num argument of the VLOOKUP function (ColumnIndexNumber) is the column number within the LookupTableWithHelperColumn from which VLOOKUP returns a value, as follows:

Column ColumnIndexNumber Comments
First 1 Helper column built by concatenating the applicable multiple criteria.
Second 2
Third 3
#th #
Item: FALSE

The range_lookup argument of the VLOOKUP function specifies whether VLOOKUP searches for an approximate or an exact match for ConcatenatedLookupValue in the first column of LookupTableWithHelperColumn.

Set the range_lookup argument to FALSE when searching for an exact match for multiple criteria.

Formula #2: Criteria1&Criteria2&…&Criteria#

The text concatenation operator (&) joins or concatenates several strings to produce a single continuous string.

When carrying out a VLookup with multiple criteria, use the & operator to:

  1. Concatenate the applicable multiple criteria (Criteria1, Criteria2, …, Criteria#); and
  2. Produce a single continuous string containing all the applicable criteria (Criteria1&Criteria2&…&Criteria#).

Follow this formula structure for both:

  • Specifying ConcatenatedLookupValue; and
  • Building the helper column in LookupTableWithHelperColumn.
VLOOKUP with multiple criteria formula example

The worksheet formulas below carry out a series of VLookups that consider multiple criteria as follows:

  • ConcatenatedLookupValue: The value specified in cell L8 ($L$8). This value is built by concatenating 2 criteria, following the same logic as the helper formulas I explain below.
  • LookupTableWithHelperColumn: The lookup table in cells B8 to H57 ($B$8:$H$57), where column B is a helper column.
  • ColumnIndexNumber: The column number specified in column J (J9, J10, J11, J12, J13 and J14).
No. VLookup formula
1 =VLOOKUP($L$8,$B$8:$H$57,J9,FALSE)
2 =VLOOKUP($L$8,$B$8:$H$57,J10,FALSE)
3 =VLOOKUP($L$8,$B$8:$H$57,J11,FALSE)
4 =VLOOKUP($L$8,$B$8:$H$57,J12,FALSE)
5 =VLOOKUP($L$8,$B$8:$H$57,J13,FALSE)
6 =VLOOKUP($L$8,$B$8:$H$57,J14,FALSE)

The helper column in the lookup table (column B) contains a worksheet formula that concatenates 2 criteria, which are specified in columns C and D. Therefore, the worksheet formulas in the helper column are as follows:

No. Helper formula
1 =C8&D8
2 =C9&D9
3 =C10&D10
4 =C11&D11
5 =C12&D12
50 =C57&D57
Effects of using VLOOKUP with multiple criteria formula example

The following image illustrates the results returned by the VLookup formulas with multiple criteria. As expected, the formulas (in cells L9 to L14) do the following:

  1. Look for the value (ConcatenatedLookupValue) specified in cell L8 by concatenating 2 criteria (MarieKelly) in the helper column (column B) of the lookup table (LookupTableWithHelperColumn).
  2. Return the values in the same row but from the columns specified in column J (ColumnIndexNumber), as follows:
    • Cell L9: The value in the second (J9) column of the lookup table with helper column (Marie).
    • Cell L10: The value in the third (J10) column of the lookup table with helper column (Kelly).
    • Cell L11: The value in the fourth (J11) column of the lookup table with helper column (Surface Laptop).
    • Cell L12: The value in the fifth (J12) column of the lookup table with helper column (Chicago).
    • Cell L13: The value in the sixth (J13) column of the lookup table with helper column (300).
    • Cell L14: The value in the seventh (J14) column of the lookup table with helper column ($1,499).

#4: VLOOKUP for second, third, or nth match VLOOKUP for second, third, or nth match formula

To carry out a VLookup that looks up the second, third, or nth match, use a formula with the following structure:

=VLOOKUP(LookupValue&Match#,LookupTableWithHelperColumn,ColumnIndexNumber,FALSE)

When carrying out a VLookup for the second, third, or nth match, do the following:

  1. Add a helper column as the first (leftmost) column of the lookup table.
  2. In this helper column, build a unique identifier by concatenating:
    • The contents in the column containing the criterion your VLookup considers; and
    • A counter that counts the number of times the contents in the current row have appeared in the column containing the criterion your VLookup considers.

To build this unique identifier, use a formula with the following structure:

=CellInCriteriaColumnRelativeRef&COUNTIF(FirstCellInCriteriaColumnAbsoluteRef:CellInCriteriaColumnRelativeRef,CellInCriteriaColumnRelativeRef)
VLOOKUP for second, third, or nth match process

To carry out a VLookup that looks up the second, third, or nth match, follow these steps:

  1. Identify the cell range (a table array) containing the lookup table.
  2. Add a helper column as the first (leftmost) column of the lookup table.
  3. Use the helper column to build a unique identifier by concatenating (i) the contents in the column containing the criterion your VLookup considers, and (ii) a counter that counts the number of times the contents in the current row have appeared in the column containing the criterion your VLookup considers.
  4. Concatenate the value and match number you want to look up and use this single concatenated string to specify the value you want to look up (LookupValue&Match#) in the helper column of the lookup table.
  5. Identify the lookup table (LookupTableWithHelperColumn) including the helper column.
  6. Specify the index number of the column (within LookupTableWithHelperColumn) from which you want to obtain a value (ColumnIndexNumber).
  7. Specify that VLOOKUP searches for an exact match of LookupValue&Match# (FALSE).
VLOOKUP for second, third, or nth match formula explanation Formula #1: VLOOKUP(LookupValue&Match#,LookupTableWithHelperColumn,ColumnIndexNumber,FALSE) Item: VLOOKUP

The VLOOKUP function does the following:

  1. Looks for a value built by concatenating the criterion your VLookup considers and the match you look for (LookupValue&Match#) in a helper (the first or leftmost) column of a table (LookupTableWithHelperColumn); and
  2. Returns a value in the same row but from another column you specify (ColumnIndexNumber).
Item: LookupValue&Match#

The lookup_value argument of the VLOOKUP function (LookupValue&Match#) is the value you look up in the helper column of LookupTableWithHelperColumn. When looking for a specific match, build this argument by concatenating:

  • The value you look for (LookupValue); and
  • The match you look for (Match#).

If both LookupValue and Match# are stored in a cell, build this lookup_value argument with the following 3 items:

  1. LookupValue: The value you look for.
  2. &: The concatenation operator.
  3. Match#: The match you look for.

In other words, & concatenates LookupValue and Match# to produce a single continuous string containing both the value and match you look for.

LookupValue&Match# must be in the first column of LookupTableWithHelperColumn. For these purposes, do the..

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

In this VBA Tutorial, you learn how to refer to, and work with, sheets and worksheets in macros. This includes:

  1. How to refer to all sheets in a workbook.
  2. How to refer to all worksheets in a workbook.
  3. How to refer to the active sheet.
  4. How to refer to a sheet by its index number.
  5. How to refer to a worksheet by its index number.
  6. How to refer to a sheet by its name.
  7. How to refer to a worksheet by its name.
  8. How to refer to a sheet by its code name.
  9. How to refer to several sheets.
  10. How to refer to several worksheets.
  11. How to loop through all sheets in a workbook with the For Each… Next loop.
  12. How to loop through all worksheets in a workbook with the For Each… Next loop.
  13. How to loop through all sheets in a workbook with the For… Next loop.
  14. How to loop through all worksheets in a workbook with the For… Next loop.
  15. How to loop through all sheets in a workbook in reverse order.
  16. How to loop through all worksheets in a workbook in reverse order.

This VBA Tutorial is accompanied by an Excel workbook containing the macros I use in the examples below. You can get immediate free access to this example workbook by entering your email address below. Alternatively, you can access all the files that accompany my Tutorials here.

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

  • General VBA constructs and structures:
    • Learn the basics of working with macros here.
    • Learn about basic VBA terms and constructs here.
    • Learn how to enable or disable macros here.
    • Learn how to work with the Visual Basic Editor here.
    • Learn how to create Sub procedures here.
    • Learn how to create object references here.
    • Learn how to work with object properties here.
    • Learn how to work with object methods here.
    • Learn how to declare and work with variables here.
    • Learn about VBA data types here.
    • Learn how to work with arrays here.
    • Learn how to work with loops here.
  • Practical VBA applications and macro examples:
    • Learn how to delete sheets and worksheets here.

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

#1: Refer to all sheets in workbook VBA code to refer to all sheets in workbook

To refer to all sheets in a workbook with VBA, use an object reference with the following structure:

Workbook.Sheets
Process to refer to all sheets in workbook

To refer to all sheets in a workbook with VBA, follow these steps:

  1. Identify the workbook containing the sheets (Workbook).
  2. Refer to the Sheets collection representing all sheets in Workbook (Sheets).
VBA statement explanation Item: Workbook

Workbook object representing the Excel workbook containing the sheets you refer to.

You can usually work with one of the following properties to refer to this Workbook object:

  • Application.ActiveWorkbook.
  • Application.ThisWorkbook.
  • Application.Workbooks.
Item: Sheets

The Workbook.Sheets property returns a Sheets collection representing all sheets in Workbook. The Sheets collection can, generally, contain both:

  • Chart objects, where each Chart object represents an individual chart sheet; or
  • Worksheet objects, where each Worksheet object represents an individual worksheet.
Macro example to refer to all sheets in workbook

The following macro example displays a message box (MsgBox) with the number of sheets (Sheets.Count) in the workbook where the macro is stored (ThisWorkbook).

Sub referToSheetsCollection()
    'source: https://powerspreadsheets.com/
    'displays a message box with the number of sheets in this workbook
    'for further information: https://powerspreadsheets.com/excel-vba-sheets-worksheets/

    'display message box with number of sheets in this workbook
    MsgBox ThisWorkbook.Sheets.Count

End Sub



Effects of executing macro example to refer to all sheets in workbook

The following GIF illustrates the results of executing the macro example. The workbook where the macro is stored contains 5 worksheets (Sheet1 through Sheet5) and 5 chart sheets (Chart1 through Chart5). Therefore, Excel displays a message box with the number 10.

#2: Refer to all worksheets in workbook VBA code to refer to all worksheets in workbook

To refer to all worksheets in a workbook with VBA, use an object reference with the following structure:

Workbook.Worksheets
Process to refer to all worksheets in workbook

To refer to all worksheets in a workbook with VBA, follow these steps:

  1. Identify the workbook containing the worksheets (Workbook).
  2. Refer to the Sheets collection representing all worksheets in Workbook (Worksheets).
VBA statement explanation Item: Workbook

Workbook object representing the Excel workbook containing the worksheets you refer to.

You can usually work with one of the following properties to refer to this Workbook object:

  • Application.ActiveWorkbook.
  • Application.ThisWorkbook.
  • Application.Workbooks.
Item: Worksheets

The Workbook.Worksheets property returns a Sheets collection representing all worksheets in Workbook.

Macro example to refer to all worksheets in workbook

The following macro example displays a message box (MsgBox) with the number of worksheets (Worksheets.Count) in the workbook where the macro is stored (ThisWorkbook).

Sub referToWorksheetsCollection()
    'source: https://powerspreadsheets.com/
    'displays a message box with the number of worksheets in this workbook
    'for further information: https://powerspreadsheets.com/excel-vba-sheets-worksheets/

    'display message box with number of worksheets in this workbook
    MsgBox ThisWorkbook.Worksheets.Count

End Sub
Effects of executing macro example to refer to all worksheets in workbook

The following GIF illustrates the results of executing the macro example. The workbook where the macro is stored contains 5 worksheets (Sheet1 through Sheet5). Therefore, Excel displays a message box with the number 5.

#3: Refer to active sheet VBA code to refer to active sheet

To refer to the active sheet with VBA, use an object reference with the following structure:

Workbook.ActiveSheet
Process to refer to active sheet

To refer to the active sheet with VBA, follow these steps:

  1. Identify the workbook containing the sheet (Workbook). If you don't identify Workbook, VBA works with the active workbook.
  2. Refer to the active sheet in Workbook (ActiveSheet).
VBA statement explanation Item: Workbook

Workbook object representing the Excel workbook containing the active sheet you refer to.

You can usually work with one of the following properties to refer to this Workbook object:

  • Application.ActiveWorkbook.
  • Application.ThisWorkbook.
  • Application.Workbooks.

If you don't specify Workbook when referring to the active sheet with ActiveSheet, VBA works with the active workbook (the workbook on top).

Item: ActiveSheet

The ActiveSheet returns an object representing the active sheet (the sheet on top) in Workbook, as follows:

  • If you specify Workbook, ActiveSheet returns an object representing the active sheet in Workbook.
  • If you don't specify Workbook, ActiveSheet returns an object representing the active sheet in the active workbook (the workbook on top).
Macro example to refer to active sheet

The following macro example displays a message box (MsgBox) with the name (Name) of the active sheet in the active workbook (ActiveSheet).

Sub referToActiveSheet()
    'source: https://powerspreadsheets.com/
    'displays a message box with the name of the active sheet
    'for further information: https://powerspreadsheets.com/excel-vba-sheets-worksheets/

    'display message box with name of active sheet
    MsgBox ActiveSheet.Name

End Sub



Effects of executing macro example to refer to active sheet

The following GIF illustrates the results of executing the macro example. As expected, Excel displays a message box with the name of the active sheet (Sheet1).

#4: Refer to sheet by index number VBA code to refer to sheet by index number

To refer to a sheet by its index number with VBA, use an object reference with the following structure:

Workbook.Sheets(SheetIndexNumber)
Process to refer to sheet by index number

To refer to a sheet by its index number with VBA, follow these steps:

  1. Identify the workbook containing the sheet (Workbook).
  2. Identify the sheet by its index number (Sheets(SheetIndexNumber)).
VBA statement explanation Item: Workbook

Workbook object representing the Excel workbook containing the sheet you refer to.

You can usually work with one of the following properties to refer to this Workbook object:

  • Application.ActiveWorkbook.
  • Application.ThisWorkbook.
  • Application.Workbooks.
Item: Sheets(SheetIndexNumber)

The Workbook.Sheets property returns a Sheets collection representing all sheets in Workbook. The Sheets collection can, generally, contain both:

  • Chart objects, where each Chart object represents an individual chart sheet; or
  • Worksheet objects, where each Worksheet object represents an individual worksheet.

SheetIndexNumber is the index number of the sheet you refer to. This index number represents the position of the sheet in the tab bar of Workbook, from left to right. For these purposes, the count usually includes:

  • Hidden sheets; and
  • Both chart sheets and worksheets.

Therefore, Sheets(SheetIndexNumber) usually returns an individual Chart or Worksheet object representing the chart sheet or worksheet whose index number is SheetIndexNumber.

Macro example to refer to sheet by index number

The following macro example activates (Activate) the fifth sheet (Sheets(5)) in the workbook where the macro is stored (ThisWorkbook).

Sub referToSheetIndex()
    'source: https://powerspreadsheets.com/
    'activates the fifth sheet in this workbook
    'for further information: https://powerspreadsheets.com/excel-vba-sheets-worksheets/

    'activate fifth sheet in this workbook
    ThisWorkbook.Sheets(5).Activate

End Sub
Effects of executing macro example to refer to sheet by index number

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

When the macro is executed, the active sheet is Sheet1. As expected, Excel activates the fifth sheet (Chart1).

#5: Refer to worksheet by index number VBA code to refer to worksheet by index number

To refer to a worksheet by its index number with VBA, use an object reference with the following structure:

Workbook.Worksheets(WorksheetIndexNumber)
Process to refer to worksheet by index number

To refer to a worksheet by its index number with VBA, follow these steps:

  1. Identify the workbook containing the worksheet (Workbook).
  2. Identify the worksheet by its index number (Worksheets(WorksheetIndexNumber)).
VBA statement explanation Item: Workbook

Workbook object representing the Excel workbook containing the worksheet you refer to.

You can usually work with one of the following properties to refer to this Workbook object:

  • Application.ActiveWorkbook.
  • Application.ThisWorkbook.
  • Application.Workbooks.
Item: Worksheets(WorksheetIndexNumber)

The Workbook.Worksheets property returns a Sheets collection representing all worksheets in Workbook.

WorksheetIndexNumber is the index number of the worksheet you refer to. This index number represents the position of the worksheet in the tab bar of Workbook, from left to right. For these purposes, the count usually:

  • Includes hidden worksheets; but
  • Doesn't include chart sheets.

Therefore, Worksheets(WorksheetIndexNumber) returns an individual Worksheet object representing the worksheet whose index number is WorksheetIndexNumber.

Macro example to refer to worksheet by index number

The following macro example activates (Activate) the first worksheet (Worksheets(1)) in the workbook where the macro is stored (ThisWorkbook).

Sub referToWorksheetIndex()
    'source: https://powerspreadsheets.com/
    'activates the first worksheet in this workbook
    'for further information: https://powerspreadsheets.com/excel-vba-sheets-worksheets/

    'activate first worksheet in this workbook
    ThisWorkbook.Worksheets(1).Activate

End Sub



Effects of executing macro example to refer to worksheet by index number

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

When the macro is executed, the active sheet is Sheet5. As expected, Excel activates the first worksheet (Sheet1).

#6: Refer to sheet by name VBA code to refer to sheet by name

To refer to a sheet by its name with VBA, use an object reference with the following structure:

Workbook.Sheets("SheetName")
Process to refer to sheet by name

To refer to a sheet by its name with VBA, follow these steps:

  1. Identify the workbook containing the sheet (Workbook).
  2. Identify the sheet by its name (Sheets(“SheetName”)).
VBA statement explanation Item: Workbook

Workbook object representing the Excel workbook containing the sheet you refer to.

You can usually work with one of the following properties to refer to this Workbook object:

  • Application.ActiveWorkbook.
  • Application.ThisWorkbook.
  • Application.Workbooks.
Item: Sheets(“SheetName”)

The Workbook.Sheets property returns a Sheets collection representing all sheets in Workbook. The Sheets collection can, generally, contain both:

  • Chart objects, where each Chart object represents an individual chart sheet; or
  • Worksheet objects, where each Worksheet object represents an individual worksheet.

“SheetName” is a string representing the name of the sheet you refer to, as displayed in the sheet's tab. If you explicitly declare a variable to represent “SheetName”, you can usually declare it as of the String data type.

Therefore, Sheets(“SheetName”) usually returns an individual Chart or Worksheet object representing the chart sheet or worksheet whose name is SheetName.

Macro example to refer to sheet by name

The following macro example activates (Activate) the sheet named “Chart1” (Sheets(“Chart1”)) in the workbook where the macro is stored (ThisWorkbook).

Sub referToSheetName()
    'source: https://powerspreadsheets.com/
    'activates the sheet named "Chart1" in this workbook
    'for further information: https://powerspreadsheets.com/excel-vba-sheets-worksheets/

    'activate Chart1 in this workbook
    ThisWorkbook.Sheets("Chart1").Activate

End Sub
Effects of executing macro example to refer to sheet by name

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

When the macro is executed, the active sheet is Sheet1. As expected, Excel activates Chart1.

#7: Refer to worksheet by name VBA code to refer to worksheet by name

To refer to a worksheet by its name with VBA, use an object reference with the following structure:

Workbook.Worksheets("WorksheetName")
Process to refer to worksheet by name

To refer to a worksheet by its name with VBA, follow these steps:

  1. Identify the workbook containing the worksheet (Workbook).
  2. Identify the worksheet by its name (Worksheets(“WorksheetName”)).
VBA statement explanation Item: Workbook

Workbook object representing the Excel workbook containing the worksheet you refer to.

You can usually work with one of the following properties to refer to this Workbook object:

  • Application.ActiveWorkbook.
  • Application.ThisWorkbook.
  • Application.Workbooks.
Item: Worksheets(“WorksheetName”)

The Workbook.Worksheets property returns a Sheets collection representing all worksheets in Workbook.

“WorksheetName” is a string representing the name of the worksheet you refer to, as displayed in the worksheet's tab. If you explicitly declare a variable to represent “WorksheetName”, you can usually declare it as of the String data type.

Therefore, Worksheets(“WorksheetName”) returns an individual Worksheet object representing the worksheet whose name is WorksheetName.

Macro example to refer to worksheet by name

The following macro example activates (Activate) the worksheet named “Sheet1” (Worksheets(“Sheet1”)) in the workbook where the macro is stored (ThisWorkbook).

Sub referToWorksheetName()
    'source: https://powerspreadsheets.com/
    'activates the worksheet named "Sheet1" in this workbook
    'for further information: https://powerspreadsheets.com/excel-vba-sheets-worksheets/

    'activate Sheet1 in this workbook
    ThisWorkbook.Worksheets("Sheet1").Activate

End Sub



Effects of executing macro example to refer to worksheet by name

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

When the macro is executed, the active sheet is Chart1. As expected, Excel activates Sheet1.

#8: Refer to sheet by code name VBA code to refer to sheet by code name

To refer to a sheet by its code name with VBA, use the sheet's code name:

SheetCodeName
Process to refer to sheet by code name

To refer to a sheet by its code name with VBA, use the sheet's code name.

VBA statement explanation Item: SheetCodeName

SheetCodeName is the code name of the sheet you refer to.

You can use a sheet's code name instead of an object reference (such as the ones I explain in other sections of this VBA Tutorial) returning the Chart or Sheet object you refer to.

Macro example to refer to sheet by code name

The following macro example activates (Activate) the worksheet whose code name is Sheet1 (Sheet1).

Sub referToSheetCodeName()
    'source: https://powerspreadsheets.com/
    'activates Sheet1 in this workbook
    'for further information: https://powerspreadsheets.com/excel-vba-sheets-worksheets/

    'activate Sheet1 in this workbook
    Sheet1.Activate

End Sub
Effects of executing macro example to refer to sheet by code name

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

When the macro is executed, the active sheet is Sheet5. As expected, Excel activates Sheet1 (both the name and code name are Sheet1).

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

In this Power Query (Get & Transform) Tutorial for beginners, you learn how to use Power Query (Get & Transform) to automate the process of importing and consolidating data in Excel.

The following are the main topics you learn about:

  • What Power Query is and how it can help you.
  • What data sources can Power Query import data from.
  • How to import data from 3 commonly-used data sources (Excel workbooks, CSV and text files) with Power Query, including the following:
    • How to import single or multiple data sources from an Excel workbook.
    • How to import data from CSV or text files.
    • How to edit a query during the process of importing data with Power Query.
    • Where and how to load the data you import with Power Query.
    • How to combine and consolidate the data you import from different files with Power Query.

This Power Query Tutorial is accompanied by several example files containing the data, queries and connections I use in the examples below. You can get immediate free access to these example files by entering your email address below. Alternatively, you can access all the files that accompany my Tutorials here.

This Power Query Tutorial was most recently updated in March 2018 with Excel Version 1802 (Build 9029.2253 Click-to-Run). Microsoft is constantly updating/modifying Power Query. If you're working with a different version of Excel/Power Query, some details (particularly menus) are different. The principles and basic processes you learn in this Power Query Tutorial, are generally applicable.

Related Tutorials

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

  • Tips and tricks:
    • Learn how to work with keyboard shortcuts here.
    • Learn 350+ Excel keyboard shortcuts here.
    • Learn several ways to convert PDF files to Excel here.
  • Formulas and functions:
    • Learn the basics of cleaning data by working with Text functions here.
  • Macros and VBA:
    • Learn the basics of working with macros here.
    • Learn about basic VBA constructs here.
    • Learn how to convert PDF files to Excel with VBA here.

You can find additional Tutorials in the Archives.

What Power Query (Get & Transform) is

One of the first steps in the process of analyzing data in Excel is importing it. You can achieve this in several ways. Common approaches involve manually importing data or using VBA to automate the process. These approaches may not be the most appropriate in several cases.

  • The process of manually importing data into Excel is generally slow, repetitive, tedious and error-prone.
  • Macros and VBA can help you automate a lot of your data importing processes. This approach, however, requires some programming knowledge and, depending on your situation, you may need to spend time maintaining the procedures you use.

Power Query (Get & Transform) allows you to import data into Excel. Power Query offers several advantages (vs. the common approaches I list above), including the following:

  • Like VBA, Power Query allows you to automate the process of importing data.
  • However, when compared with VBA, Power Query is generally easier to work with and maintain. Power Query also tends to result in better performance (vs. VBA).

Overall, you can think of Power Query as an ETL tool. In other words, Power Query allows you to:

  • Extract: Use Power Query to discover and connect to a variety of data sources.
  • Transform: Transform the extracted data by, for example, combining or refining it.
  • Load: Share the transformed data.
Data sources you can connect to with Power Query (Get & Transform)

You can connect Power Query to different data sources, including the following:

  • Files: Excel files, Text or CSV files, XML files, and JSON files.
  • Databases: Microsoft SQL Server, Microsoft Access, SQL Server Analysis Services.
  • Other example data sources: Microsoft Exchange, Facebook, SalesForce, Excel Tables or named ranges, web pages, and OData feeds.

From a broad perspective, the 2 data source types you're most likely to work with are:

  • Relational databases; and
  • Files.

This Power Query Tutorial focuses on working with the 3 following common file types:

  1. Excel workbooks.
  2. CSV (comma-separated value) files.
  3. Text files.

As you learn in the following sections, Power Query usually imports the data within a CSV or text file as follows:

  • Each line within the file is a row of data.
  • Columns are determined by the appropriate delimiter (for example, comma in CSV files or tab in tab-delimited files).
File examples for this Power Query (Get & Transform) Tutorial

You can access the example files that accompany this Power Query Tutorial by entering your email address below. Alternatively, you can access all the files that accompany my Tutorials here.

There are 3 source file examples:

  1. Excel workbook.
  2. CSV file.
  3. Text file.

These source file examples contain several thousand rows with the following (fictional) sales data:

  • Date: Between January 1 of 2018 and December 31 of 2019.
  • Item: 1 of the following Microsoft devices:
    • Surface Pro.
    • Surface Laptop.
    • Surface Book 2.
    • Surface Studio.
    • Xbox One X.
  • Store: I assume there's 1 store per city listed below. I divide the cities in 4 regions (Midwest, Northeast, South and West). The source files contain 20,000 rows of data for each region.
    • Midwest: Chicago, Detroit and Minneapolis.
    • Northeast: Boston, New York and Philadelphia.
    • South: Atlanta, Dallas, Houston, Miami, Tampa and Washington D.C.
    • West: Los Angeles, Phoenix, San Diego, San Francisco and Seattle.
  • Units sold: Between 1 and 5.
  • Unit price: I assume the price remains constant regardless of the store location and date of sale:
  • Total sales: The product of units sold times unit price (UnitsSold x UnitPrice).

The following are the differences between the different source file examples.

  • Workbook: The workbook example has 4 worksheets. Each worksheet corresponds to 1 of the regions I list above (Midwest, Northeast, South or East). Additionally:
    • The Midwest source data is formatted as an Excel Table (myTableSource).
    • The cell range containing the Northeast source data is a named cell range (myNamedRangeSource).
  • CSV: The CSV file examples only has data corresponding to the South region. This is the same data as that in the South worksheet of the example workbook.
  • Text: This is a tab-delimited text file containing (only) data corresponding to the West region. This is the same data as that in the West worksheet of the example workbook.

The source data isn't formatted. Further below, I explain how you can use Power Query to format the data.

If you use the file examples that accompany this Power Query Tutorial, you can't simply refresh the data extracted by Power Query from the main workbook (which has the queries and connections). The path of the source files you download will be different from the one I specified in the workbook example. The result is a data source error.

Therefore, I suggest you recreate the queries yourself by following the step-by-step processes I describe below.

Import data with Power Query (Get & Transform)

In the following sections, I explain the basic process you can follow to import data from an Excel workbook, CSV or text file using Power Query.

The essential steps are similar. Power Query has several options and features you can use while importing data. However, these topics exceed the scope of this Power Query Tutorial for beginners.

#1: Import a single data source from a workbook with Power Query

To import a single data source from an Excel workbook with Power Query, follow these 6 steps:

  1. Go to Ribbon > Data > Get Data > From File > From Workbook.
  2. Power Query displays the Import Data dialog box.
  3. Identify the source workbook and double-click on it.
  4. Power Query displays the Navigator dialog box.
  5. Select the data source you want to work with.
  6. Click Load.
Example of how to import a single data source from a workbook with Power Query

The following GIF illustrates the process to import a single data source from an Excel workbook with Power Query.

#2: Import data from multiple data sources in a workbook with Power Query

To import data from multiple data sources within a workbook with Power Query, follow these 7 steps:

  1. Go to Ribbon > Data > Get Data > From File > From Workbook.
  2. Power Query displays the Import Data dialog box.
  3. Identify the source workbook and double-click on it.
  4. Power Query displays the Navigator dialog box.
  5. Select “Select multiple items”.
  6. Select the data sources you want to work with.
  7. Click Load.
Example of how to import data from multiple data sources in a workbook with Power Query

The following GIF illustrates the process to import data from multiple data sources in a workbook with Power Query.

#3: Import data from a CSV file with Power Query

To import data from a CSV file to Excel with Power Query, follow these 5 steps:

  1. Go to Ribbon > Data > Get Data > From Text/CSV.
  2. Power Query displays the Import Data dialog box.
  3. Identify the source CSV file and double-click on it.
  4. Power Query displays a dialog box named after the CSV file.
  5. Click Load.
Example of how to import data from a CSV file with Power Query

The following GIF illustrates the process to import data from a CSV file with Power Query.

#4: Import data from a text file with Power Query

To import data from a text file to Excel with Power Query, follow these 5 steps:

  1. Go to Ribbon > Data > Get Data > From Text/CSV.
  2. Power Query displays the Import Data dialog box.
  3. Identify the source text file and double-click on it.
  4. Power Query displays a dialog box named after the text file.
  5. Click Load.
Example of how to import data from a text file with Power Query

The following GIF illustrates the process to import data from a text file with Power Query.

General considerations about the process to import data with Power Query Begin the process of importing data with Power Query

You begin the process of creating a query with Power Query as follows:

  • When the source data is in an Excel workbook:
    • Go to Ribbon > Data > Get Data > From File > From Workbook; or
    • Use the keyboard shortcut “Alt, A, PN, F, W”.
  • When the source data is in a text or CSV file:
    • Go to Ribbon > Data > Get Data > From Text/CSV; or
    • Use the keyboard shortcut “Alt, A, FT”.

Working with the Import Data dialog box

The Import Data dialog box is similar to other dialog boxes you work with when carrying out common Excel processes, such as opening or saving a workbook. Therefore, you can do the following:

  1. Browse to the folder where the file (workbook, CSV or text) is saved.
  2. Double-click on the appropriate file.

The file-filtering criteria applied by Excel depend on the type of file you're working with (Excel workbook vs. CSV or text). Therefore:

  • When you work with an Excel workbook:
    • The Import Data dialog box usually displays any type of Excel file. This includes, for example, regular Excel workbooks, templates and macro-enabled workbooks.
    • Regardless of the precise Excel file type you work with, Power Query limits itself to working with data in cells. Therefore, items such as PivotTables, charts or macros aren't imported.
  • When you work with a CSV file: The Import Data dialog box displays only CSV files.
  • When you work with a text file: The Import Data dialog box displays only text files.
Working with the Navigator dialog box

Power Query displays the Navigator dialog box when you work with an Excel workbook. The Navigator dialog box has 3 main sections:

  1. Left: Available data sources.
  2. Right: Preview.
  3. Right bottom: Load, Edit and Cancel buttons.

Available data sources

On the left side of the Navigator dialog box, Power Query lists the data sources you can select inside the workbook you're working with (selected with the Import Data dialog box).

When working with an Excel workbook, these data sources are generally 1 of the following 3:

  1. A worksheet.
  2. An Excel Table.
  3. A named range. It's possible to connect Power Query to, for example, dynamic named ranges. The process to work with this type of named range, however, differs from what I describe in this Power Query Tutorial.

You can distinguish these different data sources based on the icon displayed by Power Query next to the source.

If you want to select multiple data sources from a single workbook:

  1. Mark the checkbox next to “Select multiple items” on the top left side of the Navigator dialog box prior to selecting the data sources.
  2. Once you enable the option to select multiple items, Power Query displays checkboxes to the left of all data sources in the workbook. Use these checkboxes to select all the data sources you want to import.

Preview

Once you select a data source from the list of available data sources, Power Query displays a preview of the data.

Use this preview to confirm that the source data you chose is correct.

Load, Edit and Cancel buttons

Use these buttons to do any of the following:

  • Load: Load the data. You can specify how and where the data is loaded by following the process I describe further below.
  • Edit: Launch the Query Editor and edit your query. You learn the basics of working with the Query Editor further below.
  • Cancel: Close the dialog box and cancel the process of importing data with Power Query.

Working with the dialog box named after the source CSV or text file

Power Query displays a dialog box named after the source file when you work with CSV or text files. This dialog box has 3 main sections:

  1. Top: Drop-down menus.
  2. Middle: Preview.
  3. Bottom: Load, Edit and Cancel buttons.

The main difference between this dialog box and the Navigator dialog box (in a previous section) is the fact that the Navigator dialog box allows you to choose from the available data sources within a workbook.

CSV and text files contain text data only. You don't have named ranges, Excel Tables, nor multiple worksheets to choose from. Therefore, when importing data from a CSV or text file, you don't select a data source within the file.

Results of importing data with Power Query Results when you import a single data source from a workbook, or a CSV or text file with Power Query

The results of importing data with Power Query are similar when you import data from either of the following:

  • A single data source from a workbook.
  • A CSV file.
  • A text file.

After you complete the appropriate process (described in previous sections), Excel does the following:

  1. Loads the imported data to an Excel Table in a new worksheet.
  2. Displays the Queries & Connections task pane. This task pane includes the query you just created.

Results when you import data from multiple data sources in a workbook with Power Query

After you complete the process I describe above to import data from multiple data sources in a workbook, Excel does the following:

  1. Loads the imported data to the Data Model.
  2. Displays the Queries & Connections task pane. This task pane has separate queries for each data source you selected.

Edit a query with Power Query

Power Query has several features that allow you to edit queries. This section introduces the topic. However, covering all options exceeds the scope of this Power Query Tutorial.

The processes I explain below build on the basic procedures to import data (from workbooks, CSV and text files) I explain in previous sections. Please refer to those sections as needed.

#1: Edit a query when importing data from a workbook with Power Query

To edit a query and import data from a workbook with Power Query, follow these 9..

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

In this VBA Tutorial, you learn how to create input boxes with both the InputBox function and the Application.InputBox method. This includes:

  1. How to create an InputBox.
  2. How to create an InputBox with multiple lines.
  3. How to create an InputBox that works with a specific type of data.
  4. How to handle the cases where the user clicks the Cancel button of the InputBox.

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:
    • Read a Macro Tutorial for beginners here.
    • Learn the definitions of several basic and important VBA terms here.
    • Learn how to specify macro security settings here.
    • Learn how to work with the Visual Basic Editor here.
    • Learn about the Excel VBA Object Model here.
    • Learn how to refer to cell ranges here.
    • Learn how to create Sub procedures here.
    • Learn how to work with object properties 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 functions here.
    • Learn how to work with the If… Then… Else statement here.
    • Learn how to work with loops here.
    • Learn how to work with arrays here.
  • Practical VBA applications and macro examples:
    • Learn how to work with worksheets here.
    • Learn how to convert strings to numbers here.
    • Learn how to create message boxes here.
    • Learn how to create UserForms here.

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

#1: Create InputBox with InputBox function VBA code to create InputBox with InputBox function

To create a basic InputBox with the VBA InputBox function, use a statement with the following structure:

InputBoxVariable = InputBox(Prompt:=PromptString, Title:=TitleString, Default:=DefaultInputString)
Process to create InputBox with InputBox function

To create a basic InputBox with the VBA InputBox function, follow these steps:

  1. Create an input box with the InputBox function (InputBox(…)).
  2. Assign the value returned by the InputBox function to a variable (InputBoxVariable = InputBox(…)).
VBA statement explanation Item: InputBoxVariable

InputBoxVariable is the variable you want to hold the value returned by the InputBox function.

The InputBox function returns a String.

Item: =

The = operator assigns a value to a variable or property.

Use the = operator to assign the value returned by the InputBox function (InputBox(…)) to InputBoxVariable.

Item: InputBox(…)

The InputBox function:

  1. Displays an input box;
  2. Waits for the user to either (i) input text and click the OK button (or press the Enter key), or (ii) click the Cancel button (or press the Esc key); and
  3. Returns a string with the contents of the text box in the input box (when the user clicks OK or presses Enter).

If you want to handle the cases where the user clicks on the Cancel button or presses Esc, please refer to the appropriate section of this Tutorial.

Item: Prompt:=PromptString

The Prompt argument of the InputBox function is a string displayed as the message in the input box. Prompt is a required argument.

You generally specify PromptString as a string expression.

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. To create an input box with multiple lines, please refer to the appropriate section of this Tutorial.

Item: Title:=TitleString

The Title argument of the InputBox function is a string expression displayed as the title in the title bar of the input box.

Title is an optional argument. If you omit the Title argument, the title of the input box is “Microsoft Excel”.

You generally specify TitleString as a string expression.

Item: Default:=DefaultInputString

The Default argument of the InputBox function is a string expression displayed inside the text box of the input box. DefaultInputString is, therefore, the default response.

Default is an optional argument. If you omit the Default argument, the text box is empty.

Macro example to create InputBox with InputBox function

The following macro example:

  1. Creates a basic input box with the InputBox function.
  2. Assigns the value returned by the InputBox function to a variable (myInputBoxVariable = inputBox(…)).
  3. Displays a message box with the value held by the variable.
Sub CreateInputBoxFunction()
    'source: https://powerspreadsheets.com/
    'creates an input box with the InputBox function
    'for further information: https://powerspreadsheets.com/excel-vba-inputbox/

    'declare variable to hold value returned by InputBox
    Dim myInputBoxVariable As String

    '(1) create InputBox, and (2) assign value returned by InputBox function to variable
    myInputBoxVariable = inputBox(Prompt:="Create Excel VBA InputBox", Title:="This is an Excel VBA InputBox", Default:="Enter VBA InputBox value here")

    'display message box with value held by variable
    MsgBox "Your input was: " & myInputBoxVariable

End Sub
Effects of executing macro example to create InputBox with InputBox function

The following GIF illustrates the results of executing the macro example. As expected, Excel displays a basic input box using the InputBox function.

#2: Create InputBox with Application.InputBox method VBA code to create InputBox with Application.InputBox method

To create a basic InputBox with the VBA Application.InputBox method, use a statement with the following structure:

InputBoxVariable = Application.InputBox(Prompt:=PromptString, Title:=TitleString, Default:=DefaultInput)
Process to create InputBox with Application.InputBox method

To create a basic InputBox with the VBA Application.InputBox method, follow these steps:

  1. Create an input box with the Application.InputBox method (Application.InputBox(…)).
  2. Assign the value returned by the Application.InputBox method to a variable (InputBoxVariable = Application.InputBox(…)).
VBA statement explanation Item: InputBoxVariable

InputBoxVariable is the variable you want to hold the value returned by the Application.InputBox method.

The Application.InputBox method returns a Variant.

Item: =

The = operator assigns a value to a variable or property.

Use the = operator to assign the value returned by the InputBox function (InputBox(…)) to InputBoxVariable.

Item: Application.InputBox(…)

The Application.InputBox method:

  1. Displays an input box'
  2. Waits for the user to either (i) input information and click the OK button (or press the Enter key), or (ii) click the Cancel button (or press the Esc key); and
  3. Returns the information entered in the dialog box (if the user clicks OK or presses Enter) or the False Boolean value (if the user clicks Cancel).
Item: Prompt:=PromptString

The Prompt parameter of the Application.InputBox method is a string displayed as the message in the input box. Prompt is a required parameter.

You generally specify PromptString as a string expression.

You can also specify PromptString as a number, a date or a Boolean. In such cases, Excel coerces the number, date or Boolean to a string.

PromptString can be composed of multiple lines. To create an input box with multiple lines, please refer to the appropriate section of this Tutorial.

Item: Title:=TitleString

The Title parameter of the Application.InputBox method is the title displayed in the title bar of the input box.

Title is an optional parameter. If you omit the Title parameter, the title of the input box is “Input”.

The Title parameter is of the Variant data type.

Item: Default:=DefaultInput

The Default parameter of the Application.InputBox method is the value displayed inside the text box of the input box. DefaultInput is, therefore, the default response.

Default is an optional parameter. If you omit the Default parameter, the text box is empty.

The Default parameter is of the Variant data type.

Macro example to create InputBox with Application.InputBox method

The following macro example:

  1. Creates a basic input box with the Application.InputBox method.
  2. Assigns the value returned by the Application.InputBox method to a variable (myInputBoxVariable = Application.inputBox(…)).
  3. Displays a message box with the value held by the variable.
Sub CreateInputBoxMethod()
    'source: https://powerspreadsheets.com/
    'creates an input box with the Application.InputBox method
    'for further information: https://powerspreadsheets.com/excel-vba-inputbox/

    'declare variable to hold value returned by InputBox
    Dim myInputBoxVariable As Variant

    '(1) create InputBox, and (2) assign value returned by Application.InputBox method to variable
    myInputBoxVariable = Application.inputBox(Prompt:="Create Excel VBA InputBox", Title:="This is an Excel VBA InputBox", Default:="Enter VBA InputBox value here")

    'display message box with value held by variable
    MsgBox "Your input was: " & myInputBoxVariable

End Sub
Effects of executing macro example to create InputBox with Application.InputBox method

The following GIF illustrates the results of executing the macro example. As expected, Excel displays a basic input box using the Application.InputBox method.

#3: Create InputBox with multiple lines using InputBox function VBA code to create InputBox with multiple lines using InputBox function

To create an InputBox containing multiple lines with the VBA InputBox function, use a statement with the following structure:

InputBoxMultipleLinesVariable = inputBox(Prompt:=PromptString1 & NewLineCharacter & PromptString2 & ... & NewLineCharacter & PromptString#)
Process to create InputBox with multiple lines using InputBox function

To create an InputBox containing multiple lines with the VBA InputBox function, follow these steps:

  1. Create an input box with the InputBox function (InputBox(…)).
  2. Specify the message displayed in the message box (Prompt:=PromptString1 & NewLineCharacter & PromptString2 & … & NewLineCharacter & PromptString#) as an appropriately concatenated (with the & character) combination of:
    • Strings (PromptString1, PromptString2, …, PromptString#); and
    • Characters that create a new line or line break (NewLineCharacter).
  3. Assign the value returned by the InputBox function to a variable (InputBoxMultipleLinesVariable = InputBox(…)).
VBA statement explanation Item: InputBoxMultipleLinesVariable

InputBoxMultipleLinesVariable is the variable you want to hold the value returned by the InputBox function.

The InputBox function returns a String.

Item: =

The = operator assigns a value to a variable or property.

Use the = operator to assign the value returned by the InputBox function (InputBox(…)) to InputBoxVariable.

Item: inputBox(…)

The InputBox function:

  1. Displays an input box;
  2. Waits for the user to either (i) input text and click the OK button (or press the Enter key), or (ii) click the Cancel button (or press the Esc key); and
  3. Returns a string with the contents of the text box in the input box (when the user clicks OK or presses Enter).

If you want to handle the cases where the user clicks on the Cancel button or presses Esc, please refer to the appropriate section of this Tutorial.

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

The Prompt argument of the InputBox function is a string displayed as the message in the input box. Prompt is a required argument.

You generally specify Prompt as a string expression.

The maximum length of Prompt is roughly 1024 characters. However, this maximum length may vary slightly depending on the width of the characters you work with.

To create an input box with multiple lines, you build the string expression assigned to Prompt by concatenating as many strings (PromptString1, PromptString2, …, PromptString#) and new line characters (NewLineCharacter) as required. For these purposes:

  • PromptString1, PromptString2, …, PromptString# are the strings (excluding the new line characters) that determine the message in the input box.
  • The & operator carries out string concatenation. Therefore, & concatenates the different strings and new line characters.
  • NewLineCharacter is a character or character combination returning 1 of the following:
    • Carriage return.
    • Linefeed.
    • Carriage return linefeed combination.
    • New line (which is platform specific).

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 InputBox with multiple lines using InputBox function

The following macro example:

  1. Creates an input box containing multiple lines (Create Excel VBA InputBox” & vbNewLine & “with multiple lines) with the InputBox function.
  2. Assigns the value returned by the InputBox function to a variable (myInputBoxMultipleLinesVariable = inputBox(…)).
  3. Displays a message box with the value held by the variable.
Sub CreateInputBoxFunctionMultipleLines()
    'source: https://powerspreadsheets.com/
    'creates an input box with multiple lines using the InputBox function
    'for further information: https://powerspreadsheets.com/excel-vba-inputbox/

    'declare variable to hold value returned by InputBox
    Dim myInputBoxMultipleLinesVariable As String

    '(1) create InputBox with multiple lines, and (2) assign value returned by InputBox function to variable
    myInputBoxMultipleLinesVariable = inputBox(Prompt:="Create Excel VBA InputBox" & vbNewLine & "with multiple lines")

    'display message box with value held by variable
    MsgBox "Your input was: " & myInputBoxMultipleLinesVariable

End Sub
Effects of executing macro example to create InputBox with multiple lines using InputBox function

The following GIF illustrates the results of executing the macro example. As expected, Excel displays an input box containing multiple lines using the InputBox function.

#4: Create InputBox with multiple lines using Application.InputBox method VBA code to create InputBox with multiple lines using Application.InputBox method

To create an InputBox containing multiple lines with the VBA Application.InputBox method, use a statement with the following structure:

InputBoxMultipleLinesVariable = Application.InputBox(Prompt:=PromptString1 & NewLineCharacter & PromptString2 & ... & NewLineCharacter & PromptString#)
Process to create InputBox with multiple lines using Application.InputBox method

To create an InputBox containing multiple lines with the VBA Application.InputBox method, follow these steps:

  1. Create an input box with the Application.InputBox method (Application.InputBox(…)).
  2. Specify the message displayed in the message box (Prompt:=PromptString1 & NewLineCharacter & PromptString2 & … & NewLineCharacter & PromptString#) as an appropriately concatenated (with the & character) combination of:
    • Strings (PromptString1, PromptString2, …, PromptString#); and
    • Characters that create a new line or line break (NewLineCharacter).
  3. Assign the value returned by the Application.InputBox method to a variable (InputBoxMultipleLinesVariable = Application.InputBox(…)).
VBA statement explanation Item: InputBoxMultipleLinesVariable

InputBoxMultipleLinesVariable is the variable you want to hold the value returned by the Application.InputBox method.

The Application.InputBox method returns a Variant.

Item: =

The = operator assigns a value to a variable or property.

Use the = operator to assign the value returned by the InputBox function (InputBox(…)) to InputBoxVariable.

Item: Application.InputBox(…)

The Application.InputBox method:

  1. Displays an input box'
  2. Waits for the user to either (i) input information and click the OK button (or press the Enter key), or (ii) click the Cancel button (or press the Esc key); and
  3. Returns the information entered in the dialog box (if the user clicks OK or presses Enter) or the False Boolean value (if the user clicks Cancel).
Item: Prompt:=PromptString1 & NewLineCharacter & PromptString2 & … & NewLineCharacter & PromptString#

The Prompt parameter of the Application.InputBox method is a string displayed as the message in the input box. Prompt is a required parameter.

You generally specify Prompt as a string expression. You can also specify Prompt as a number, a date or a Boolean. In such cases, Excel coerces the number, date or Boolean to a string.

To create an input box with multiple lines, you build the expression assigned to Prompt by concatenating as many strings (PromptString1, PromptString2, …, PromptString#) and new line characters (NewLineCharacter) as required. For these purposes:

  • PromptString1, PromptString2, …, PromptString# are the strings (excluding the new line characters) that determine the message in the input box.
  • The & operator carries out string concatenation. Therefore, & concatenates the different strings and new line characters.
  • NewLineCharacter is a character or character combination returning 1 of the following:
    • Carriage return.
    • Linefeed.
    • Carriage return linefeed combination.
    • New line (which is platform specific).

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 InputBox with multiple lines using Application.InputBox method

The following macro example:

  1. Creates an input box containing multiple lines (Create Excel VBA InputBox” & vbNewLine & “with multiple lines) with the Application.InputBox method.
  2. Assigns the value returned by the Application.InputBox method to a variable (myInputBoxMultipleLinesVariable = Application.inputBox(…)).
  3. Displays a message box with the value held by the variable.
Sub CreateInputBoxMethodMultipleLines()
    'source: https://powerspreadsheets.com/
    'creates an input box with multiple lines using the Application.InputBox method
    'for further information: https://powerspreadsheets.com/excel-vba-inputbox/

    'declare variable to hold value returned by InputBox
    Dim myInputBoxMultipleLinesVariable As Variant

    '(1) create InputBox with multiple lines, and (2) assign value returned by Application.InputBox method to variable
    myInputBoxMultipleLinesVariable = Application.inputBox(Prompt:="Create Excel VBA InputBox" & vbNewLine & "with multiple lines")

    'display message box with value held by variable
    MsgBox "Your input was: " & myInputBoxMultipleLinesVariable

End Sub
Effects of executing macro example to create InputBox with multiple lines using Application.InputBox method

The..

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

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

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 month
Free Preview