Pages

Monday, July 28, 2014

Visualising Data

It's really hard to visualise data on-the-spot when it's not data that I live with.

Wednesday, April 30, 2014

Happy 50th Birthday, BASIC

I work almost daily with Microsoft Visual Basic for Applications - the language built into Microsoft Access, Excel, Word, Powerpoint, Project. It could stand improvements in elegance and modernity - that's what Visual Basic.NET was supposed to be. Except that Microsoft Office is written in COM architecture, not .NET - so VB.NET doesn't look like it's going to be embedded into Office-as-we-know-it.

But that's an aside. VBA itself is a descendant of the Microsoft's earliest BASIC, which was patterned after Dartmouth BASIC



Wednesday, March 5, 2014

Getting database data from Microsoft Excel

A friend asked me whether Excel could reach out to SQL Server data and grab data. I haven’t checked out this facility for a long time, so whipped up Excel 2013 and gave it a whirl.

Firstly, in Excel 2007, 2010 and 2013 the external data commands have been consolidated into the External Data tab of the Excel Ribbon. That’s good and easy to find.

Secondly, Microsoft Query is still alive and well for ODBC and local database connections. Except that no cosmetic work has been done on it – it looks really ugly on Windows 8 and the File Open / Save dialogs are even older, circa Windows 3.1 Despite this, it is still the nexus that connects Excel to these data sources.

Thirdly, with the new generation Excels, data grabbed from databases is dumped into Excel Tables – which is good – one of the excellent features in new generation Excel versions is the Excel Table.

Excel External Data Sources

As you can see, Excel continues to expand it’s ability to retrieve data from many kinds of data sources.

For this discussion, we are interested in classic tabular data sources – Microsoft Access database, SQL Server database, ODBC databases.

Notice Microsoft Query is an option. This will launch Microsoft Query.

Workbook Connection and data in Excel cells

After you have defined your request, you will be able to save a separate .dqry file or embed the request directly into the current Excel file. Your Excel file will have a Workbook Connection

Defining your Microsoft Query request

Microsoft Query has a simple user interface to

  • choose which database table or view/query
  • choose which columns you want
  • state your criteria to filter the data (parameterised stored procedures are not supported)
  • choose the sort order

You can use wizard driven screens or non modal user interface – the user interface crudely looks like Microsoft Access’s Query Design screen

Wizard dialogs

Microsoft Query Design Screen

More Advanced Data Retrieval

If you need to perform more complex data retrievals and coupling with parameter driven server-side Stored Procedures / Queries, you can use the VBA language in combination with Data Access Objects or ActiveX Data Objects to establish the request programmatically and form a Recordset which can then place the data into Excel cells. See KB 185125

Wednesday, December 25, 2013

Making formulae easier to follow in Excel

In the old days, we would write really long formulae with nested functions. Pre Excel 5.0 and in other products, we did not have the niceties of VBA as a supplementary classic programming language so we used to torture ourselves.

Fast forward to 2013 and Excel generations 2007 and later have niceties in display and useage that reduce the pain of long formulae. Courtesy of +Bill Jelen’s discussions on social media, we know that we can use:
  • you can use spaces in the formula. Instead of typing A1+A2, you can type A1 + A2 chunking out the formula and improving readability. You can’t put a leading space before the first = sign. You can’t put a space between the function name and the bracket e.g. SUM( is ok, SUM ( is not
  • you can use Alt+Enter to insert line breaks in the formula bar. Remember that in Excel 2007 onwards, you can pull down the floor of the formula bar, increasing the number of lines Excel will display. This allows more classic programming language display.
  • you can use the Name Box when you build the formula in the Function Builder. We know that. However, did you know that you can use the Function Builder repeatedly in a formula with nested functions?

Tuesday, December 17, 2013

Screen Capture in the Windows 8

There are several options to screen capture classic desktop (programs):

  • PrtScr to save to clipboard
  • the Windows Snipping Tool
  • Microsoft Office 2010 (and later) programs have an Ribbon Command – Insert > Screen Shot > Screen Clipping
  • Greenshot – a very nifty free utility that can capture, save to file automatically, upload to various photo gallery sites automatically
  • the Windows Problem Steps Recorder (records a screenshot every keystroke sequence or mouse action you carry out until you stop.
  • and a host of third party free and paid utilities

However, the Windows 8 Apps Tiles Layer breaks your workflow – If you use one of the utilities above, after the screenshot, Windows returns to the classic Desktop. If you return to the Tiles layer, some Tile Apps lose state.

Use Windows logo key + PrtScr and the screenshot will save to My Pictures/Screenshots as a PNG file automatically, without switching to the Classic Desktop.

Friday, December 13, 2013

Free Intro to VBA Excel

I often look for reference material for people intending to pick up Visual Basic for Applications (VBA) and program Microsoft Excel. Just today, encountered this compilation by Hooman Askari-Nasab

Enjoy!

Tuesday, November 12, 2013

HTML & CSS Resources

I sometimes still get involved with beginners to the web. Yes, nearly everyone knows how to Facebook, buy from eBay or Google for this and that but this is with the consumer hat on – fewer people have a basic knowledge of the atomic elements that make up a webpage. Here are some leads.
W3Schools has been there for a long time. It is still well presented reference and there are some areas where you can edit some HTML interactively and see the result.
Shay Howe’s Beginner’s Guide and Advanced Guide covers significant points.
Learn CSS Layout is a deconstructive tutorial.
jQuery from the source.
Adobe Dreamweaver reference and Help center
Scan your webpage and produce several screenshots with different browsers using BrowserStack

Microsoft Office on the Mac

Ok, Ok, I’m pure Microsoft Windows user. Been that since I Windows was born. I am forced to use a Mac very rarely and haven’t hit the comfort zone in this environment. But participants at classes sometimes ask

But where is the Ctrl key on the Mac?
https://exceljet.net/keyboard-shortcuts

How do I Right Mouse Click?
http://www.wikihow.com/Right-Click-on-a-Macbook

Where are the function keys?
https://exceljet.net/blog/how-to-use-mac-function-keys-with-excel

Thursday, October 31, 2013

Working in the Outline View in Microsoft Word

It is sad that Microsoft has sought to de-emphasise Outline View in Microsoft Word 2013 – it may be that they are optimising the user interface of these programs for Windows 8 touch tablets (Surface, Surface Pro and so on).

For putting structured thoughts to paper (think academic Thesis, comprehensive proposals and arguments), outlining is a very powerful tool and closely related to Buzan Mind Maps.

Since you are thinking out, you don’t want paper layout, fonts, or how it appears (presentation with diagrams and so on) to get in the way, you just want to write and re-organise.

Switching Views

Microsoft Knowledgebase 290938 has the following recommendations:

  • Alt+Ctrl+P for Print Layout View
  • Alt+Ctrl+O for Outline View
  • Alt+Ctrl+N for Draft View

ShortcutWorld has a visually pleasant list of keystrokes.

Whilst in Outline View

  • Ctrl+Tab to insert a Tab Character when in Outline View (since the Tab and Shift+Tab combination is busy)
  • Numeric keypad Slash (/) to toggle display of character formatting

Dummies.com has a short and sweet list of Keyboard Shortcuts by Dan Gookin.
Tip: Ctrl+Shift+N to force the current paragraph to Normal Style

Don’t forget to visit Word MVP FAQ for some experienced insight tips.