Author: Nick Hodge

UK Excel User Conference

Posted on by Nick Hodge

A meeting of the above group will take place on Wednesday 1st and Thursday 2nd April 2009 in London. This FREE event has some fantastic speakers from the Excel community.

The agenda for the two days is outlined below and you can book for either or both days by emailing bookings@excelusergroup.org.

Microsoft will be providing the venue and the hospitality and we look forward to a great and informative couple of days. The agenda is below, but if you want the full version with session details and speaker bios, it can be downloaded here..

Venue:
Microsoft London (Cardinal Place)
100 Victoria Street
London SW1E 5JL
Tel: 0870 60 10 100

Agenda:

Wednesday 1st April 2009

Time Event Speaker
9:15am – 9:45am Registration & coffee  
9:45am – 10:00am Welcome Nick Hodge
10:00am – 10:45am Working smart with the Excel grid Simon Murphy
10:45am – 11:45am Intermediate functions Bob Phillips
11:45am – 12:15pm Coffee  
12:15pm – 1:15pm What’s in a name? Charles Williams
1:15pm – 2:00pm Lunch  
2:00pm – 2:45pm TBA Microsoft
2:45pm – 3:30pm Tips and tricks in charting Andy Pope
3:30pm – 4:00pm Coffee  
4:00pm – 5:00pm Pivot tables Roger Govier
5:00pm – 5:30pm Pre-submitted Q&A All
5:30pm – Late Dinner, drinks, etc All (Inc. Delegates)

Thursday 2nd April 2009

Time Event Speaker
8:30am – 9:00am Registration & coffee  
9:00am – 9:15am Welcome Nick Hodge
9:15am – 10:00am Data exchange Nick Hodge
10:00am – 11:00am VBA – It’s in everyone’s reach Simon Murphy
11:00am – 11:30am Coffee  
11:30am – 12:45pm Information – Visualising data Andy pope & Bob Phillips
12:45pm – 1:30pm Lunch  
1:30pm – 2:15pm Arrays and array functions Bob Phillips
2:15pm – 3:15pm Speeding up Excel Charles Williams
3:15pm – 3:45pm Tea & coffee  
3:45pm – 4:30pm Pre-submitted Q&A All
4:30pm Close  

My Coding Technique

Posted on by Nick Hodge

Dick has been kind enough to allow me to mirror posts from DDoE, but gets little back. Therefore as a guest poster I thought I would post some ramblings I made today on excel user group regarding my own particular coding techniques. (It also allows him the weekend off)

I have recently been doing most of my coding in Visual Studio, doing some ‘real’ work for a change, coding using VB.net, ASP.net and ADO.net.

The environment in VS2008 is much improved over the VBE in MS Office (VBA). For example, it auto-indents and, with the addition of Developer Express’s Coderush product it even adds helpful lines between these indents. In large code projects it becomes a real chore to keep pressing the tab key to indent, but it is pretty essential if you are going to be able to easily ‘read’ and debug your code. Below is an example of my code in VS2008.

VS2008

Another thing you also soon notice is the huge nature of the .NET framework meaning that if you don’t ‘type’ your variables (that is declare them strongly as a certain ‘type’) you get little or no help at all and that makes the whole scenario impossible. (Well not impossible, but improbable if you are not the sort of person who can memorise Pi to 120 places or something!).

This brings me to my point in VBA.

When I first started coding, as with most I suspect, you simply recorded the code with the macro recorder and then amended that to get rid of all the unnecessary defaults it records. You then move onto hand coding but a little like this. (…again I suspect)

 

Now, the above code will work (I know, why the application….. It’s just to prove a point), but two finger typing makes it very slow and painful to write and often, when using the objects, intellisense gives you no clues as to what properties, methods, or child objects are available to you. It also makes it very laborious to debug.

The Range(“C1?)… is also very dangerous as if you have multiple workbooks open, or your workbook has multiple worksheets, you may find that Range(“C1?)… does not refer to the C1 you think it does as your code may have made another workbook active. (Remember, you are just using ActiveWorkbook).

Now consider this code, which does the same.

First you will notice we declare two variables (wb and wks (could be anything really)) to ‘carry’ the two objects (the Workbook and the Worksheet respectively). In VBA, if you have an ‘Object’ variable you have to ‘Set’ it (unlike ‘value’ variables which can just be assigned e.g. myVal=0) and we do this by using the ActiveWorkbook (hopefully we are sure that the activeworkbook is the correct one at this point!) and then we ‘Set’ the Worksheet object variable by assigning the Sheet1 worksheet. (Note that I am using wb in front of this assignment. That’s because I know that wb refers to the correct workbook).

What I am sure of now is that whatever I do with wb or wks will always refer to those two objects. Whatever’s active, without activating them and without selecting. Your code will run quicker and there will be no screen flashing (hence I really have no need for the Application. ScreenUpdating, etc).

The second and third benefits of this approach are that I now only refer to Workbook.Worksheets(“Sheet1?) as wks and, while typing wks and the period to use properties and methods of the Worksheet, I am sure to get a ‘clue’ from the VBE’s intellisense, as below. This does not always happen when using full notation as VBA seems to lose track of the object it is using. By declaring it in a ‘strong typed’ way, it knows and guides you through.

