Excel User Group

User Initials in Excel

Daily Dose of Excel — Thu, 28 Aug 2008 21:27:39 GMT

In Excel, you can get the Username by using Application.UserName. The Username is what is entered on the General tab of Tools > Options. Inexplicably, you cannot get the user's initials, but you can in some other Office programs. I read a suggestion to automate Publisher and get the user initials via [...] Read More......(read more)

Using Excel & Excel Services with SQL Server Analysis Services 2008

Microsoft Excel — Thu, 28 Aug 2008 19:49:00 GMT

Today's author, Pej Javaheri, a Product Manager on the SharePoint team, talks about configuring Excel Services to work with the just-released SQL Server 2008. With the recent announcement of SharePoint Server 2007 supporting SQL Server 2008 , like you, I was excited to setup my Excel / Excel Services environment to take advantage of the great new capabilities available, and there are many. I encourage you to take a look at how the new SQL environment will benefit your SharePoint deployment, and...(read more)

Random Numbers Repeating

Daily Dose of Excel — Wed, 27 Aug 2008 16:21:38 GMT

I write some data to an Access database, after which I retrieve the Autonumber that Access generates. Prior to writing the data, it sits in a class and all of the classes sit in a collection. The collection needs a unique string for each entry, so I create one until I can the [...] Read More......(read more)

COM Shim Wizard Version 2.3.1 is released

VSTO & .NET & Excel — Wed, 27 Aug 2008 09:31:34 GMT

The version of the COM Shim Wizard that supports VS 2008 is finally available. The documentation seems to be the same as for the previously version, indicating that the update is only to make sure it works with VS 2008. In other words, it works with both VS 2005 and VS 2008. To download it You can use [...] Read More......(read more)

Twitter

Daily Dose of Excel — Mon, 25 Aug 2008 13:12:41 GMT

I signed up for Twitter a couple of months ago, made a few posts, and pretty much abandoned it. If you’re not familiar with it, Twitter is a micro-blog. You make posts of 140 characters or less. I don’t follow many people and I’m not sure what value this whole idea has. [...] Read More......(read more)

The Ultimate Steal

Daily Dose of Excel — Sat, 23 Aug 2008 19:44:25 GMT

Hi all Great tip: Thanks to Dave Peterson For the student (or families with students): http://www.microsoft.com/student/discounts/theultimatesteal-us/default.aspx Ron de Bruin http://www.rondebruin.nl/tips.htm Read More......(read more)

Add missing built-in commands to the QAT or Ribbon

Daily Dose of Excel — Sat, 23 Aug 2008 14:05:03 GMT

Hi all I add this page to my site last week with a few examples to add missing controls to the Quick Access Toolbar or Ribbon. http://www.rondebruin.nl/notinribbon.htm I am curious which missing built-in commands the readers of this blog have add to the QAT or Ribbon. Tip: John WalkenBach posted a add-in for the speech controls on his site this [...] Read More......(read more)

Deploying VSTO Add-In To Multiple Users On Windows XP

Nick Hodge — Fri, 22 Aug 2008 07:37:00 GMT

Matthew from Australia kindly dropped by to outline a solution to a tricky situation with VSTO deployment to multiple users on Windows XP.

I don't profess to know the issue, but it seems like it should get a wider audience, hence the post.

Here is the link to Matthew's solution.

Retirement Calculator

Daily Dose of Excel — Thu, 21 Aug 2008 21:36:11 GMT

Download 401kalculator.xls.zip Unprotect the sheet (no password) and unhide some columns to see the calculations. Read More......(read more)

Using Excel 2007 for Progress Tracking in the Classroom

Microsoft Excel — Thu, 21 Aug 2008 18:39:00 GMT

Today's author, Danny Khen, a Program Manager on the Excel team, discusses a solution he recently built using Excel. The Manual Spreadsheet Spreadsheets are the bread and butter of running the business world, where PCs are ubiquitous. The possibility, indeed even the need, to use spreadsheets in an environment where access to a computer is not taken for granted seems absurd to us, as business users and technology professionals. We do still print reports for some output scenarios, such as executive...(read more)

