http://www.excelcharts.com/blog/how-to-create-an-excel-dashboard/
How to create a dashboard in Excelhttp://www.blogger.com/img/blank.gif
by Jorge Camoes on September 16, 2007
Excel dashboards
Excel dashboards and executive reports are powerful, fairly easy to design and a great way to improve your Excel and data visualization skills. Because of its flexibility, you can virtually design any dashboard in Excel exactly the way you, or the users, imagined. And, best of all, you may want to implement it yourself or consider it a prototype and ask IT to implement it.
Once you know what will the Excel dashboard be used for and what kind of metrics users expect, there are three major areas that you must address when creating it:
how to bring data into your spreadsheets;
how to manage the data and link it to the dashboard objects, like charts and tables;
how to design the dashboard report.
Let’s take a look at each of them.
How to bring data into your Excel dashboard
Yes, Excel is a very flexible tool, but to create an Excel dashboard you can’t just paste some data and add a few charts, can you? You must maintain and update it and, if you want to decrease the cost associated with those tasks, you must impose some structure to your data.
Usually, the data should not be entered directly into the spreadsheet. You may copy /paste the data, but the best option is to connect the spreadsheet to the data source. There is a standard way to communicate with external databases called ODBC. You can use it to connect your dashboard to a table in Access or Oracle, for example (there’s a free video in the members area, that shows how to connect Excel to an Access table; create a free account to watch it).
Once you have that connection established, every time the data changes at the source it also changes in the spreadsheet, after refreshing. Keep in mind that a good practice is to minimize the amount of data you bring into your spreadsheet. If you don’t need it, don’t use it. If there are calculations to be performed, try to do them at the source.
Data can be imported using two basic structures: a flat file and a pivot table. As a rule of thumb, I would say that a pivot table tends to create larger files but the calculations will be faster. The flat file will be smaller but, if you need complex formulas to select the data, performance will suffer (you must test this in your own project).
How to manage the data and link it to the dashboard objects
If you use pivot tables you can extract the data using the GETPIVOTDATA function. If you use a flat file, there is a vast array of formulas you can use, like database formulas (DSUM, DGET), lookup (VLOOKUP, MATCH, INDEX) or math formulas (SUM, SUMIF, SUMPRODUCT).
Having many formulas and calculations is dangerous to your dashboard integrity. You can decrease this by using pivot tables. That’s one of the reasons I choose them by default.
Named Ranges
Do I have to tell you that you should document your work? OK, I’ll tell you: yes, you should. But you can simplify that boring task by using named ranges. I am sure this is a familiar example: “=Sales-Expenses” is better than “=$G$4-$H$5”.
Named ranges not only help you making your formulas cleaner and easier to read, but they also play a key role in data management for Excel dashboards. Example: let’s say that the next update adds a new column to your table. If you don’t use named ranges you’ll have to change every single formula and add that extra column. With dynamic named ranges, the extra column is immediately available. This means less and more reliable work.
Dynamic Charts
I am a truly believer in dynamic charts. Dashboards that the user can’t interact with don’t make much sense to me. All my dashboards have controls to change markets, change products, change sales territories, whatever. What is important is that the user must be able to interact with the dashboard and build his/her knowledge by exploring the available data. Some managers prefer a static dashboard (perhaps just a sheet of paper, or a PDF file), but even then you should implement interaction, because it will be easier to create those static reports (you can automate them).
If you are creating interactive charts you will probably need dynamic ranges. You can do it using the OFFSET() function.
Macros
OK, you are not a programmer (I am not) but you should consider using some macros to speed up some repetitive tasks. Also, some cool things that you can add to your dashboards can only be done using macros, like greeting the users and selecting the right profile when they open the dashboard (you may want to read the discussion around the use of VBA in Excel dashboards).
How to design the dashboard report
If you like the kind of charts that you find in marketing-oriented tools like Crystal Xcelsius let me tell you this: they don’t work. There is a first wow! and then their uselessness starts creeping in.
This is not a matter of opinion only: I tried (and failed) to replicate my Demographic Dashboard (see below) using Xcelsius and detailed the process in a series of posts (1, 2, 3, 4, 5), so I know why these tools are useless for serious work. Don’t try to use Excel to mimic them.
You may still be using Excel 2003. Excel 2003 chart defaults are ugly and you should avoid them like the plague (and make sure you select the acceptable formats). Excel 2007 and 2010 are much better, but they still need some work. Throughout this blog you’ll find many charting tips, and tips to improve your Excel dashboard, so I will not elaborate much on this. The basic premise is this: a chart is used to discover actionable patterns in the data, and you should do your best to show those patterns.
This means that you should remove everything that has no added value (3D effects, backgrounds, etc), create a hierarchy of focus and context data (make relevant data stand out, using color carefully) and add at least a basic form of interaction (let users select the series, for example).
Resources
The reference in dashboard design if of course Stephen Few’s book, Information Dashboard Design. Other recommended reading are John Walkenbach’ Excel Bible (2003 or 2007) and Excel Charts (2003 or 2007); Tomothy Zapawa’ Excel Advanced Report Development (2003 or 2007); Michael Alexander’s Excel 2007 Dashboards and Reports for Dummies and Jon Peltier’s blog.
An Excel Dashboard Example: The Demographic Dashboard
This is an example of dashboard reporting using many advanced functions:
As I said above, it is difficult to put everything to work together in an Excel dashboard project. With the Demographic Dashboard, I wanted to show how to connect the dots and create a fully functional dashboard. So, I used a very interesting dataset from the US Census Bureau (population by sex, age and country, 1996-2050) to design it.
Actually, there are three dashboards… I took advantage of Excel’s built-in flexibility to create three versions of the same dashboard. They all look the same, but each one uses a different set of techniques:
The VBA version: This was the first one. It uses Visual Basic for Applications and pivot tables. It is faster than the other ones, but it relies on VBA to perform some tasks, and that may put off some users (I’m just using recorded macros, nothing too complex);
The VBA-Free version: After some discussion on the merits of using VBA (Excel Dashboards: Do You Need VBA?) I decided to add a VBA-free Excel dashboard; It mainly uses pivot tables and the powerful GETPIVOTDATA function. It is a well balanced version: it is slower than the previous version but since it doesn’t use VBA some users don’t have to leave their comfort zone;
The LookUp Table: It uses a spreadsheet database (basically a table that you paste into a sheet) and lookup functions (VLOOKUP, MATCH, OFFSET) to get the data for the dashboard report. Personally I don’t feel very comfortable with spreadsheet databases (more maintenance, more error prune, slower) but it is a good starting point to learn advanced Excel techniques (and sometimes is it your only option).
For a deeper discussion on the pros and cons of each version you may want to read the post Performance vs flexibility in Excel: Demographic Dashboard Lookup Edition.
Wrap-Up
Excel is a great tool for any type of business executive reporting. It allows for fast, flexible and cost-effective dashboard implementation (or prototyping). Intermediate users should be able to design and implement an executive dashboard in a matter of days (or even hours, for the first draft), provided that they know how to select the right techniques.
Friday, April 8, 2011
Thursday, April 7, 2011
http://searchsap.techtarget.com/resources/SAP-software-and-modules
SAP software and modules
Need to gain a deeper understanding of SAP modules? Can't quite figure out the interrelationship of SAP modules? Want to beef up on functional modules? Get the information you need in this extensive collection of SAP software resources.
*
SAP All-in-One
SAP All-in-One is SAP's business software for small and medium sized enterprises (SMEs). SAP Business All-in-One helps users manage financials, HR, procurement, inventory, manufacturing, logistics, product development, corporate services and customer service.
*
SAP Business ByDesign
SAP Business ByDesign offers midmarket companies with 50 to 500 employees a range of on-demand applications, including financials, procurement and CRM software, via a dashboard. Business ByDesign marks SAP's entry into the hosted-applications arena. Keep track of the latest developments,...
*
SAP Business One
SAP Business One is integrated enterprise resource planning (ERP) software for SMBs and mid-market companies. SAP Business One contains 14 core modules, including the Administration Module; Financials Module; Sales Opportunities Module; Sales Module, where orders are entered, shipped and...
*
SAP CRM software
SAP CRM helps marketers sharpen their customer focus and increase customer retention. SAP CRM capabilities include marketing resource and brand management; campaign management; segmentation and list management; real-time offer management; loyalty management; and e-marketing.
*
SAP ERP software
Learn the concepts of SAP enterprise resource planning and how to keep SAP ERP deployments on time and under budget. Learn about SAP ERP components, including the SAP FI (financial) and SAP HR (human resources) modules.
*
SAP HR management
Get help on SAP HR management, HR implementations, human resources reporting, personnel selection and administration, organization management, time management, payroll, benefits, HR configuration tables, online training, quality assurance in SAP HR, and how to transfer HR data to the...
*
SAP MM
The SAP MM (Materials Management) module interfaces with SAP's Production Planning and Plant Maintenance modules. The purpose of SAP MM is to ensure timely delivery of materials and to reduce inventory management costs. SAP MM monitors material requisitions (based on BoMs) and the...
*
SAP PLM software
SAP PLM (SAP Product Lifecycle Management) software supports all product-related processes – from the first product idea, through manufacturing to product service. SAP PLM helps users optimize the product development process and get products to market quicker.
*
SAP PM
SAP PM, or Plant Maintenance, deals with the preventive maintenance of manufacturing equipment that accompanies a streamlined production process. SAP PM also monitors downtime of equipment, workforce production hour as well as the time, materials and labor necessary for such maintenance.
*
SAP SCM software
The SAP SCM software (Supply Chain Management) module helps users with SCM planning of supply chain processes to enable collaboration and coordination of the supplier network. SAP SCM can help transform a linear supply chain into a responsive supply network, helping companies respond to...
*
SAP SD
The SAP SD (Sales and Distribution) module tracks proceedings from customer inquiries, requests for proposals (RFPs) and requests for quotations (RFQs) and pricing through working with the finished-goods stock (in factories and warehouses) to picking, packing, shipping and delivery.
*
SAP financial management
SAP financial management begins with SAP's FI (Financial Accounting) module, which integrates with other SAP software modules such as MM (Materials Management), PP (Production Planning), SD (Sales and Distribution), PM (Plant Maintenance) PS (Project Systems) as well as HR.
*
SAP industry-specific software
SAP's industry-specific software includes the SAP for retail module; SAP manufacturing module; and SAP public sector module.
o SAP for retail module
o , SAP manufacturing module
o , SAP public sector module
*
SAP mobile technology
Mobilizing SAP applications has become a key strategy for the vendor. Learn more about the SAP product roadmap for SAP mobile applications. Read articles and research on how SAP mobile applications have evolved and get analyst projections on mobile technology development. Discover how...
*
SAP supplier relationship management software
Many businesses want to strengthen their supplier relationships and make procurement a more strategic process. The SAP Supplier Relationship Management (SAP SRM) application automates, simplifies and accelerates procure-to-pay processes for goods and services. With SAP SRM, you can reduce...
*
SAP xApps
SAP xApps, or Composite Applications, are based on the SAP ESOA, meaning they use SAP enterprise SOA services and run on an SAP NetWeaver application server. SAP xApps include small-footprint software products from SAP, SAP partners and customers. Most xApps are targeted at specific...
SAP software and modules
Need to gain a deeper understanding of SAP modules? Can't quite figure out the interrelationship of SAP modules? Want to beef up on functional modules? Get the information you need in this extensive collection of SAP software resources.
*
SAP All-in-One
SAP All-in-One is SAP's business software for small and medium sized enterprises (SMEs). SAP Business All-in-One helps users manage financials, HR, procurement, inventory, manufacturing, logistics, product development, corporate services and customer service.
*
SAP Business ByDesign
SAP Business ByDesign offers midmarket companies with 50 to 500 employees a range of on-demand applications, including financials, procurement and CRM software, via a dashboard. Business ByDesign marks SAP's entry into the hosted-applications arena. Keep track of the latest developments,...
*
SAP Business One
SAP Business One is integrated enterprise resource planning (ERP) software for SMBs and mid-market companies. SAP Business One contains 14 core modules, including the Administration Module; Financials Module; Sales Opportunities Module; Sales Module, where orders are entered, shipped and...
*
SAP CRM software
SAP CRM helps marketers sharpen their customer focus and increase customer retention. SAP CRM capabilities include marketing resource and brand management; campaign management; segmentation and list management; real-time offer management; loyalty management; and e-marketing.
*
SAP ERP software
Learn the concepts of SAP enterprise resource planning and how to keep SAP ERP deployments on time and under budget. Learn about SAP ERP components, including the SAP FI (financial) and SAP HR (human resources) modules.
*
SAP HR management
Get help on SAP HR management, HR implementations, human resources reporting, personnel selection and administration, organization management, time management, payroll, benefits, HR configuration tables, online training, quality assurance in SAP HR, and how to transfer HR data to the...
*
SAP MM
The SAP MM (Materials Management) module interfaces with SAP's Production Planning and Plant Maintenance modules. The purpose of SAP MM is to ensure timely delivery of materials and to reduce inventory management costs. SAP MM monitors material requisitions (based on BoMs) and the...
*
SAP PLM software
SAP PLM (SAP Product Lifecycle Management) software supports all product-related processes – from the first product idea, through manufacturing to product service. SAP PLM helps users optimize the product development process and get products to market quicker.
*
SAP PM
SAP PM, or Plant Maintenance, deals with the preventive maintenance of manufacturing equipment that accompanies a streamlined production process. SAP PM also monitors downtime of equipment, workforce production hour as well as the time, materials and labor necessary for such maintenance.
*
SAP SCM software
The SAP SCM software (Supply Chain Management) module helps users with SCM planning of supply chain processes to enable collaboration and coordination of the supplier network. SAP SCM can help transform a linear supply chain into a responsive supply network, helping companies respond to...
*
SAP SD
The SAP SD (Sales and Distribution) module tracks proceedings from customer inquiries, requests for proposals (RFPs) and requests for quotations (RFQs) and pricing through working with the finished-goods stock (in factories and warehouses) to picking, packing, shipping and delivery.
*
SAP financial management
SAP financial management begins with SAP's FI (Financial Accounting) module, which integrates with other SAP software modules such as MM (Materials Management), PP (Production Planning), SD (Sales and Distribution), PM (Plant Maintenance) PS (Project Systems) as well as HR.
*
SAP industry-specific software
SAP's industry-specific software includes the SAP for retail module; SAP manufacturing module; and SAP public sector module.
o SAP for retail module
o , SAP manufacturing module
o , SAP public sector module
*
SAP mobile technology
Mobilizing SAP applications has become a key strategy for the vendor. Learn more about the SAP product roadmap for SAP mobile applications. Read articles and research on how SAP mobile applications have evolved and get analyst projections on mobile technology development. Discover how...
*
SAP supplier relationship management software
Many businesses want to strengthen their supplier relationships and make procurement a more strategic process. The SAP Supplier Relationship Management (SAP SRM) application automates, simplifies and accelerates procure-to-pay processes for goods and services. With SAP SRM, you can reduce...
*
SAP xApps
SAP xApps, or Composite Applications, are based on the SAP ESOA, meaning they use SAP enterprise SOA services and run on an SAP NetWeaver application server. SAP xApps include small-footprint software products from SAP, SAP partners and customers. Most xApps are targeted at specific...
Friday, February 25, 2011
Summarize Spreadsheet Data With Excel's Array Formulas
http://www.exceluser.com/explore/arrays1.htm
Array formulas may be Excel's most powerful feature for summarizing data. However, they also are one of Excel's
least-used features. Here's how to use this hidden power.
Subtotals can reveal very useful management information.
For example, managers might be interested to learn that sales increased by 10% last month. But they would be fascinated to learn that Pat Smith's sales of Widgets in the Northwest region doubled last month, while the sales of all other people fell sharply.
Array formulas provide a way by which Excel users can discover such useful information.
Introducing the Data
To explain the power of array formulas I'll use this database. It shows sales by Seller, Product, Region, and Customer, with Quantity and Total Sales, in dollars.
I named each column of data with the label shown in row 1. To do so, I selected the range A1:F15; chose Insert, Name, Create; chose Top Row; and then chose OK.
By assigning names in this way we anchor the names in the gray border rows, rows 2 and 15. By doing so, we can add new data to this table between the gray rows and be confident that the names will expand as needed.
Introducing Array Formulas, Example 1
Let's begin our examination of this data by summarizing the sales for Jill and Joe. Cells J3 and J4 perform this summary by using array formulas.
Here's the formula for the cell shown:
J3: {=SUM(IF(Seller=$I3,Total,0))}
Notice the braces that surround this formula. You do NOT enter those characters when you key in the formula. Instead, you type the formula shown within the braces. But when you're done typing you don't press Enter. Instead, you hold down the Ctrl and Shift keys, then press Enter. After you do so, Excel displays the formula in the formula bar with the braces, as shown above.
Here's the key to understanding how array formulas work: Each array formula creates temporary arrays in memory, and then the outside function returns the results from that array.
To illustrate, the "Seller=$I3" part of the formula creates a temporary array like this: {FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;TRUE; FALSE;TRUE;FALSE;TRUE;FALSE;FALSE} Here, the second element in the array is TRUE. That is, the second cell of the Seller range, cell A3 contains the text "Joe".
The formula says that wherever TRUE appears, return the corresponding value from the Total column. Therefore, the statement
"IF(Seller=$I3,Total,0)" returns the temporary array: {0;12600;0;0;0; 5060;0;1980;0;1540;0;4500;0;0}
Finally, the SUM function returns the sum of this temporary array: 25,680.
The second formula is similar:
J3: {=SUM(IF(Seller=$I4,Total,0))}
You can copy this formula from cell I3, or enter it for the practice. Again, if you enter it, do NOT manually enter the braces. Instead, when you enter the formula using Ctrl-Shift Enter, the braces will appear automatically.
More Examples of Array Formulas
The best way to gain an understanding of array formulas is to see several examples. All of these refer to the database at the top of this page.
Example 2
This table summarizes sales by sales person by product. The grand total, as you can see, matches the grand total of Example 1.
This array formula is slightly longer:
J9: {=SUM(IF((Seller=$I9)*(Prod=J$8),Total,0))}
Here, we multiply the first array ("Seller=$19") by a second array ("Prod=J8"). This returns an array that contains TRUE only when both corresponding cells in the array are TRUE.
That is, when you work with array formulas, you multiply when you want an AND relationship.
Also, notice that I enclosed each test in parentheses. Not only does Excel require these parentheses, they make the formula easier to understand when you read it.
When you copy the array formula to the remainder of this display, you must copy in two steps, not one. With normal formulas you could copy cell J9 to the range J9:L10. But when you try to follow the same approach with an array formula, Excel complains. That is, Excel objects when you try to copy an array formula to a multi-cell range that includes itself.
Therefore, you first copy cell J9 to cell J10, then copy the range J9:J10 to the range K9:L9.
The formulas in row 11 and in column L are merely SUM formulas for the columns and rows respectively, as shown here:
J11: =SUM(J9:J10)
L9: =SUM(J9:K9)
For convenience, in the following examples I'll call the range of calculations above and to the left of the SUMs the Working Range.
Example 3
We can dig deeper into the data by adding an additional argument. Here, we enter the label "Hats" in cell K13 to report only on sales of hats.
The array formulas for this example are slightly longer, but follow the same pattern as before:
J15: {=SUM(IF((Seller=$I15)*(Region=J$14)*(Prod=$K$13),Total,0))}
Copy this formula to the Working Range, J15:L16.
Example 4
Array formulas work with more functions than merely the SUM function. And you can perform additional calculations within the formula.
For example, this formula returns the largest price for Hats that Joe has sold:
J28: {=MAX(IF((Seller=$I28)*(Prod=J$27),Total/Qty,0))}
Copy this formula to the Working Range.
Example 5
In several of the following examples we're going to need the units sold by sales person by product. So let's calculate those numbers now.
The formula is:
P3: {=SUM(IF((Seller=$O3)*(Prod=P$2),Qty,0))}
As before, copy this formula to the Working Range.
Example 6
We can calculate the average price that Joe has charged for Hats.
Here's one way to do this:
P9: {=AVERAGE(IF((Seller=$O9)*(Prod=P$8),Total/Qty,""))}
Notice the null string in the last argument of this formula. If we had used a zero, the AVERAGE function would have included all those zeros in the calculation of its average, which we don't want. Instead, by using a null string, we cause the AVERAGE function to ignore the cells that don't satisfy our criteria. Alternatively, we could have used FALSE, like this:
P9: {=AVERAGE(IF((Seller=$O9)*(Prod=P$8),Total/Qty,FALSE))}
No matter which way we calculate this formula, it has a problem: Its average is not weighted by the number of units sold. Therefore, if you multiply each average price by each number of units sold in Example 5, you won't get a correct grand total.
Example 7
This version calculates a weighted average price by sales person by product. When we multiply each value by the equivalent number of units sold from Example 5, and total the results, we'll get the grand totals shown in Examples 1 and 2.
The key formula is:
P15: {=SUM(IF((Seller=$O15)*(Prod=P$14),Total,""))/
SUM(IF((Seller=$O15)*(Prod=P$14),Qty,""))}
(Because it's too long to fit in one row, I'm showing this formula in two rows. You would enter it in one long line, of course.)
In this specific formula, we calculate Joe's total sales for hats and then divide by the total number of hats that Joe sold. This gives us the weighted-average price for the hats that Joe sold.
As always, copy this formula to the Working Range.
Example 8
This display checks our results. The first formula is simply:
P21: =P3*P15
This formula multiplies the number of hats that Joe sold (Example 5) by the weighted average sales price (Example 7) to calculate Joe's total sales of hats, in dollars. As always, copy this formula to the Working Range. The bottom-right cell in this display shows that the total of all sales matches the grant total of the data at the beginning of this article.
Example 9
In Example 2, I said that multiplying two tests creates an AND relationship. That is, the result is TRUE only if all values that you multiply also are TRUE.
Similarly, summing two results creates an OR relationship.
To illustrate, suppose the sales manager is thinking about merging the North and the West regions. So he wants a report that treats those regions as one region.
Here's the first formula:
P29: {=SUM(IF((Seller=$O29)*
((Region=P$27)+(Region=P$28)),Total,0))}
(Because it's too long to fit in one row, I'm showing this formula in two rows. You would enter it in one long line, of course.)
Here, if the Seller is Joe and if the Region is either North or West, the array returns the Total; otherwise, it returns 0. As usual, the SUM function returns the total of the array.
When you copy the formula to cell Q29, the formula returns the total of Joe's sales for Regions that contain "South" or that are blank.
Extending Array Formulas
Array formulas can be extended in some surprising ways. Specifically, the Excel functions CHOOSE and OFFSET offer some options to keep in mind.
To illustrate, consider this formula:
D20: {=SUM(IF(Seller=$A$20,Total,0))}
Suppose you want to expand this formula. Suppose that sometimes you want to return the Total when Seller equals the label in cell A20, and at other times you want to return the Total when Region equals the label in cell B20. You want to specify the test to use by entering the value 1 (for Seller) or 2 (for Region) in cell C20.
To set up this summary, you could use the CHOOSE function in two different ways:
D20: {=SUM(IF(CHOOSE($C20,Seller=$A$20,Region=$B20),Total,0))}
D20: {=CHOOSE($C20,SUM(IF(Seller=$A$20,Total,0)),
SUM(IF(Region=$B$20,Total,0)))}
(Because it's too long to fit in one row, I'm showing the second formula in two rows. You would enter it in one long line, of course.)
Either option works the same in this specific example. But in real life, you could use these options in different ways. For example, the second approach would allow you to choose completely different formulas merely by changing the number in cell C20.
Similarly, the OFFSET function allows you to choose different rows or columns to test or sum. For example:
E20: {=SUM(IF(OFFSET(Seller,0,$A22)=$B22,Total,0))}
Here if the value in cell A22 is 1, the OFFSET function causes the formula to compare the label in cell B22 to the first column to the right of Seller, or the Prod column in the example. If the value in cell A22 is 2, the OFFSET function compares the label to the second column, which is the Region column in the example.
In Conclusion
Array formulas are the ideal tool to use whenever you need to summarize a spreadsheet database. Once you start to experiment with them, you'll see what I mean.
Array formulas may be Excel's most powerful feature for summarizing data. However, they also are one of Excel's
least-used features. Here's how to use this hidden power.
Subtotals can reveal very useful management information.
For example, managers might be interested to learn that sales increased by 10% last month. But they would be fascinated to learn that Pat Smith's sales of Widgets in the Northwest region doubled last month, while the sales of all other people fell sharply.
Array formulas provide a way by which Excel users can discover such useful information.
Introducing the Data
To explain the power of array formulas I'll use this database. It shows sales by Seller, Product, Region, and Customer, with Quantity and Total Sales, in dollars.
I named each column of data with the label shown in row 1. To do so, I selected the range A1:F15; chose Insert, Name, Create; chose Top Row; and then chose OK.
By assigning names in this way we anchor the names in the gray border rows, rows 2 and 15. By doing so, we can add new data to this table between the gray rows and be confident that the names will expand as needed.
Introducing Array Formulas, Example 1
Let's begin our examination of this data by summarizing the sales for Jill and Joe. Cells J3 and J4 perform this summary by using array formulas.
Here's the formula for the cell shown:
J3: {=SUM(IF(Seller=$I3,Total,0))}
Notice the braces that surround this formula. You do NOT enter those characters when you key in the formula. Instead, you type the formula shown within the braces. But when you're done typing you don't press Enter. Instead, you hold down the Ctrl and Shift keys, then press Enter. After you do so, Excel displays the formula in the formula bar with the braces, as shown above.
Here's the key to understanding how array formulas work: Each array formula creates temporary arrays in memory, and then the outside function returns the results from that array.
To illustrate, the "Seller=$I3" part of the formula creates a temporary array like this: {FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;TRUE; FALSE;TRUE;FALSE;TRUE;FALSE;FALSE} Here, the second element in the array is TRUE. That is, the second cell of the Seller range, cell A3 contains the text "Joe".
The formula says that wherever TRUE appears, return the corresponding value from the Total column. Therefore, the statement
"IF(Seller=$I3,Total,0)" returns the temporary array: {0;12600;0;0;0; 5060;0;1980;0;1540;0;4500;0;0}
Finally, the SUM function returns the sum of this temporary array: 25,680.
The second formula is similar:
J3: {=SUM(IF(Seller=$I4,Total,0))}
You can copy this formula from cell I3, or enter it for the practice. Again, if you enter it, do NOT manually enter the braces. Instead, when you enter the formula using Ctrl-Shift Enter, the braces will appear automatically.
More Examples of Array Formulas
The best way to gain an understanding of array formulas is to see several examples. All of these refer to the database at the top of this page.
Example 2
This table summarizes sales by sales person by product. The grand total, as you can see, matches the grand total of Example 1.
This array formula is slightly longer:
J9: {=SUM(IF((Seller=$I9)*(Prod=J$8),Total,0))}
Here, we multiply the first array ("Seller=$19") by a second array ("Prod=J8"). This returns an array that contains TRUE only when both corresponding cells in the array are TRUE.
That is, when you work with array formulas, you multiply when you want an AND relationship.
Also, notice that I enclosed each test in parentheses. Not only does Excel require these parentheses, they make the formula easier to understand when you read it.
When you copy the array formula to the remainder of this display, you must copy in two steps, not one. With normal formulas you could copy cell J9 to the range J9:L10. But when you try to follow the same approach with an array formula, Excel complains. That is, Excel objects when you try to copy an array formula to a multi-cell range that includes itself.
Therefore, you first copy cell J9 to cell J10, then copy the range J9:J10 to the range K9:L9.
The formulas in row 11 and in column L are merely SUM formulas for the columns and rows respectively, as shown here:
J11: =SUM(J9:J10)
L9: =SUM(J9:K9)
For convenience, in the following examples I'll call the range of calculations above and to the left of the SUMs the Working Range.
Example 3
We can dig deeper into the data by adding an additional argument. Here, we enter the label "Hats" in cell K13 to report only on sales of hats.
The array formulas for this example are slightly longer, but follow the same pattern as before:
J15: {=SUM(IF((Seller=$I15)*(Region=J$14)*(Prod=$K$13),Total,0))}
Copy this formula to the Working Range, J15:L16.
Example 4
Array formulas work with more functions than merely the SUM function. And you can perform additional calculations within the formula.
For example, this formula returns the largest price for Hats that Joe has sold:
J28: {=MAX(IF((Seller=$I28)*(Prod=J$27),Total/Qty,0))}
Copy this formula to the Working Range.
Example 5
In several of the following examples we're going to need the units sold by sales person by product. So let's calculate those numbers now.
The formula is:
P3: {=SUM(IF((Seller=$O3)*(Prod=P$2),Qty,0))}
As before, copy this formula to the Working Range.
Example 6
We can calculate the average price that Joe has charged for Hats.
Here's one way to do this:
P9: {=AVERAGE(IF((Seller=$O9)*(Prod=P$8),Total/Qty,""))}
Notice the null string in the last argument of this formula. If we had used a zero, the AVERAGE function would have included all those zeros in the calculation of its average, which we don't want. Instead, by using a null string, we cause the AVERAGE function to ignore the cells that don't satisfy our criteria. Alternatively, we could have used FALSE, like this:
P9: {=AVERAGE(IF((Seller=$O9)*(Prod=P$8),Total/Qty,FALSE))}
No matter which way we calculate this formula, it has a problem: Its average is not weighted by the number of units sold. Therefore, if you multiply each average price by each number of units sold in Example 5, you won't get a correct grand total.
Example 7
This version calculates a weighted average price by sales person by product. When we multiply each value by the equivalent number of units sold from Example 5, and total the results, we'll get the grand totals shown in Examples 1 and 2.
The key formula is:
P15: {=SUM(IF((Seller=$O15)*(Prod=P$14),Total,""))/
SUM(IF((Seller=$O15)*(Prod=P$14),Qty,""))}
(Because it's too long to fit in one row, I'm showing this formula in two rows. You would enter it in one long line, of course.)
In this specific formula, we calculate Joe's total sales for hats and then divide by the total number of hats that Joe sold. This gives us the weighted-average price for the hats that Joe sold.
As always, copy this formula to the Working Range.
Example 8
This display checks our results. The first formula is simply:
P21: =P3*P15
This formula multiplies the number of hats that Joe sold (Example 5) by the weighted average sales price (Example 7) to calculate Joe's total sales of hats, in dollars. As always, copy this formula to the Working Range. The bottom-right cell in this display shows that the total of all sales matches the grant total of the data at the beginning of this article.
Example 9
In Example 2, I said that multiplying two tests creates an AND relationship. That is, the result is TRUE only if all values that you multiply also are TRUE.
Similarly, summing two results creates an OR relationship.
To illustrate, suppose the sales manager is thinking about merging the North and the West regions. So he wants a report that treats those regions as one region.
Here's the first formula:
P29: {=SUM(IF((Seller=$O29)*
((Region=P$27)+(Region=P$28)),Total,0))}
(Because it's too long to fit in one row, I'm showing this formula in two rows. You would enter it in one long line, of course.)
Here, if the Seller is Joe and if the Region is either North or West, the array returns the Total; otherwise, it returns 0. As usual, the SUM function returns the total of the array.
When you copy the formula to cell Q29, the formula returns the total of Joe's sales for Regions that contain "South" or that are blank.
Extending Array Formulas
Array formulas can be extended in some surprising ways. Specifically, the Excel functions CHOOSE and OFFSET offer some options to keep in mind.
To illustrate, consider this formula:
D20: {=SUM(IF(Seller=$A$20,Total,0))}
Suppose you want to expand this formula. Suppose that sometimes you want to return the Total when Seller equals the label in cell A20, and at other times you want to return the Total when Region equals the label in cell B20. You want to specify the test to use by entering the value 1 (for Seller) or 2 (for Region) in cell C20.
To set up this summary, you could use the CHOOSE function in two different ways:
D20: {=SUM(IF(CHOOSE($C20,Seller=$A$20,Region=$B20),Total,0))}
D20: {=CHOOSE($C20,SUM(IF(Seller=$A$20,Total,0)),
SUM(IF(Region=$B$20,Total,0)))}
(Because it's too long to fit in one row, I'm showing the second formula in two rows. You would enter it in one long line, of course.)
Either option works the same in this specific example. But in real life, you could use these options in different ways. For example, the second approach would allow you to choose completely different formulas merely by changing the number in cell C20.
Similarly, the OFFSET function allows you to choose different rows or columns to test or sum. For example:
E20: {=SUM(IF(OFFSET(Seller,0,$A22)=$B22,Total,0))}
Here if the value in cell A22 is 1, the OFFSET function causes the formula to compare the label in cell B22 to the first column to the right of Seller, or the Prod column in the example. If the value in cell A22 is 2, the OFFSET function compares the label to the second column, which is the Region column in the example.
In Conclusion
Array formulas are the ideal tool to use whenever you need to summarize a spreadsheet database. Once you start to experiment with them, you'll see what I mean.
Thursday, February 24, 2011
Wednesday, February 23, 2011
http://pubs.logicalexpressions.com/pub0009/lpmarticle.asp?id=349
Example 1a.
Sub CreateWorkbook()
Workbooks.Add
End Sub
Next we need to determine how many worksheets should be in the new workbook. We can do this by counting how many names are listed in the original workbook (the workbook with the VBA code). Keep in mind that we now have two workbooks open, the original and the new one created by our subroutine. When we added the new workbook, it became the "active" workbook, but since we want to count the names which are stored in the original workbook, we need to reference it as "ThisWorkbook". This signifies that the code is referencing the workbook in which it (the code) is contained.
We are assuming that the names are contained on the first worksheet, so we will use ThisWorkbook.Worksheets(1). You may remember from Lesson 1 that we can find out how many rows are actually used in a worksheet by looking at the "UsedRange" property of the Worksheet object.
Example 1b.
Sub CreateWorkbook()
Dim iSheetCount As Integer
Workbooks.Add
iSheetCount = ThisWorkbook.Worksheets(1).UsedRange.Rows.Count
End Sub
The next step is to set the correct number of worksheets in the new workbook. To do this we will use two separate "while" loops. One will be used if we need to remove worksheets, the second if we need to add worksheets. As I mentioned above, when a new workbook is added, it becomes the active workbook, so we can reference the new workbook using "ActiveWorkbook".
Example 1c.
Sub CreateWorkbook()
Dim iSheetCount As Integer
Workbooks.Add
iSheetCount = ThisWorkbook.Worksheets(1).UsedRange.Rows.Count
Application.DisplayAlerts = False
' If we have too many worksheets, delete one at a time until
' we reach the correct number:
While ActiveWorkbook.Worksheets.Count > iSheetCount
ActiveWorkbook.Worksheets(1).Delete
Wend
' If we don’t have enough worksheets, add more one at a time
' until we reach the correct number:
While ActiveWorkbook.Worksheets.Count < iSheetCount
ActiveWorkbook.Worksheets.Add
Wend
End Sub
The line "Application.DisplayAlerts = False" is handy when you want your VBA code to do something that Excel normally gives you an "Are you sure?" message about when you try to do it by hand. Without this line of code, Excel will display a warning message each time the code tries to delete a worksheet. Setting the DisplayAlerts property of the Application object to False turns off such messages.
Lastly, we need to set the names of the worksheets in the new workbook according to the contents of the original workbook. For this we will use a simple "for" loop, reading each name from ThisWorkbook, and assigning it to the Name property of each Worksheet object in the ActiveWorkbook.
Example 1d.
Sub CreateWorkbook()
Dim iSheetCount As Integer
Dim iSheet As Integer
Workbooks.Add
iSheetCount = ThisWorkbook.Worksheets(1).UsedRange.Rows.Count
Application.DisplayAlerts = False
' If we have too many worksheets, delete one at a time until
' we reach the correct number:
While ActiveWorkbook.Worksheets.Count > iSheetCount
ActiveWorkbook.Worksheets(1).Delete
Wend
' If we don’t have enough worksheets, add more one at a time
' until we reach the correct number:
While ActiveWorkbook.Worksheets.Count < iSheetCount
ActiveWorkbook.Worksheets.Add
Wend
For iSheet = 1 To iSheetCount
ActiveWorkbook.Worksheets(iSheet).Name = _
ThisWorkbook.Worksheets(1).Cells(iSheet, 1).Value
Next iSheet
End Sub
Note that we use the iSheet variable as both a reference into the Worksheets collection of the new workbook, and as the row number when reading the names from the original workbook.
You may also notice that an underscore character can be used to split a long line of code over two lines. The underscore must be preceded by a space.
Debugging
When something goes horribly awry in your code, or your code doesn’t produce the results you expect, you can use the Debug and Run menus in the Visual Basic Editor to help locate the source of the problems. Below are some of the most useful commands:
Command
Shortcut Key
Toolbar Button
Description
Run
F5
Executes the current subroutine or function
Toggle Breakpoint
F9
Sets or Clears a breakpoint, which pauses execution of the code
Step Into
F8
Steps through each line of code (when one subroutine is called from another this will "step into" the called subroutine)
Step Over
Shift + F8
Steps through each line of code (when one subroutine is called from another this will "step over" the called subroutine)
Reset
Stops execution of your code. You can restart execution only from the beginning.
Break
Ctrl+Break
Temporarily halts execution of your code. You can continue execution from the where it left off.
Quick Watch
Shift + F9
When execution is paused, displays the value of the highlighted variable. (You may also view the value of a variable by pausing the cursor over it)
Before you run your code, save your workbook. It doesn’t happen often, but you may inadvertently have written code which is truly evil and will cause Excel to crash. You don’t want to lose your code, diabolical though it may be. Also, pay particular attention to the keyboard shortcut Ctrl+Break. If your code has gone into an infinite loop, or something is taking much longer than you expected, Ctrl+Break is the easiest way to stop it. Once execution has been halted, you can either Reset it, or step through from where it was paused, using F8.
Try using the debugging commands to step through the homework example solution. As you step through line by line, you can actually Alt+Tab (or Alt+F11) between the VBA Editor and Excel to see each worksheet added and each worksheet name as it changes.
Functions
All of the examples that we’ve used so far have been in the form of subroutines, aka macros. In Lesson 1, I mentioned that your VBA code would be in the form of subroutines and functions, but I have yet to define what a function is, or what the difference is between a function or a subroutine.
Well, the main difference is that a function returns a value. In other words, you can call a function from a subroutine (or another function), and use the value that the function passes back. This may be more easily understood with a simple example.
Example 2.
Function UpperFileName() As String
UpperFileName = UCase(ActiveWorkbook.Name)
End Function
Sub ShowFileName()
MsgBox UpperFileName()
End Sub
Here we’ve defined a simple function called UpperFileName. We’ve declared the function type (string) in a way similar to how we declare a variable, using "As" followed by a data type. This represents which data type will be returned by the function. In this example, the function returns a string, which it creates by getting the file name of the current active workbook, and converting it to upper case (using the VB function UCase). We return this string value by using the function name as if it were a variable, and setting it equal to the string. The subroutine ShowFileName calls the function UpperFileName and displays the returned value in a message box.
(This may be apparent to you if you’ve written code before, but if you’re one of the brave souls who’s never programmed before and made it this far, you should note that you can call one subroutine or function from another subroutine or function.)
Function and Subroutine Parameters
To really make a worthwhile function, you will need the ability to send values into the function. These values are known as either parameters or arguments. For example, suppose you wanted to write a function which converted a temperature from degrees fahrenheit to degrees celsius. This function would need to take as input a temperature in fahrenheit. You can do this by listing the parameter with its type within the parentheses that follow the function name.
Example 3.
Function Celsius(dFahrenheit As Double) As Double
Celsius = (dFahrenheit - 32) * (5 / 9)
End Function
Sub ConvertTemperature()
Dim dtemperatureF As Double
Dim dtemperatureC As Double
dtemperatureF = Cells(1, 4).Value
dtemperatureC = Celsius(dtemperatureF)
Cells(2, 4).Value = dtemperatureC
End Sub
The function, named Celsius, is declared as a double, meaning that it returns a double-precision floating point value. It takes, as a parameter, a double value called dFahrenheit. It convert dFahrenheit to the corresponding celsius temperature, then returns this value.
The subroutine ConvertTemperature reads a fahrenheit value from cell D1, converts it to celsius using our function, and places the result in cell D2.
Functions can take more than one parameter. Each parameter should be separated by a comma. The function below, for example, can convert a number to either celsius or fahrenheit, depending on the value of the second parameter, bConvertToCelsius. This is a Boolean variable, which means that it can have a value or either True or False.
Example 4.
Function CelsiusOrFahrenheit(dTemperature As Double, _
bConvertToCelsius As Boolean) As Double
If bConvertToCelsius Then 'dTemperature is in Fahrenheit,
'so convert to Celsius
CelsiusOrFahrenheit = (dTemperature - 32) * (5 / 9)
Else 'dTemperature is in Celsius, convert to Fahrenheit
CelsiusOrFahrenheit = dTemperature * (9 / 5) + 32
End If
End Function
Subroutines can also take parameters. Note, however, that a subroutine which requires parameters cannot be called directly from Excel’s Tools, Macros menu. It must be called from another subroutine (or function).
Using Your Functions in the Spreadsheet
The coolest thing about writing your own functions is that you can actually use them directly in your spreadsheet. All of the examples above are contained in the Excel VBA Article 2 Example Solution.xls (link above). If you haven’t already been adding the examples to your own workbook, download the Article 2 Example Solution workbook.
In order to use your function in the spreadsheet, you only need to make one minor change. In the VBA Editor, add the word "Public" before the word "Function". This simply makes the function accessible from outside the VBA module in which it resides.
Next go to the spreadsheet, enter a number (temperature) in one cell, then click in an empty cell. Select Insert, Function from the main menu. The last item in the Function Category list on the left is called User Defined. Click on this and you will see your user-defined functions listed magically on the right. Double-click on Celsius. You will then be presented with a box in which you are prompted for the parameter which the function requires, dFahrenheit. Click on the cell in which you typed a temperature, then click OK. Your temperature has been converted from Fahrenheit to Celsius!
I don’t know about you, but I was pretty excited when I learned I could write my own functions, then use them in my spreadsheet. Then again, maybe you’re not as big a geek as I.
Example 1a.
Sub CreateWorkbook()
Workbooks.Add
End Sub
Next we need to determine how many worksheets should be in the new workbook. We can do this by counting how many names are listed in the original workbook (the workbook with the VBA code). Keep in mind that we now have two workbooks open, the original and the new one created by our subroutine. When we added the new workbook, it became the "active" workbook, but since we want to count the names which are stored in the original workbook, we need to reference it as "ThisWorkbook". This signifies that the code is referencing the workbook in which it (the code) is contained.
We are assuming that the names are contained on the first worksheet, so we will use ThisWorkbook.Worksheets(1). You may remember from Lesson 1 that we can find out how many rows are actually used in a worksheet by looking at the "UsedRange" property of the Worksheet object.
Example 1b.
Sub CreateWorkbook()
Dim iSheetCount As Integer
Workbooks.Add
iSheetCount = ThisWorkbook.Worksheets(1).UsedRange.Rows.Count
End Sub
The next step is to set the correct number of worksheets in the new workbook. To do this we will use two separate "while" loops. One will be used if we need to remove worksheets, the second if we need to add worksheets. As I mentioned above, when a new workbook is added, it becomes the active workbook, so we can reference the new workbook using "ActiveWorkbook".
Example 1c.
Sub CreateWorkbook()
Dim iSheetCount As Integer
Workbooks.Add
iSheetCount = ThisWorkbook.Worksheets(1).UsedRange.Rows.Count
Application.DisplayAlerts = False
' If we have too many worksheets, delete one at a time until
' we reach the correct number:
While ActiveWorkbook.Worksheets.Count > iSheetCount
ActiveWorkbook.Worksheets(1).Delete
Wend
' If we don’t have enough worksheets, add more one at a time
' until we reach the correct number:
While ActiveWorkbook.Worksheets.Count < iSheetCount
ActiveWorkbook.Worksheets.Add
Wend
End Sub
The line "Application.DisplayAlerts = False" is handy when you want your VBA code to do something that Excel normally gives you an "Are you sure?" message about when you try to do it by hand. Without this line of code, Excel will display a warning message each time the code tries to delete a worksheet. Setting the DisplayAlerts property of the Application object to False turns off such messages.
Lastly, we need to set the names of the worksheets in the new workbook according to the contents of the original workbook. For this we will use a simple "for" loop, reading each name from ThisWorkbook, and assigning it to the Name property of each Worksheet object in the ActiveWorkbook.
Example 1d.
Sub CreateWorkbook()
Dim iSheetCount As Integer
Dim iSheet As Integer
Workbooks.Add
iSheetCount = ThisWorkbook.Worksheets(1).UsedRange.Rows.Count
Application.DisplayAlerts = False
' If we have too many worksheets, delete one at a time until
' we reach the correct number:
While ActiveWorkbook.Worksheets.Count > iSheetCount
ActiveWorkbook.Worksheets(1).Delete
Wend
' If we don’t have enough worksheets, add more one at a time
' until we reach the correct number:
While ActiveWorkbook.Worksheets.Count < iSheetCount
ActiveWorkbook.Worksheets.Add
Wend
For iSheet = 1 To iSheetCount
ActiveWorkbook.Worksheets(iSheet).Name = _
ThisWorkbook.Worksheets(1).Cells(iSheet, 1).Value
Next iSheet
End Sub
Note that we use the iSheet variable as both a reference into the Worksheets collection of the new workbook, and as the row number when reading the names from the original workbook.
You may also notice that an underscore character can be used to split a long line of code over two lines. The underscore must be preceded by a space.
Debugging
When something goes horribly awry in your code, or your code doesn’t produce the results you expect, you can use the Debug and Run menus in the Visual Basic Editor to help locate the source of the problems. Below are some of the most useful commands:
Command
Shortcut Key
Toolbar Button
Description
Run
F5
Executes the current subroutine or function
Toggle Breakpoint
F9
Sets or Clears a breakpoint, which pauses execution of the code
Step Into
F8
Steps through each line of code (when one subroutine is called from another this will "step into" the called subroutine)
Step Over
Shift + F8
Steps through each line of code (when one subroutine is called from another this will "step over" the called subroutine)
Reset
Stops execution of your code. You can restart execution only from the beginning.
Break
Ctrl+Break
Temporarily halts execution of your code. You can continue execution from the where it left off.
Quick Watch
Shift + F9
When execution is paused, displays the value of the highlighted variable. (You may also view the value of a variable by pausing the cursor over it)
Before you run your code, save your workbook. It doesn’t happen often, but you may inadvertently have written code which is truly evil and will cause Excel to crash. You don’t want to lose your code, diabolical though it may be. Also, pay particular attention to the keyboard shortcut Ctrl+Break. If your code has gone into an infinite loop, or something is taking much longer than you expected, Ctrl+Break is the easiest way to stop it. Once execution has been halted, you can either Reset it, or step through from where it was paused, using F8.
Try using the debugging commands to step through the homework example solution. As you step through line by line, you can actually Alt+Tab (or Alt+F11) between the VBA Editor and Excel to see each worksheet added and each worksheet name as it changes.
Functions
All of the examples that we’ve used so far have been in the form of subroutines, aka macros. In Lesson 1, I mentioned that your VBA code would be in the form of subroutines and functions, but I have yet to define what a function is, or what the difference is between a function or a subroutine.
Well, the main difference is that a function returns a value. In other words, you can call a function from a subroutine (or another function), and use the value that the function passes back. This may be more easily understood with a simple example.
Example 2.
Function UpperFileName() As String
UpperFileName = UCase(ActiveWorkbook.Name)
End Function
Sub ShowFileName()
MsgBox UpperFileName()
End Sub
Here we’ve defined a simple function called UpperFileName. We’ve declared the function type (string) in a way similar to how we declare a variable, using "As" followed by a data type. This represents which data type will be returned by the function. In this example, the function returns a string, which it creates by getting the file name of the current active workbook, and converting it to upper case (using the VB function UCase). We return this string value by using the function name as if it were a variable, and setting it equal to the string. The subroutine ShowFileName calls the function UpperFileName and displays the returned value in a message box.
(This may be apparent to you if you’ve written code before, but if you’re one of the brave souls who’s never programmed before and made it this far, you should note that you can call one subroutine or function from another subroutine or function.)
Function and Subroutine Parameters
To really make a worthwhile function, you will need the ability to send values into the function. These values are known as either parameters or arguments. For example, suppose you wanted to write a function which converted a temperature from degrees fahrenheit to degrees celsius. This function would need to take as input a temperature in fahrenheit. You can do this by listing the parameter with its type within the parentheses that follow the function name.
Example 3.
Function Celsius(dFahrenheit As Double) As Double
Celsius = (dFahrenheit - 32) * (5 / 9)
End Function
Sub ConvertTemperature()
Dim dtemperatureF As Double
Dim dtemperatureC As Double
dtemperatureF = Cells(1, 4).Value
dtemperatureC = Celsius(dtemperatureF)
Cells(2, 4).Value = dtemperatureC
End Sub
The function, named Celsius, is declared as a double, meaning that it returns a double-precision floating point value. It takes, as a parameter, a double value called dFahrenheit. It convert dFahrenheit to the corresponding celsius temperature, then returns this value.
The subroutine ConvertTemperature reads a fahrenheit value from cell D1, converts it to celsius using our function, and places the result in cell D2.
Functions can take more than one parameter. Each parameter should be separated by a comma. The function below, for example, can convert a number to either celsius or fahrenheit, depending on the value of the second parameter, bConvertToCelsius. This is a Boolean variable, which means that it can have a value or either True or False.
Example 4.
Function CelsiusOrFahrenheit(dTemperature As Double, _
bConvertToCelsius As Boolean) As Double
If bConvertToCelsius Then 'dTemperature is in Fahrenheit,
'so convert to Celsius
CelsiusOrFahrenheit = (dTemperature - 32) * (5 / 9)
Else 'dTemperature is in Celsius, convert to Fahrenheit
CelsiusOrFahrenheit = dTemperature * (9 / 5) + 32
End If
End Function
Subroutines can also take parameters. Note, however, that a subroutine which requires parameters cannot be called directly from Excel’s Tools, Macros menu. It must be called from another subroutine (or function).
Using Your Functions in the Spreadsheet
The coolest thing about writing your own functions is that you can actually use them directly in your spreadsheet. All of the examples above are contained in the Excel VBA Article 2 Example Solution.xls (link above). If you haven’t already been adding the examples to your own workbook, download the Article 2 Example Solution workbook.
In order to use your function in the spreadsheet, you only need to make one minor change. In the VBA Editor, add the word "Public" before the word "Function". This simply makes the function accessible from outside the VBA module in which it resides.
Next go to the spreadsheet, enter a number (temperature) in one cell, then click in an empty cell. Select Insert, Function from the main menu. The last item in the Function Category list on the left is called User Defined. Click on this and you will see your user-defined functions listed magically on the right. Double-click on Celsius. You will then be presented with a box in which you are prompted for the parameter which the function requires, dFahrenheit. Click on the cell in which you typed a temperature, then click OK. Your temperature has been converted from Fahrenheit to Celsius!
I don’t know about you, but I was pretty excited when I learned I could write my own functions, then use them in my spreadsheet. Then again, maybe you’re not as big a geek as I.
Sub UseExcelFunctions()
Dim iColumnCount As Integer
Dim lRowCount As Long
Dim iCol As Integer
Dim dAverage As Double
Dim dStdDev As Double
Worksheets(2).Activate
' Count number of columns and rows in use...
iColumnCount = ActiveSheet.UsedRange.Columns.Count
lRowCount = ActiveSheet.UsedRange.Rows.Count
' Cycle through each non-empty column...
For iCol = 1 To iColumnCount
' Use the Average and StDev Excel functions to
' calculate values for each column...
dAverage = WorksheetFunction.Average(Columns(iCol))
dStdDev = WorksheetFunction.StDev(Columns(iCol))
' Place results below existing data...
Cells(lRowCount + 2, iCol).Value = dAverage
Cells(lRowCount + 3, iCol).Value = dStdDev
Next iCol
End Sub
Dim iColumnCount As Integer
Dim lRowCount As Long
Dim iCol As Integer
Dim dAverage As Double
Dim dStdDev As Double
Worksheets(2).Activate
' Count number of columns and rows in use...
iColumnCount = ActiveSheet.UsedRange.Columns.Count
lRowCount = ActiveSheet.UsedRange.Rows.Count
' Cycle through each non-empty column...
For iCol = 1 To iColumnCount
' Use the Average and StDev Excel functions to
' calculate values for each column...
dAverage = WorksheetFunction.Average(Columns(iCol))
dStdDev = WorksheetFunction.StDev(Columns(iCol))
' Place results below existing data...
Cells(lRowCount + 2, iCol).Value = dAverage
Cells(lRowCount + 3, iCol).Value = dStdDev
Next iCol
End Sub
ARRAYS
Excel Visual Basic arrays are structures which are used to stored several related variables, of the same type. Each of the entries in the array can be accessed by an index number.
For example, if you had 20 members of a team and you wanted to store all the names for use in your VBA code. You could declare 20 variables to hold the team member names, as follows:
Dim Team_Member1 As String
Dim Team_Member2 As String
.
.
.
Dim Team_Member3 As String
Alternatively, you could use the much simpler and more organised method of storing the Team members in an array of 20 String variables :
Dim Team_Members(1 To 20) As String
Following the array declaration, each entry of the array is accessed by indexing as follows:
Team_Members(1) = "John Smith"
A further advantage of storing your data in an array, rather than in individual variables is if you want to perform the same action on member of the list. If your team member names are stored in 20 individual variables, you will need 20 lines of code to carry out a specific action on each name. However, if you have stored your names in an array, you can use a simple loop to carry out the action for each entry in the array.
This is shown in the example below, which prints out each name in the Team_Members array to a cell in Column A of Sheet1 of the current Excel Workbook:
For i = 1 To 20
Cells(i, 1).Value = Team_Members(i)
Next i
It is clear from the above that the handling of an array of 20 names is much less cumbersome and more organised than handling 20 individual variables, but imagine if you had 1,000 names to store! And imagine that you wanted to store Surnames separately from Forenames! It is clear that it would soon become impossible to handle this amount of data without the use of Arrays in your VBA code.
Multi-Dimensional Excel Visual Basic Arrays
The Visual Basic Arrays discussed above are one-dimensional, in that they refer to one list of Names of team members. However, arrays can have multiple dimensions. An array having two dimensions acts as a grid of values. For example, imagine that you want to store daily sales figures for the month of January, for 5 different sales teams. You would need a 2-dimensional array, consisting of 5 sets of figures over 31 days. You would then declare the array as follows:
Dim Jan_Sales_Figures(1 To 31, 1 To 5) As Currency
In order to access the entries in the array 'Jan_Sales_Figures', you need to use two indices, refering to the day of the month and the team number. For example, the sales figures for Team2 on January 15th would be referenced as:
Jan_Sales_Figures(15, 2)
You can declare arrays with 3 or more dimensions in the same way - ie. by adding further dimensions into the declaration and using a further index to reference the array entries.
Declaring Excel Visual Basic Arrays
The above sections have already given some examples of Visual Basic Array declarations, but it is worth discussing this further. As seen above, a one-dimensional array can be declared as follows:
Dim Team_Members(1 To 20) As String
This declaration tells the VBA compiler that the array 'Team_Members' has 20 variables, which are referenced by indices 1 to 20. However, we could also decide to number our array variables from 0 to 19, in which case the declaration would be:
Dim Team_Members(0 To 19) As String
In fact, the default form of array indexing is to start at 0, so if you omit the start index from the declaration, and simply declare the array as:
Dim Team_Members(19) As String
Then the VBA compiler will understand this to be an array of 20 variables, which are indexed from 0 to 19.
The same rules are applied to declarations of multi-dimensional Visual Basic arrays. As shown in the previouse example, a two-dimensional array is declared by separating the dimension indices by a comma:
Dim Jan_Sales_Figures(1 To 31, 1 To 5) As Currency
However, if we omit the start indices from both dimensions, as follows:
Dim Jan_Sales_Figures(31, 5) As Currency
this is understood to be a two-dimensional array in which the first dimension has 32 entries, indexed from 0 to 31 and the second dimension has 6 entries, indexed from 0 to 5.
Dynamic Arrays
In the above examples, the arrays all have fixed dimensions. However, in many cases, we don't know how big an array is going to be before runtime. We could solve this be declaring a huge array, in an attempt to cover the maximum possible size needed, but this would use up an unnecessarily large amount of memory and could slow down your program. A better option would be to use a Dynamic array, which is an array that can be sized and re-sized as many times as you like, during the execution of a macro.
A dynamic array is declared with empty parentheses, as follows:
Dim Team_Members() As String
You then need to declare the dimension of the array during the execution of the code, using the ReDim statement:
ReDim Team_Members(1 To 20)
If, during the execution of the code, you need to extend the size of your array, you can use ReDim again:
If Team_Size > 20 Then
ReDim Team_Members(1 To Team_Size)
End If
It should be noted that resizing a dynamic array in this way will result in the loss of all the values that had previously been stored in the array. If you want to avoid this loss, and keep the values that had previously been assigned to the array, you need to use the "Preserve" keyword, as shown below:
If Team_Size > 20 Then
ReDim Preserve Team_Members(1 To Team_Size)
End If
The disadvantage of using the "Preserve" keyword when resizing Visual Basic Arrays is that you can only change the upper bound of an array, not the lower bound. Also, if you have a multi-dimensional array, the use of the "Preserve" keyword limits you to changing only the last dimension of the array.
For example, if you had 20 members of a team and you wanted to store all the names for use in your VBA code. You could declare 20 variables to hold the team member names, as follows:
Dim Team_Member1 As String
Dim Team_Member2 As String
.
.
.
Dim Team_Member3 As String
Alternatively, you could use the much simpler and more organised method of storing the Team members in an array of 20 String variables :
Dim Team_Members(1 To 20) As String
Following the array declaration, each entry of the array is accessed by indexing as follows:
Team_Members(1) = "John Smith"
A further advantage of storing your data in an array, rather than in individual variables is if you want to perform the same action on member of the list. If your team member names are stored in 20 individual variables, you will need 20 lines of code to carry out a specific action on each name. However, if you have stored your names in an array, you can use a simple loop to carry out the action for each entry in the array.
This is shown in the example below, which prints out each name in the Team_Members array to a cell in Column A of Sheet1 of the current Excel Workbook:
For i = 1 To 20
Cells(i, 1).Value = Team_Members(i)
Next i
It is clear from the above that the handling of an array of 20 names is much less cumbersome and more organised than handling 20 individual variables, but imagine if you had 1,000 names to store! And imagine that you wanted to store Surnames separately from Forenames! It is clear that it would soon become impossible to handle this amount of data without the use of Arrays in your VBA code.
Multi-Dimensional Excel Visual Basic Arrays
The Visual Basic Arrays discussed above are one-dimensional, in that they refer to one list of Names of team members. However, arrays can have multiple dimensions. An array having two dimensions acts as a grid of values. For example, imagine that you want to store daily sales figures for the month of January, for 5 different sales teams. You would need a 2-dimensional array, consisting of 5 sets of figures over 31 days. You would then declare the array as follows:
Dim Jan_Sales_Figures(1 To 31, 1 To 5) As Currency
In order to access the entries in the array 'Jan_Sales_Figures', you need to use two indices, refering to the day of the month and the team number. For example, the sales figures for Team2 on January 15th would be referenced as:
Jan_Sales_Figures(15, 2)
You can declare arrays with 3 or more dimensions in the same way - ie. by adding further dimensions into the declaration and using a further index to reference the array entries.
Declaring Excel Visual Basic Arrays
The above sections have already given some examples of Visual Basic Array declarations, but it is worth discussing this further. As seen above, a one-dimensional array can be declared as follows:
Dim Team_Members(1 To 20) As String
This declaration tells the VBA compiler that the array 'Team_Members' has 20 variables, which are referenced by indices 1 to 20. However, we could also decide to number our array variables from 0 to 19, in which case the declaration would be:
Dim Team_Members(0 To 19) As String
In fact, the default form of array indexing is to start at 0, so if you omit the start index from the declaration, and simply declare the array as:
Dim Team_Members(19) As String
Then the VBA compiler will understand this to be an array of 20 variables, which are indexed from 0 to 19.
The same rules are applied to declarations of multi-dimensional Visual Basic arrays. As shown in the previouse example, a two-dimensional array is declared by separating the dimension indices by a comma:
Dim Jan_Sales_Figures(1 To 31, 1 To 5) As Currency
However, if we omit the start indices from both dimensions, as follows:
Dim Jan_Sales_Figures(31, 5) As Currency
this is understood to be a two-dimensional array in which the first dimension has 32 entries, indexed from 0 to 31 and the second dimension has 6 entries, indexed from 0 to 5.
Dynamic Arrays
In the above examples, the arrays all have fixed dimensions. However, in many cases, we don't know how big an array is going to be before runtime. We could solve this be declaring a huge array, in an attempt to cover the maximum possible size needed, but this would use up an unnecessarily large amount of memory and could slow down your program. A better option would be to use a Dynamic array, which is an array that can be sized and re-sized as many times as you like, during the execution of a macro.
A dynamic array is declared with empty parentheses, as follows:
Dim Team_Members() As String
You then need to declare the dimension of the array during the execution of the code, using the ReDim statement:
ReDim Team_Members(1 To 20)
If, during the execution of the code, you need to extend the size of your array, you can use ReDim again:
If Team_Size > 20 Then
ReDim Team_Members(1 To Team_Size)
End If
It should be noted that resizing a dynamic array in this way will result in the loss of all the values that had previously been stored in the array. If you want to avoid this loss, and keep the values that had previously been assigned to the array, you need to use the "Preserve" keyword, as shown below:
If Team_Size > 20 Then
ReDim Preserve Team_Members(1 To Team_Size)
End If
The disadvantage of using the "Preserve" keyword when resizing Visual Basic Arrays is that you can only change the upper bound of an array, not the lower bound. Also, if you have a multi-dimensional array, the use of the "Preserve" keyword limits you to changing only the last dimension of the array.
Subscribe to:
Posts (Atom)