Intellisense

The last construct that I use all the time is the With…End With block. This gives you a further shortcut when you intend to make a lot of changes to a single object, (I also use it to make changes to objects ‘around’ the referenced one, see Offset(…) below)

You’ll notice we’ve added a rng variable, that refers to a Range object and then we ‘set’ that to the range A1 on Sheet1 (using our wks variable that we know refers to that sheet in the ActiveWorkbook (wb)). We then use that rng object in a With…End With block to assign stuff to that object. (notice too I use an internal With…End With block to assign stuff to the Font object). You’ll also notice I moved the wks.Name line out of the block as it is no longer referring to the wks but the rng. Of course I could have incorporated the .Name into the rng block, by using

.Parent.Name=”Data”

As the Worksheet (wks), is the ‘Parent’ object of the Range (rng) object.

Maybe the image below will help to show the components parts.

Code explanation
 

That’s just a few pointers in what I do to make my code shorter, more readable, efficient and easier to debug. It also has the spin off in making the VBE tool work for you instead of against you.

Just my slant on things… Comments.

Inaugural UK Office User Group Event

Posted on by Nick Hodge

Dick has kindly given me permission to post on the DDoE about an upcoming event in the UK focussing on Office 2007.

It will take place at Microsoft’s Campus, Thames Valley Park, Reading, Berkshire, on Friday 27th April 2007 and is COMPLETELY FREE!

I will be one of the speakers, discussing the customisation of the new Office UI using RibbonX and VBA, but there will be some BI discussions, as well as presentations of other Office products, including Visio. (I call all these add-ins to Excel, but keep it quiet).

If you are interested, to help us with catering, please book you place with rich@gordonassociates.co.uk. Or, if you wish to look at the detail for the day, go to www.officeusergroup.co.uk

Thanks

Things I’ve learned about MVPs

Posted on by Nick Hodge