Counting Olympic Medals

Daily Dose of Excel — Mon, 18 Aug 2008 01:32:16 GMT

I saw this article in the LA Times about measuring Olympic Medals per capita. I like the idea, but I also think figuring in the GDP of the country makes sense. The US could throw a lot of money at an individual event and probably fair pretty well. I got the GDP via Wikipedia [...] Read More......(read more)

My Coding Technique

Daily Dose of Excel — Fri, 15 Aug 2008 21:11:57 GMT

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 [...] Read More......(read more)

My Coding Technique

Nick Hodge — Fri, 15 Aug 2008 07:01:31 GMT

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.

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)

Sub Demo()
Application.ScreenUpdating = False
Application.EnableEvents = False
ActiveWorkbook.Worksheets("Sheet1").Name = "Data"
ActiveWorkbook.Worksheets("Sheet1").Range("A1").Value = "Nick"
ActiveWorkbook.Worksheets("Sheet1").Range("B1").Value = "Hodge"
'Now you could risk this...
Range("C1").Value = Range("A1").Value & " " & Range("B1").Value
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub

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.

Sub Demo()
Dim wb As Workbook
Dim wks As Worksheet

Set wb = ActiveWorkbook
Set wks = wb.Worksheets("Sheet1")

With Application
    .ScreenUpdating = False
    .EnableEvents = False
    
    With wks
        .Name = "Data"
        .Range("A1").Value = "Nick"
        .Range("B1").Value = "Hodge"
        .Range("C1").Value = .Range("A1").Value & " " & .Range("B1").Value
    End With
    
    .ScreenUpdating = True
    .EnableEvents = True
End With

End Sub

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.

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)

Option Explicit

Sub Demo()
Dim wb As Workbook
Dim wks As Worksheet
Dim rng As Range

Set wb = ActiveWorkbook
Set wks = wb.Worksheets("Sheet1")
Set rng = wks.Range("A1")

With Application
    .ScreenUpdating = False
    .EnableEvents = False
    
wks.Name = "Data"
    
    With rng
        .Value = "Nick"
        .Offset(0, 1).Value = "Hodge"
        .Offset(0, 2).Value = .Value & " " & .Offset(0, 1).Value
        With .Font
            .Bold = True
            .Color = RGB(0, 0, 0)
        End With
        .Interior.Color = RGB(255, 0, 0)
    End With
    
    .ScreenUpdating = True
    .EnableEvents = True
End With

End Sub

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.

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.

Reading Excel Files from Linux

Microsoft Excel — Fri, 15 Aug 2008 05:40:00 GMT

Today's author, Chris Rae, a Program Manager on the Excel team, talks about using Perl code to read Excel's new file format. The OOXML Format As most readers will know, we spent a lot of time during the development of Office 2007 in creating and documenting a new XML-based file format (Office Open XML) to replace the much more complex binary formats the various Office applications have used for the last twenty years. A lot of time. Many of us dream in XML now. OOXML encompasses an XML file...(read more)

My Places on File Dialogs

Daily Dose of Excel — Thu, 14 Aug 2008 16:00:06 GMT

On the left side of the File Open dialog (and the File Save dialog), there is an area called My Places. My Recent Documents, Desktop, My Documents, My Computer, and My Network Places must be the default, because I’m sure I’ve never added or deleted anything from this list. Recently, Bob Phillips alerted me to [...] Read More......(read more)

ShortcutText on Menu Items

Daily Dose of Excel — Wed, 13 Aug 2008 16:21:49 GMT

CommandBarButtons have a ShortcutText property that you can use to list a command's shortcut next to its menu item. The Cut menu item has Ctrl+X listed next to it. In this example, I make a new menu with one item under it. OnKey is used to assign a keyboard shortcut and the ShortcutText property [...] Read More......(read more)

