Saturday, December 25, 2010

Tables in Excel 2007, 2010

A support article at Microsoft - this is a quickref sheet. One point - remember that the zebra stripes are dynamic and managed by the table but un-managed and baked in after they are converted to a range.

in reference to: Quick Reference Card - Use Excel tables to manage information - Microsoft Office (view on Google Sidewiki)

Wednesday, November 24, 2010

Excel and the Maths inclined

Excel is now used by all and sundry. For those with higher Math in mind, here is a resource page.

in reference to: excel resources (view on Google Sidewiki)

Tuesday, October 26, 2010

The MsgBox prompt IF…THEN…ELSE Access macro

I don’t like Microsoft Access macros as much as I like VBA – I do a lot of work in VBA and know it well – although Access macros are useful in limited, simple tasks, anything that requires VBA expressions are not fun because you have to enter a VBA expression in a non VBA IDE.


Notice also, the editing environment is not conducive to using VBA Enumerations in the MSGBOX expression.

Note that Microsoft Access 2010 has enhanced the Macro Designer and added more Macro commands – you can create a macro in Access 2010 that will not survive running the same file under Access 2007. If you used VBA, you will probably need to re-compile to ensure VBA binary compatibility and keep on going.

Dynamic Named Ranges in Excel the classic way...

In Excel 2007 forwards, lists that expand and contract needed OFFSET calculation. Now with the new Tables feature, we don't have to use this old method.

in reference to: Dynamic Named Ranges That Expand and Contract Automatically in Your Excel Spreadsheet (view on Google Sidewiki)

Wednesday, October 20, 2010

Watermark and Background in Excel worksheets

Funny thing about Excel backgrounds – You’d think that

Page Layout > Background

would apply a background image to the worksheet. It does and it doesn’t. It does display the graphic on-screen but not when you print. I think.

If you want a washed out watermark, what you need to do is to

  1. go to Page Header (or Page Footer?) section boxes.
  2. Insert Picture
  3. Format Picture > Picture > (Image Control) > Color > Washout


Wednesday, September 29, 2010

Wednesday, September 8, 2010

Migrating to Office 2010

PDF Migration Guides, Interactive Menu to Ribbon applets (Silverlight based) and Command Reference Excel workbooks here

in reference to: Office 2010 Migration Guides - Excel - Microsoft Office (view on Google Sidewiki)

Saturday, September 4, 2010

CSS Tutorials

A well written but dated book by Stephen Mulder - the Web Designer's Guide to Style Sheets - Hayden Press is no longer updated and the website has disappeared. Mulder wrote summaries for webmonkey - they're very readable.

in reference to: Mulders Stylesheets Tutorial | Webmonkey | (view on Google Sidewiki)

Wednesday, August 18, 2010

Dynamic Ranges in Excel - low maintenance

I like Named Ranges in Excel. They free you from remembering to $A$1 every time you need to refer to an absolute block. And Named Ranges allow you to set up a reference block away from the worksheet that contains the Data Validation in-cell drop down list.

However, one more thing, Named Ranges still don't autoexpand to handle more items added later. Use ranges overlaid onto Lists (Excel 2003) or Tables (Excel 2007 and later). Or use OFFSET and COUNTA formula directly in the Name Manager specification.

in reference to: Excel Video Lessons | Dynamic Validation (view on Google Sidewiki)

Surprise, Surprise - the VLOOKUP cheatsheet

Here are a bunch of VLOOKUP references

Monday, August 16, 2010

Interesting app

Haven't tried it yet - works with Powerpoint and OneNote - Microsoft Interactive Classroom - you can poll and quiz participants in a class

in reference to: Download details: Microsoft Interactive Classroom (view on Google Sidewiki)

Friday, August 13, 2010

The catalogue of Excel functions

Often, in Excel classes, participants ask about Excel functions and formulae. Of course, our job as trainers is to show and tell about functions specifically how to key them in, how to handle keying errors and recover from syntax errors. I tend to tell people that Excel has so many functions (and even more in the Excel 2010 version) that as generalists we don't have the domain intimacy with specific theory used in a function. Easily getting a list of function names in the catalogue is important. Walkenbach has written several Excel books and Excel function books. Here is a listing of the functions, with hyperlinks to the Microsoft Office website.

in reference to: The Spreadsheet Page Excel Downloads: Excel 2007 Function List (view on Google Sidewiki)

Monday, August 9, 2010

