Happy Chappy: Bill Jelen is Mr Excel (Photo credit: Wikipedia)

I might as well share this list of recommended reading for Excel Macros and VBA on my blog since I am often asked.

I have found the following resources particularly useful

Tutorials to deepen your knowledge of Excel VBA:

http://excelvbatutor.com/vba_tutorial.html

A great overall tutorial if you want to get the basics right. Don’t let the version (Excel 2003) put you off as there are few changes between the various versions of Excel – especially as you are starting to learn the language.

On http://www.ozgrid.com/VBA/ you will find great bits of code samples for every day needs, however it is not an end-to-end tutorial. Copy and paste for sure!

Come to think of it, I also like http://www.cpearson.com but you are probably quite busy with the above already!

Books about Excel and VBA

I also like Bill Jelen aka Mr Excel’s books so you might want to give a try to VBA and Macros: Microsoft Excel 2010.

This one, Office VBA Macros You Can Use Today: Over 100 Amazing Ways to Automate Word, Excel, PowerPoint, Outlook, and Access is interesting in that it shows ways to make these applications work together. I did not find the provided examples that relevant to my work but still, they are great to get you practicing some advanced skills.

That’s about it for now. If you have any other favourites of your own, please list them in comments below.  Best of luck with your coding !

You can download this worksheet from the Office Web App link below or here: Date Ranges with Excel Functions

A quick guide to the functions used:
TODAY() – displays the current date.
WEEKDAY(serial_number, [return_type]) – returns a number which represents the day of the week e.g. Monday = 0. Change the return_type if you prefer Sunday to be 0. Very useful when you know that today’s weekday is 0 and adding 7 then means Sunday.
DATE(year, month, day) - returns a date from three different numbers e.g. DATE(2012,12,31) returns the last day of the year.
YEAR(serial_number) – returns the year for the date entered as a serial number.

Using Excel Functions to calculate dates and date ranges

Image via Wikipedia

Learn how to use the PowerPoint shapes and drawing tools to create your own shapes, from simple to elaborate. You never need to pay for your cliparts again if you have the patience to draw your own.

In this video, Diane Elkins demonstrates how to

• use the Freeform tool to draw new shapes
• manipulate the shape with Edit Points
• Apply Shape Fill to add texture or relief to the shape

Very clever altogether! 

 

This is where my Universal Social Charge (USC) Calculation in Excel workbook comes in handy. Here is a quick tour of the USC Calculator Excel workbook.

Download the Excel Workbook Universal Social Charge (USC)

• USC Calculations for Excel 97-2003
• USC Calculations for Excel 2007-2010

How to use the Excel Workbook Universal Social Charge (USC)

1. The workbook opens to display a table of contents. Click USC Calculator.
2. Type an Annual Gross Pay amount in the red box. The calculations are shown in the grey cells. Click Copy the formula.
   
3. You can copy the formula and paste it into your own workbook. Just make sure to update the cell reference as indicated. Click USC Rules and Calculation table.
4. If the rates change, use this page to update them. You can also customise and print the Ready Reckoner (table) to have a quick reference handy.

See also: Universal Social Charge on irishtaxnews.wordpress.com

I always love it when I train people who have limited computer skills and I get to show them copy and paste or cut and paste. While initially confused by the concept, they soon adopt the clipboard functionality and it becomes their number one instant productivity tool.

Cut, copy and paste can be used to move folders around the computer, get  files into folders, insert pictures into documents, duplicate text across files. And because it has become so ubiquitous, we take it for granted.

• When the iPhone was launched in 2007, its lack of clipboard functionality caused much criticism. When finally added, cut and paste was celebrated around the blogosphere iPhone finally gets copy and paste.
• And when we take things for granted, we sometimes use them without thinking as with this example of typographical instructions left behind, taken from the excellent HeritageWeek.ie. Can we guess? The event organiser (Chester Beatty Library) emailed their listing with special instructions for spelling. The website editor copy-pasted the whole thing and did not even need the instructions at all then.

So be careful that it doesn’t happen to you! Learn how co cut, copy and paste with the clipboard and marvel at the time savings offered by that simple feature. If it didn’t exist we would have to invent it again.

Video: Outlook canned answers at the touch of a button

Create a new shortcut button to a message template

1. Right-click any toolbar and select Customize in the contextual, pop up menu.
   
2. In the Customize dialog, select the Commands tab. Under Categories, click File. Under Commands (the right part of the dialog box), hold the left click button on your mouse or pointing device and drage Mail Message into a toolbar. In this example I drag the button to the New dropdown.
   
3. The new command is now visible in the toolbar menu New. Do not close the Customize dialog box.
   
4. Right-click the New Message command. In the contextual,  pop up menu, type the Name to display for this command (instead of Mail Message, I called mine Welcome Message)
   
5. Right-click the command a second time and this time in the pop up menu, select Assign Hyperlink and Open.
   
6. In the Link to File dialog box, browse to the message template you want to associate with this command button. Did you make a note of it as suggested in this tip?
    
7. You can close all dialog boxes now, including the Customize dialog box. You now have a command button which starts your message template.
   

Video: More easy subtotals in a Microsoft Excel database with the SUMIF function

Using the SUMIF function to calculate the total by region

• The list of regions is in D4:D28 and is the first argument
• The Criteria is either the name of the region (Sligo) or a cell which contains the name (L4)
• The Sum_Range is the list of values and it must match the list of regions i.e. same height.

Using SUMIF in a Summary table

You can see in how the values calculated in the table of the left are obtained with the SUMIF function. Note that by using absolute references ($D$4:$D$28) it is easy to copy the formula and extend it to all rows at once with AutoFill.

Why not follow us?

from RSS, e-mail updates, share us on Facebook or just bookmark us in your browser. We have new tips all week long and they never take more than two minutes.

Video: Get Excel charts and tables always up to date in your Microsoft PowerPoint presentation

1. Copy the data or chart from Excel
   
2. Switch to your PowerPoint presentation and select Paste Special from the Edit menu
    
3. In the Paste Special dialog box, choose the Paste Link option.

The object you are inserting is recognised as Microsoft Office Excel Worksheet Object.

It will show as Microsoft Office Excel Chart Object if you started from an Excel chart.

Update Links

When you open the PowerPoint presentation in the future, a message will warn you that the presentation is linked to other files and it gives you an option to Update Links and see the latest figures – or to cancel and see the slides with the figures as they were last shown.

Video: Make Microsoft Access forms more user-friendly with big navigation controls

Adding command buttons for navigation to a form

1. Switch to Design view
2. Display the Toolbox if it is not visible by clicking Toolbox

1. Click Command Button on the toolbox
2. Follow the steps of the Command Button Wizard
   1. choose the type of button and associated command
   2. change the appearance if required
   3. assign a name to the button (useful if you further programme the form)

This is the form with the two large navigation buttons you created which are more user-frienldy ways to guide users.