How to Create a Perpetual Yearly Calendar in Excel

Microsoft Excel — Fri, 08 Aug 2008 06:21:00 GMT

Today's author: Mark Gillis is an Excel writer. He was born October 3, 1952. Does his birthday fall on the same weekday as your birthday? Do you have nagging, unanswered questions floating around in the back of your mind like: "What day of the week does the date, January 23, 4589 fall on?", "What's the date of the eighth Thursday of 1922?", and "Is 9000 a leap year?". If you're like me, the answer is clearly no. Nevertheless, there's a remote chance that...(read more)

Change built-in groups in the Ribbon

Daily Dose of Excel — Thu, 07 Aug 2008 19:51:50 GMT

Hi all It is not possible to add to or remove controls from built-in groups in the Ribbon. For example, the Format Painter button from the Home tab’s Clipboard group cannot be removed from this group. But there is a way around this restriction. We can hide a built-in group and then duplicate it with RibbonX. And then we can [...] Read More......(read more)

Data Visualization? Sir, Yes Sir!

Daily Dose of Excel — Mon, 04 Aug 2008 21:46:52 GMT

Did you ever want to join the army but don’t like all the physical activity? And instead of shooting people you’d rather create charts and dashboards in Excel? If so, you should go to the Excel Dashboard and Visualization Bootcamp. This 3-day boot camp is designed for Excel users who need to more effectively [...] Read More......(read more)

Increment File Names

Daily Dose of Excel — Mon, 04 Aug 2008 02:46:21 GMT

I have to save a file that may have the same name as an existing file. If it does, I append a number to the end of it to make it unique. The problem is that the file will live in three different folders in its life; Working, Review, and Archive. I [...] Read More......(read more)

Building an Asset Tracking Application in Excel Services – Part 5 of 5

Microsoft Excel — Fri, 01 Aug 2008 20:29:00 GMT

Today's author, Dan Parish, concludes his discussion of building an Excel Services solution. In Part 4 , I walked through creating the web part pages for the solution. At this point, you should have a fully functional asset tracking application up and running. The last piece, that we'll build today, is a simple web part that will allow your admin to archive all of the information they have collected. Overview At this point we now have the entire application up and running. We can collect...(read more)

VS 2008, COM Add-ins and launch conditions

VSTO & .NET & Excel — Thu, 31 Jul 2008 09:49:29 GMT

I can create COM add-ins based on the Shared Add-in template in VS 2008 and they all work well on my developing computer. However, I got an issue when I tried to deploy them on configurations where only .NET Framework 2.0 is installed. In the New Project dialog we can select the targeting .NET Framework version, 2.0 [...] Read More......(read more)

The NEW Spreadsheet Page

Daily Dose of Excel — Thu, 31 Jul 2008 01:54:12 GMT

Finally, I redesigned The Spreadsheet Page. The official launch is Friday, but you can take a sneak peek if you're interested. Out with FrontPage, and in with Expression Engine. Man, that's some powerful software. The site is all database driven now, so it will be much easier to maintain and update. Feedback is welcome. If [...] Read More......(read more)

Multiple Add-ins

Daily Dose of Excel — Tue, 29 Jul 2008 21:08:43 GMT

I'm creating several add-ins that will all use the same top-level menu item. The add-ins will be installed as needed. So someone may have just one and someone else may have half a dozen. The right answer, I think, is to have one controlling add-in that handles the menus (and not much [...] Read More......(read more)

Filter by Selected Cell’s Value, Cell’s Color or Cell’s Font Color

Daily Dose of Excel — Tue, 29 Jul 2008 17:57:02 GMT

Hi all After creating and testing a lot of code to filter on normal and on Conditional Formatting colors I came up with a nice and easy solution I think. I made this page for Excel 2007 http://www.rondebruin.nl/colorfilter2007.htm Suggestions are welcome Ron de Bruin http://www.rondebruin.nl/tips.htm Read More......(read more)