Connect Access 2010 to cloud computing Azure

Interesting. Remember when IT snobs say that Access is lightweight? Not big iron? How about when Access front ends to Windows Azure?

in reference to: Access 2010 and SQL Azure - Microsoft Access - Site Home - MSDN Blogs (view on Google Sidewiki)

The comprehensive Excel Range Name Manager has been updated

JKP and collaborators have updated their Name Manager to support 64 bit Excel 2010. Really useful if you dig Range Names

in reference to: Name Manager (view on Google Sidewiki)

Created custom menus in Excel

Here's an short and sweet article from JKP Application Development Services about the difference in custom user menus in classic Excel vs Excel 2007.

in reference to: Menu's (view on Google Sidewiki)

Saturday, July 24, 2010

The different types of Webpage Layouts

We were discussing the difference between static and elastic width webpage layouts in a class. Here's an interesting article

in reference to: A Guide on Layout Types in Web Design (view on Google Sidewiki)

Friday, July 23, 2010

Number and Date formats

I had trouble the other day trying to find the Office Help reference for the Date formatting placeholders in Word. The good thing about the Office suite is that Word, Excel, Access all share the same formatting syntax.

in reference to: Number Formats in Microsoft Excel (view on Google Sidewiki)

Excel Pivot Table Tutorials

by Debra Dalgleish - heaps and heaps of tips

in reference to: Excel Pivot Table -- Dynamic Data Source (view on Google Sidewiki)

Chandoo does conditional formatting in Excel like a rockstar

Some inventive ways to use Excel conditional formatting and this does not even need the latest Excel version.

in reference to: Conditional Formatting in Excel | - Learn Microsoft Excel Online (view on Google Sidewiki)

Saturday, July 17, 2010

Wednesday, July 14, 2010

Revised Web References

Here’s a revised reading list for those who want a leg up to the world of web pages.

Friday, July 9, 2010

You've got .chm compiled Help files but you can't see the content

Actually, very frustrated - followed the instructions, copied the text to the .reg file but it didn't work. Fiddled for ages, then read more MSKB articles. The article shows a UNC path to your .chm files but really the files were on my local hard drive so UNC is not appropriate. After all that reading and testing, here is text for Windows 7 .reg file - copy this into notepad, save as a .reg file and run it - I ran it from an admin account and suspected UAC and elevated security issues with Windows 7 but that was a red herring.


  1. c:\refs is where I put the .chm files
  2. I used both the C:\ and the file protocol - I think the file protocol was the one that solved it.

Windows Registry Editor Version 5.00




Microsoft Office 2007 Object Model Maps

For VBA programming. MSDN Office Developer Center Reference

in reference to: Object Model Maps (view on Google Sidewiki)

Friday, June 18, 2010

Tuesday, May 18, 2010

What's new in OneNote 2010

I don't use OneNote enough - I've had too many other knowledge libraries. But I reckon the 2010 is the one where I take more notice. I already have some notes in the 2007 edition, the 2010 edition seems to be a landmark.

in reference to: Facebook (view on Google Sidewiki)

Tuesday, May 11, 2010

Saturday, May 8, 2010

The Pesky Excel 2010 Object Model difference

This afternoon, I was working with Access 2010 on an Access datafile that was created and is in production in an Access 2007 / Excel 2007 environment. There is inter application Automation - VBA in Access has a reference to Excel. Things seemed to be working smoothly until I noticed some odd glitches. On one Access 2010 machine, certain parts of code would not work - the error message was something like "unknown database object". On another Access 2010 machine, the DATE() function gave an error. Checked the object library references in the VBA IDE. Found that Excel 14 / Excel 12 alternately were missing in action. In the relevant Office version, the Excel Object Model Library was there, but not automatically ticked, while the wrong, missing Excel Object Model Library was.

After a while, I avoided using Access 2010 to develop the project.

Reblog this post [with Zemanta]

Thursday, May 6, 2010

Bye Clipomatic, Hello ClipX

I used to be a fan of Mike Lin's Clipomatic. Then my Windows XP became too crowded and my mult-clipboard programs stopped working properly. Now I am on Windows 7 but Clipomatic is sadly out of date and does not cope with the security settings to %Program Files%

Fortunately, ClipX has been updated, trying out the beta 7 x86 release.....

in reference to: ClipX (view on Google Sidewiki)

Tuesday, May 4, 2010

