|
|
Have you ever written an Excel proficiency exam? Maybe you'll have some advice or tips for the person who wrote to me this week, asking for help with the Excel Expert 2007 exam. He's having trouble with the macros and custom functions that will be part of the test.
It's been a long time since I wrote the Excel Expert exam, that was part of the old Microsoft Office User Specialist series. The exam has probably changed many times since then, but back then it was a mixture of multiple choice questions and simulated workbooks (if I'm remembering correctly!)
Anyway, I passed, and the certificate is still proudly displayed on my office wall. Well, it's pinned to the wall, behind the door, but it's still in good shape! Wow, June 1999 – that was a long time ago.
The Excel Expert Test
The Microsoft website has a list of topics that are covered on the exam, including this section on Managing Macros and User-Defined Functions:
- Record and edit a macro.
- This objective may include but is not limited to: recording a macro and editing a macro in Visual Basic for Applications (VBA)
- Manage existing macros.
- This objective may include but is not limited to: moving macros between workbooks, assigning a shortcut key to an existing macro, assigning a macro to a button in a worksheet, and configuring macro security levels
- Create a user-defined function (UDF).
Record and Edit a Macro
There are written instructions and a video on the Contextures website, for recording and testing a macro in Excel. That article briefly discusses macro security levels, and showing the Developer tab.
To see how to edit a recorded macro, you can watch the video on this blog post: Excel VBA Edit Your Recorded Macro. There are written instructions there too, in case you'd prefer to read about it.
Manage Existing Macros
If you need to copy macros into a workbook, or from one workbook to another, there are instructions here: Adding Code to a Workbook
For details on assigning a macro to a worksheet button, take a look at this page: Excel VBA Worksheet Macro Buttons. To see the code and buttons, you can download the sample workbook from that page.
And, of course, if you want an extensive online course that covers all this, and much more, you can enroll in Chandoo's Excel VBA school.
______________
To focus on specific data in a pivot table, you can use report filters or field filters.
However, using those filters affects the pivot table subtotals and grand totals. Only the amounts for the filtered data are included in the results.
Collapse or Expand the Fields
Another option for hiding the pivot table details is to collapse one or more of the items, so only its subtotal is showing. In the screenshot below, I'm about to click on the Collapse button for the Bars category in the pivot table.
The Bars data is still in the pivot table, but only its subtotal is showing, not the City detail rows. The subtotals for East and Bars are not affected.
Show the Details Again
After you're finished focusing on the specific data, you can show all the detail rows again.
A quick way to show all the details for a field is to right-click on an item in the field, such as Bars.
Then, in the popup menu, click Expand/Collapse, and click Expand Entire Field.
Watch the Pivot Table Video
To see the steps for hiding or showing the detail items in a pivot table, you can watch this short Excel video tutorial.
Or watch on YouTube: Hide Pivot Table Detail Without Filtering
___________
Someone emailed me this week, about a problem he was having with my sample Part Data Entry UserForm.
When I took a look at the workbook, everything seemed okay, and the code had been copied and altered correctly.
Then I noticed that there was a formatted Excel table on the data collection sheet, which wasn't in my original file. That can cause problems if you're using Excel VBA to add data to the first blank row on the worksheet.
Change the Last Row Code
In the comments for my Find First Blank Row blog post, Rick Rothstein suggested this code revision:
LastRow = Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row
Rick mentioned that this formula ignores cells with formulas that are displaying the empty string. If your situation is such that you need to identify formula cells that might be displaying the empty string, then change the xlValues argument to xlFormulas.
So, I changed the Part Data Entry code, to use the Find method for finding the last row. I replaced this old line of code:
'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row
With this line of code:
iRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1
Parts Data Entry UserForm With Combo Boxes
There is another version of the Parts Data Entry UserForm, and it's a little fancier, with combo boxes to select parts and locations. I have updated the Parts Data Entry UserForm With Combo Boxes too, with the revised last row code.
Get the Updated Sample Files
You can download the updated versions of the parts data entry forms on the Contextures website.
Excel VBA School
If you're struggling with Excel programming, Chandoo has re-opened his Excel VBA school and registration is ongoing. You can sign up for the VBA classes only, or choose a package that includes Excel School, and you could even add the Excel Dashboards class, and become an Excel master at your own pace.
The VBA course starts at the beginner level, and there are 13 weeks of classes that are designed to take you to the advanced skill level. Online videos show you the steps, and there are workbooks and code examples to download.
You can see the details, and sign up here: Chandoo's Excel VBA school
_________________
You know how tough it can be to maintain two versions of the same file. It creates twice as much work for you, and no extra rewards!
So, instead of having two versions of the Excel weight loss tracker – pounds and kilograms – I've rolled them into one workbook. For now, there is a separate file for the stone measurements, because it has a different layout on the data entry sheet. If possible, I'll roll that version in later.
Select a Measurement System
When you open the Excel weight loss tracker, go to the WeightGoals sheet and select a measurement system – pounds or kilos – from the data validation drop down.
That changes the labels and calculations in the worksheet. Since we've selected Pounds in this example, the labels say "Lbs to Lose" and "Height (Inches)". Those would show "Kgs to Lose" and "Height (cms") if we had selected Kilos.
Measurement System Lookup Table
The workbook has a measurement system lookup table, which uses INDEX and MATCH formulas to find the correct information for the measurement that you selected. Even the chart title changes, based on your selection.
Weight Loss Tracker Improvements
In this version of the weight loss tracker, there is a pivot table to summarize the data (why didn't I think of that before!). After you enter data, just click Refresh All, and the pivot table and dashboard chart are updated. You can add as many rows of data as you need, without struggling to change the data range.
The chart has a target line too, so you can see how close you're getting to your goal.
Download the Excel Weight Loss Tracker
You can download a zipped copy of the Excel weight loss tracker, and try it for yourself. The sheets are protected, with the data entry cells unlocked, and there's no password on the worksheets.
There are sample files for Excel 2007/Excel 2010 (updated version), and Excel 2003 (old version of the file) on the Contextures website: Excel Weight Loss Tracker
If you can think of any enhancements for the next version, please let me know.
______________
To make it easier for people to enter data, you can create drop down lists on an Excel worksheet.
Usually the source lists are stored in the same workbook as the drop downs. However, with named ranges, it is possible to use a list in a different workbook.
In the screen shot shown below, the original list is in the workbook at the left. The drop downs are in a different workbook, on the right.
There Is a Catch
My preference would be to keep the lists and drop downs in the same workbook, but if you need to have them in separate files, this technique will allow you to do that.
There's one catch though, when using this data validation technique. The source workbook, which contains the original list, must also be open, when you are using the drop down lists.
So, it's not a perfect solution, but it's fairly easy to implement, as long as you remember to open the other workbook too.
Excel 2010 Instructions
I've just uploaded a video with instructions for this technique in Excel 2010, so you can see the steps for creating the named ranges and data validation drop down lists.
The written instructions for Excel 2007 and Excel 2010 are in this blog post: Data Validation List From Different Workbook
You can also watch the video on YouTube: Excel Drop Downs From List in Different Workbook
________________
Happy New Year! I hope you had a safe and happy New Year's Eve celebration, and are off to a good start in 2012. Things got a bit rowdy at the Contextures office party, and I found these guys passed out on the floor, the next morning.
Change All Pivot Table Filters
Despite the wild parties, I was able to get some work done over the holidays. There is a new sample file on the Contextures website, that changes all the pivot tables, when you change a report filter in one pivot table.
For example, if you change the "Item" report filter in one pivot table, all the other pivot tables with an "Item" filter will change. They get the same report filter settings that were in the pivot table that you changed.
Select Multiple Items
In this version of the sample file, the "Select Multiple Items" setting is also changed, to match the setting that is in the pivot table that you changed.
In the screen shot below, the Item field has the "Select Multiple Items" setting turned off. If any other pivot tables in the workbook have an "Items" filter, the "Select Multiple Items" setting for those fields will also change.
How It Works
The multiple pivot table filtering works with event programming. There is Worksheet_PivotTableUpdate code on each worksheet, and it runs when any pivot table on that worksheet is changed or refreshed.
For each report filter field, the code checks for the Select Multiple Items setting, and changes it on all the pivot tables with the same report filter field. The code loops through all the worksheets in the file, and through each pivot table on each sheet.
Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
Dim wsMain As Worksheet
Dim ws As Worksheet
Dim ptMain As PivotTable
Dim pt As PivotTable
Dim pfMain As PivotField
Dim pf As PivotField
Dim pi As PivotItem
Dim bMI As Boolean
On Error Resume Next
Set wsMain = ActiveSheet
Set ptMain = Target
Application.EnableEvents = False
Application.ScreenUpdating = False
For Each pfMain In ptMain.PageFields
bMI = pfMain.EnableMultiplePageItems
For Each ws In ThisWorkbook.Worksheets
For Each pt In ws.PivotTables
If ws.Name & "_" & pt <> wsMain.Name & "_" & ptMain Then
pt.ManualUpdate = True
Set pf = pt.PivotFields(pfMain.Name)
bMI = pfMain.EnableMultiplePageItems
With pf
.ClearAllFilters
Select Case bMI
Case False
.CurrentPage = pfMain.CurrentPage.Value
Case True
.CurrentPage = "(All)"
For Each pi In pfMain.PivotItems
.PivotItems(pi.Name).Visible = pi.Visible
Next pi
.EnableMultiplePageItems = bMI
End Select
End With
bMI = False
Set pf = Nothing
pt.ManualUpdate = False
End If
Next pt
Next ws
Next pfMain
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
Download the Sample File
To test the code, you can download the sample file from the Contextures website. On the Sample Excel Files page, in the Pivot Tables section, look for PT0025 - Change All Page Fields with Multiple Selection Settings. The file will work in Excel 2007 or Excel 2010, if you enable macros.
Watch the Excel Video Tutorial
To see the steps for copying the code into your worksheet, and an explanation of how the code works, you can watch this short Excel video tutorial.
Or watch on YouTube: Change All Pivot Tables When One Changes
______________________
If you have similar data on two or more worksheets, you might want to combine that data in a pivot table, to show the summarized results. Unfortunately, the pivot table from data on multiple sheets can be a disappointment.
Create a Pivot Table with Programming
A couple of years ago, Excel MVP, Kirill Lapin (KL), shared a sample file that he created, with amendments by Hector Miguel Orozco Diaz. It uses code to automatically create a pivot table from multiple sheets in a workbook.
You can read the details here: Create a Pivot Table from Multiple Sheets.
Revised Solution
Kirill's sample file was created as a conceptual prototype, and targeted advanced VBA users. The code has minimal error handling and compatibility checks.
However, the sample file was extremely popular, and Excel users at all skill levels wanted to adopt this solution in their own applications. To make things easier, Kirill has created a similar solution based on ADO.
Advantages:
- No need for temporary file generation
- The code is faster and less prone to errors
Disadvantages:
- No manual refresh of the PivotTable
- Need to rebuild connection from the scratch to update the cache with new data
Download the ADO Sample File
You can download the new ADO version of the file from the Contextures website: PT0024 - Pivot Table from Multiple Sheets - ADO version. There is also a "Plug and Play" version of the file, at the same link.
________________
When you insert a comment in Excel, a rather boring yellow rectangle appears, where you can add your text.
That's all very proper and dignified, but sometimes you want something a bit more attention-getting.
In the good old days of Excel 2003, it was easy to change the comment shape, with a simple right-click. In Excel 2007 and Excel 2010, you need to add a command to the QAT, so you can change the comment shape.
Add the Change Shape Command to the QAT
- At the right end of the QAT, click the drop down arrow
- Click More Commands
- In the Choose Commands From drop down, click All Commands
- In the list of commands, click Change Shape, and click Add, to move it to the Quick Access Toolbar
- Close the Excel Options window.
Change the Comment Shape
- Right-click the cell which contains the comment.
- Choose Edit Comment
- Click on the border of the comment, to select it.
- On the QAT, click the Change Shape command, and click on a shape to select it.
- If necessary, drag the corner handle of the comment, to adjust its size, to fit the text.
- When finished, click outside the comment.
Watch the Video
To see the steps for adding the Change Shape command to the QAT and changing the comment shape in Excel 2010 or Excel 2007, please watch this short Excel video tutorial.
Or watch the video on YouTube: Change a Comment Shape in Excel 2010
_____________________
Uh-oh! It's almost Christmas and you haven't mailed any greeting cards yet. Don't worry, you can create a personalized Excel message box instead! That will warm your co-workers' hearts, and it saves paper and postage costs too.
Get the Application UserName
In Excel VBA, you can use Application.UserName to get the registration name for the Microsoft Office applications.
However, some people don't update that setting, and it might show a company name, instead of the user's name.
Get the Network UserName
Another option is to get the network user name, with a Windows API call. There is sample code in the Microsoft Knowledgebase: Visual Basic Procedure to Get Current User Name, and I've used a variation on that code in my sample file.
You can find other API code examples on the VB.Net site, and API code on The Access Web, to pull more information, such as Computer name, or locale settings.
In the sample code shown below, the network username is pulled from the DLL. This code is stored in a regular code module.
'==================================
' Access the GetUserNameA function _
' in advapi32.dll and
' call the function GetUserName.
Declare Function GetUserName _
Lib "advapi32.dll" _
Alias "GetUserNameA" _
(ByVal lpBuffer As String, _
nSize As Long) As Long
'==================================
' Main routine to Dimension variables,
' retrieve user name
' and display answer.
Function Get_User_Name() As String
' Dimension variables
Dim lpBuff As String * 25
Dim ret As Long, UserName As String
' Get the user name minus any
' trailing spaces found in the name.
ret = GetUserName(lpBuff, 25)
Get_User_Name = Left(lpBuff, InStr(lpBuff, Chr(0)) - 1)
End Function
'==================================
Create the Personalized Excel Message
Next, you can create an Excel message box, and show the network username in the message text. In this example, I've also calculated the year for the current date. Then, add 1 to the year number, to offer best wishes for the upcoming year.
'==================================
Sub ChristmasMessage()
' show greeting with username and
' upcoming year
MsgBox " Merry Christmas, " _
& Get_User_Name & " " _
& vbCrLf _
& " and best wishes for " _
& Year(Date) + 1 & "! "
End Sub
'==================================
Download the Sample File
You can visit the Contextures website, to download the personalized Excel Message Box sample file. The file is in Excel 2007/2010 format, and is zipped.
In the sample workbook, the Workbook_Open event runs this macro, so the personalized message appears when the file is opened, if macros are enabled.
Watch the Video
To see the steps for adding the GetUserName code to your workbook, and creating a personalized Excel message, you can watch this short Excel video tutorial.
Or watch on YouTube: Excel Message with User Name
__________
I'm just getting back from a vacation in South Carolina, where I didn't use Excel too often, except to add up all my Christmas shopping expenses. If you're on my shopping list, you might be getting a basket from the Charleston market.
Or maybe you'd like some of the local barbeque sauce or a bag of grits?
Christmas Planner
There are only 10 days until Christmas, and I'm almost ready. How about you? If you're still planning and shopping, there is an Excel Christmas planner on the Contextures website, that you can use to help you stay organized.
The Excel Christmas planner has a budget sheet, where you can plan and track your spending.
Christmas Gift List
If you're lucky, you don't have too many gifts to buy. But even for a few gifts, it helps to make a list to keep track of costs. The Excel Christmas planner helps you track your gifts too.
Christmas Tasks
The Excel Christmas planner has other sheets too, including a Christmas task list, so you can keep track of all those important things you're supposed to do over the next few days. You don't want to realize on December 24th that no one has ordered the turkey!
Winter Weather
It was nice to spend some time in the warmer weather, and yes, it's colder back here in Canada, but at least we don't have to watch for alligators! Now I'd better go and order that turkey.
_______________
|
|