Just so that Dick, John, et al will know I was still compos mentis at the bar on Wednesday night at the MVP Summit in Seattle, where I promised to ‘dish the dirt’ on the individual MVPs…here’s what I learned, in no particular order (list is limited to the drunkards at the Rock Bottom Bar on Wednesday night)…

  • Debra Dalgleish – Has a wheat intolerance which resulted in the most revolting food being wheeled out for her at each meal break, she also has a wicked sense of humour (spelt correctly), which results in a drink being requested of the poster at the main dinner event at the Museum of Flight, which when delivery was attempted, thought it funny to run off into the crowd never to be seen again!
  • Ken Puls – is a rare breed…an interesting accountant, worryingly however, the only currency he appeared to have was Canadian Tire (purposely spelt incorrectly) dollars. Also his last name should surely have an ‘e’?
  • Bill Jelen – Who sounds like a TV presenter and acted like one too by recording endless podcasts with the MVPs. We have yet to know what for, but it gave us endless fun with comments from us like the sign off at the bar…”This is Bill Jelen, Rock Bottom Bar, Seattle”!
  • John Walkenbach – John was able to join us as the ‘find and replace’ of all his books from Excel 2003 to Excel 2007 did not take as long as expected, although he did seem incredibly annoyed with the Office ribbon group due to the many changes causing an actual re-write of his commandbar chapter. You should also be aware that he only has 15 years of life left.
  • Zack Barresse – As one of the new boys, Zack was very quite so not much to say here, although he was always present at the bar, so can’t be that bad, also as you will see later, it’s very difficult with me present to get a word in edgeways
  • Masaru Kaji (Colo) – Seems to have lost his coca-cola fan fetish, but still turns up with the best camera and takes hundreds of pictures, strangely, we never see them though
  • Tushar Mehta – Tushar is undoubtedly a great ‘thinker’, unlike most he waits during the sessions and then, when the Excel Dev team have announced the ‘latest and greatest’, Tushar wants it to spin, have flashing lights and play the national anthem. He’s our ‘thinking outside the box’ type of guy
  • Bob Phillips– Bob, in the style of most of us Brits managed to consume his fair share of falling down water, had a dry sense of humour, but you knew you were VERY late for a session if Bob was there before you!
  • Andrew Engwirda – Not your typical Australian. Is quiet, does not like Cricket (and therefore did not wind it into us Brits about the cricket and rugby) ad does not know the one rules of Aussie Rules Football. (No guns allowed). I put this down to him living in Japan for many years and he certainly knew the Japanese for cherry blossom, which I have now forgotten
  • John Peltier – Undoubtedly John originally hails from French Canada as his name should be pronounced pelteee-a, but as he is American it should be pronounced Pelteee-er? Didn’t speak to him all trip as he comes from Boston and I like tea!
  • Dick Kusleika – I’ve seen Dick WITHOUT a cap, strange thing is he has a fine head of hair! Well, except the line all around his head where the cap fits. We were worried when he arrived as he only had 3 hats but four days, but it was OK as he bought one in the Boeing shop…loves IHOP… Actually SEEMED interested in the rules (laws) of cricket! (Not sure he figured how a game could last 5 whole days (8 hours play each day) and still be a draw)!
  • Damon Longworth – Damon is like Zack, very quiet, but boy, give him a task to do and he organises it with military precision! Even managed to get us a free tour of Boeing… Not that we could pay for an employee to take us on a detailed tour of the WHOLE 777 line and a bit of the 767 one…it was awesome! Soon to be organising the ‘rumble down under’ MVP conference tour of Australia ;-)
  • Niek Otten – Speaks very good English, which helps as none of us speak Dutch! Niek has to be early for everything, he was leaving directly from the bar I think for a flight home on Thursday lunchtime! and no… we weren’t drinking that late!
  • Nick Hodge – Quietly spoken, very retiring and shy, but don’t diss the Queen! ……

    • All told we had a great time, there were others who attended, Bill Manville, Bob Umlas, Stephen Bullen, etc, etc, but they did not want their brains dulled by drink or were too busy staying sober for other pursuits ;-)

    It is one of the best things about our group that we can get together and talk about such diverse things and even vote on what Add-in should be next to be coupled to Excel (To date we have Add-ins such as Access, Word, PowerPoint, etc), it was decided at this summit that we should develop SQL server as the next one.

    Outputting Worksheet Comments

    Posted on by Nick Hodge

    All

    The code below will look at all comments in the activeworkbook and save them to a text file on the root drive (C:Test.txt).

    The comments will be one to a line, with the sheet and range address from whence they came, who added the comment and it’s text. Being in a text file should make it easy to import elsewhere if needed.

    Sub writeComments()
    Dim mycomment As Comment
    Dim mySht As Worksheet
    Open “C:Test.txt” For Output As #1
    For Each mySht In ActiveWorkbook.Worksheets
        For Each mycomment In ActiveWorkbook.Worksheets(mySht.Name).Comments
            Print #1, “From “ & mycomment.Parent.Parent.Name _
                & “!” & mycomment.Parent.Address _
                & ” comes the comment: “ _
                & mycomment.Text
        Next mycomment
    Next mySht
    Close #1
    End Sub

    Happy Holidays
    Nick Hodge
    Microsoft MVP – Excel
    www.nickhodge.co.uk

    Parameters in Excel external data queries

    Posted on by Nick Hodge

    Hi everyone, first time authoring here and looking to pass on one of the neat, but less intuitive aspects of data management in Excel.

    Often I find myself with data in an external database, such as Access and continuously editing the query there to get the data how I want it in Excel. With care, this can be done directly in Excel. (Using 2003, but earlier version will be similar).

    Open a workbook and on the active sheet in cells A1 enter Start Date and in B1 enter the date 01/01/2003. In A2 enter End Date and in B2 enter the date 02/02/2003.

    Start End Dates

    Take the menu options Data>Import External Data>New Database Query…

    External Data Menu

    You will fire from here a dialog asking for your selection of an external datasource. We have chosen ‘MS Access Database’.

    Data Source Dialog

    Navigate your way to your Access database and select the table or query you want from the list displayed and add the fields you require, as below. (Remember, if you have a parameter query in Access already, this will create an error if we try to use it in Excel. ‘Too few parameters, expected 1’) .

    Field Selection Dialog

    Move on three screens making no changes until you arrive at the final screen (below). Take the second option to ‘View data or edit query in Microsoft Query’. This will launch Microsoft Query. (For those familiar with Access, this looks very similar to the query designer).

    Finish Query

    From the image below you can see we have shown the ‘criteria grid’ by selecting View>Criteria from the MS Query menus.

    In our example we are going to take orders with a ship date between two dates, (01/01/2003 and 02/02/2003). To do this we enter the operator ‘Between’ followed by our first parameter. These are enclosed in square brackets and what is in here will, in certain circumstances, appear as the prompt in the input box, with the entry being the parameter. ‘Between [Enter the start date]’.

    The next part is the ‘And’ operator followed by our second parameter, completing the parameter thus:

    Between [Enter a start date] And [Enter an end date]

    Microsoft Query

    In MS Query select File>Return data to Microsoft Excel. You will be prompted for your two parameters. (start and end date), but you can ignore them. (Answer OK).

    You will now get the dialog below, asking for the positioning on the sheet.

    Sheet Position

    Click the ‘parameters…’ button to show the dialog below. You now have three choices.

    1. Prompt for the values. (You can enter any prompt here).
    2. Use the following value. (You can enter a static value).
    3. Get the value from the following cell (Our example).

    Remember to set how the value is obtained for all values and, if you want the data to update each time you change the value of the cell(s), then select the checkbox. (against each value again).

    Parameter Selection

    Click OK in the ‘Parameters’ dialog and select $A$4 as the cell for the start of the data, click ‘OK’ in the ‘import data’ dialog and your data should flow in filtered between the two dates supplied. Each time you change the dates, the query is refreshed with the new input.

    Query Result

    If you find you wish to change parameters or the way they action at a later date, this can be done in Excel via ‘Data>Import External Data>Parameters…’ or via the ‘External data’ toolbar. (Above).

    Hope you can use this and any comments welcome

    Nick Hodge
    MVP – Excel

    www.nickhodge.co.uk