Saturday, May 1, 2010

Life after VBA

It's difficult to figure out what lies after VBA when you are cool with VBA in Office and see that there are nice things you could do but they are in .NET. Aside from the skilling up and hoping that the .NET bits and pieces are installed on end users' machines, it's the case of what features that could be tempting. .NET is evolving at a rapid rate but not every feature is grokkable or even worth grokking to you.

Here are some videos.

in reference to: How Do I Videos for Office (view on Google Sidewiki)

Thursday, April 29, 2010

Do you need a speller all the time?

Microsoft Office applications have a speller - a really good one nowadays. But for those other systems that don't have a speller, try TinySpell - it doesn't curvy underline all your words but it does raise an pop up window when it detects you hitting a space after a word.

in reference to: Impact (view on Google Sidewiki)

Friday, April 23, 2010

Friday, April 16, 2010

Thursday, April 1, 2010

Copying shape size in Visio

"Another, more civilized, way of doing this is to open a Size & Position window from the View menu. In this window, you can see the size and position properties of the selected shape. You can then select several shapes, and update their width or height by typing the required value into the box. In order to make those shapes the same as the one you want, you have to select it first so that its properties are shown in the window. Ah, but there is another problem. You can't just hit enter, the edit box recognizes the fact that no change was made and does nothing, you have to retype the value again to have the desired effect. Still it's better than nothing"

in reference to: On Everything And Nothing In Particular: Changing shape size in Microsoft Visio (view on Google Sidewiki)

Thursday, March 25, 2010

Access 2010 and Web Databases on Sharepoint 2010

It's an issue that to play with Sharepoint 2010, we need a 64bit Quad Core machine with minimum 4Gb of RAM. Sure, current technology and costs are low enough that this will not be an issue for a production server, but for a play/developer machine we just want to use any old thing near us.

In a rapidly evolving featureset though, the people at Microsoft are mapping Access functionality to Sharepoint data and Sharepoint workflows. A lot of current Office gurus who are not Sharepoint will groan and say "Who asked them for this?" but this is for me a great idea - Access is a great desktop tool and not a bad LAN networked tool. But to bring some of the familiarity of Access to Sharepoint is a win-win for both Access and Sharepoint camps.

Time will Tell.

in reference to: iGoogle (view on Google Sidewiki)

Wednesday, March 24, 2010

Rich HTML content in Microsoft Access Memo fields

I remember years ago, I took a Microsoft VB6 sample and constructed an Access 97 version of HTML Text email using the Web Browser control. I just had a look at it here:

By Gee, that was 2000, it's now 2010 - 10 years ago!

Anyway, Stephan Lebans added more zest to the project and in his HTML editor webpage, he has an example/demo file that pops up a form where you can edit rich text and most importantly embed a graphic.

The new Access 2007 and 2010 Rich Text formatted memo fields and Text Control can't embed a graphic. Or I haven't figured out how.

My next issue is how to use that content and send it via SMTP - you see, if you embed a local file reference with file:///, that won't go - you need to turn it to a cid reference, then send the graphic as well.....

in reference to: HTMLEditor (view on Google Sidewiki)

Wednesday, March 17, 2010

Access Archon's Archives

Helen Feddema's been steadily amassing a whole bunch of samples for VBA coding in Access over the years. She writes for WAW and for herself.

in reference to: (view on Google Sidewiki)

One of several public Australian datasets

I've often wanted datasets for Excel and Access demoes and tutorials. Australian Govt agencies have put them online. There are Public Internet Locations and Toilets - gotta woke on that xml import to Excel though - it's constipated.

in reference to: (view on Google Sidewiki)

Microsoft Project 2010 Videos

Finally Project embraces the Office Fuent UI and the Excel team's given them bonus UI behaviours in the grid.

in reference to: (view on Google Sidewiki)

Saturday, March 6, 2010

Office 2010 Upgrade for free

Purchase a qualifying Office 2007 product (with or without a new PC) from an authorized reseller between March 5, 2010 and September 30, 2010. See the eligibility requirements for more information.

in reference to: (view on Google Sidewiki)

Saturday, February 20, 2010

New to .NET programming?

Although the Microsoft Visual Studio ultimate editions for enterprise and AZURE programming cost heaps, Microsoft offers starter freebie editions for schools and hobbyists. With those tools there are also supporting websites for new comers and learners. Here are several beginner portals.


I took my eye off the ball in terms of future and pending developments for Microsoft Office - Office 2010 and the automation language, VBA, and now it seems VBA is up to version 7.

Microsoft appears to have had a dilemma - Office, tracing its roots back to Word 1.0, Excel 4.0 (and even earlier) has always been COM based. The Microsoft nerd programming community embraced .NET framework ages ago and we're now looking at the pending Visual Studio 2010 and .NET framework 4.0 - that's 4 versions from when .NET started.

However, Office programs remain mired in COM based programming and retains VBA (VB6, the big brother bit the dust long ago). Over time, the Microsoft Office team (allegedly) have had to deal with mismanaged source code and specifications - features were incrementally tacked on to Office programs every year and a consolidated "big book of Office" is hard to come by.

Well, Office 2010 looks like another landmark. For the first time, there will be two mechanisms - the 32bit Office and the 64bit Office to take advantage of the bifurcation in Windows (started with Windows XP). I don't think the plumbing of Office really impacts the end user of Office - yes, there will be more capacity and so on, but fundamentally, if you type a few words into Word, Word should be transparently carry out the tasks, not confront you with what plumbing lies behind it.

So, Office 2o10 will have a 64 bit edition. Hold off those of you who grumble about the fact that "bug number 23748 has not been fixed and will it ever be fixed" - the aim of the game is to progress the plumbing since the plumbers are the ones who put it together.

Since Office 2010 will be available in 64 bit, it must now be obvious that VBA has to be 64 bit capable as well. Thus VBA7. And since they are tinkering with that, they must have added new features to VBA7 and tinkered with the incrementing Object Programming Model of eacg of the Office programs.

Office 2007 is impacting business with The Ribbon - that's the obvious aesthetic change. We're finally getting training assignments for Office for veteran users - Office since '95 has not had a visual change.

It does not dawn on many veteran users and IT Pros that the Office team has also implemented significant architectural change - the driving force was a new User Interface, new XML based file formats and an evolving stance on an integrated result document visual (better looking fonts, consolidated colours and styles, new shared charting and graphic objects engine).

So, Office 2007 has proven to be a landmark and a motivator for upgrading Office in the corporate offices. Looks like Office 2010 will up the ante - the UI may not change that much but there's going to be a lot of underlying plumbing changes. I wonder how many Excel macros will break? Get your free Office 2010 Beta (some priviledged users have the Release Candidate) to have an fore taste.

Oh, in case you haven't been playing, there is also the on-the-go options - Office Webapps and Office Mobility - but are they any relation to Office classic?

Monday, February 15, 2010

CHKDSK used to be simple, reliable and effective

When we were using MS-DOS and later FAT, FAT32 on Windows up to 9x. Then Microsoft invented NTFS and although the utility is called CHKDSK, it really isn't DOS CHKDSK, it is a utility to diagnose and repair NTFS structures. Since NTFS is a complex structure, CHKDSK complexity has increased a lot - I thought it was me who was having issues with my Windows XP post SP3 machine with CHKDSK - after a run to fix problems, the whole volume would be irretrievably corrupted.

Now there is a Microsoft Knowledge Base article detailing its failings - and for me, it's not just pre-SP3, it can also happen post SP3

in reference to: (view on Google Sidewiki)

Tuesday, February 9, 2010

More on the Excel Solver.

The Excel Solver Add-in is a mathematical tool supplied by The free version supplied with Excel is a one-time tool - to generate a bunch of scenarios, you can use VBA to supply the automation.

Obviously, you're advised to understand the fundamentals of the optimisation engine before you set it to work with real world figures.

in reference to: (view on Google Sidewiki)

Monday, February 8, 2010

The classic way of pivoting fields in Excel 2007

The Pivot Table user interface has changed (again) in Excel 2007. The Pivot Table interactive screen elements and Word’s Mail Merge workflow are not in any way irritating. What is irritating is that Microsoft feels they need to evolve the user interface when they progress the Office version.

For those who love the classic way of dragging and dropping fields from horizontal to vertical (and vice versa), it can be puzzling that Excel 2007 forces use of the new vertical Task Pane. The good news is that you can still switch on that drag and drop behaviour of fields on the worksheet.

pivot table ui

Thursday, January 28, 2010

Inserting symbols and special characters in Word

In a class, some participants asked about inserting temperature degree marks and check marks into their documents. I knew there would be an article on the web that would cover this, This is one by Shauna Kelley.