Category Archives: Tips

Excel Functions and Formulas Hints and Tips Tips Visual Basic For Applications (VBA)

Excel tip: Display the name of the last user to save a file

A colleague came to me today with a request.  She wanted a cell on her worksheet to display the user name of the last person to save her Excel workbook, along with the time it was saved, and asked me if there was some kind of function in Excel that did this.  I told her unfortunately not, but that I could write a VBA routine that would update a particular cell whenever the file was saved.

This, she said, was fine, but she’d prefer that there was a function or some way of doing it which didn’t necessarily link to a hard-coded cell or worksheet in the file.

At first I thought about creating a UDF to do this, but after a while pondering I decided a more elegant way might be to create a Name in the workbook which would be updated every time the file was saved.

Here’s how to do it:-

First of all, create a name in your workbook called ‘lastupdated’.  In the ‘Refers to:’ section of the dialog, create a reference to a text string such as “Last updated by MRichardson on 15/11/2011”, then click ‘Add’ (Don’t forget the quotes in your text string so that Excel recognises it as such).  The completed dialog should look like this:-


Now, to test this, create a reference to your Name in any cell in the workbook by typing in ‘=lastupdated’.  Press enter and your text should appear in the cell.  Magic!

It’s worth pointing out at this stage that names don’t always have to refer to cell or range references.  They can also be used as in-workbook constants of a kind.  I often use this technique when I want to use often occuring values in formulas and functions (such as VAT, for example).

Right.  We’ve done that.  All we now need to do is update the value of the ‘lastupdated’ name whenever the workbook is saved.

Press Alt-F11 to get to the VBA IDE and double-click on the ‘ThisWorkbook’ module.  We want to update everytime the workbook is saved, so we’re going to add our code to the ‘BeforeSave’ event.

The code itself is really quite straightforward and only takes up one line.  Here it is:-

ThisWorkbook.Names("lastupdated").Value = "Last updated by " & Environ("USERNAME") & " on " & Format(Now, "DD/MM/YYYY")

The code updates the value of our Name with the text, concatenated with the username (which is provided by Environ(“USERNAME”) and the ‘Format’ function to display today’s date in the ‘DD/MM/YYYY’ format.

Your code should look like this in the IDE:-

And that’s it.  Compile the project, close the IDE and save your file.  The cell containing the reference to our name should update with the correct details and will every time somebody saves changes to it.  Whenever you or your users open the file they will always know who last modified it.

The beauty of using a Name is that you can put the reference to it in any cell in the workbook, so you don’t have to deal with hard-coded cells which might be overwritten.  If you need to change the cell the details are displayed in, just add the reference to ‘lastupdated’ to somewhere else in your workbook.  Simple!

Comments on this process, as always, are more than welcome.

Tips Visual Basic For Applications (VBA)

VBA: The StrConv function

In excel, there is a function named PROPER, which takes text as an argument and formats it so that the first character of each word in the text is capitalised, it works like this:-

=PROPER(“this is my example text”)

and the result is:-

Proper Function Excel

It’s a pretty useful function.  I wanted to carry out similar text formatting in a VBA routine I’d written in Access, but didn’t want to reference Excel and then use Application.WorksheetFunction to call it.  That’s where the incredibly useful StrConv function in VBA steps in.

StrConv looks like this:-

StrConv(string to convert, conversion type)

The conversion type argument is one of a selection of constants which carry out various text manipulations.  Here’s a list of them and what they do:-

vbUpperCase – converts the entire string to uppercase
vbLowerCase – converts the entire string to lowercase
vbProperCase – capitalises the first letter of every word in the text string

There are some others, but I’m not going to discuss them as I’ve never used them.

So..to convert our text string using VBA, it would look something like this:-

 

StrConv Example
It’s that easy – no need to over complicate things!

Tips Visual Basic For Applications (VBA)

Microsoft Listview Control – a funny little bug..


For some reason I can’t yet fathom, the Microsoft Listview control (version 6.0) occasionally really gets messed up on my computer and I can’t access the properties for it by double clicking or selecting ListViewCtrl Object>Properties from the drop-down menu.

This is, like,  well irritating, as the kids would say.

I did, however find a way around it so that the properties can be re-activated as it were – which means I don’t have to delete, and then re-create the listview control every time this happens.

The solution is straightforward:-

  1. Right click on the listview control in design mode
  2. Click on ListViewCtrl Object>Convert..
  3. From the options, choose ‘Convert To’ and make sure ListView Control, version 6.0 is selected.  The text at the bottom of the dialog will tell you that nothing will happen and that the control won’t be converted.
  4. Click OK and you should have regained control over your control!

There is, however one other thing you should do if you have this problem, and that is to ensure that, if your listview is populated using VBA you use the ListItems.Clear method to clear the contents of the control before populating them.  This might seem like an unusual statement to make, as there should be nothing if the control has not been populated, but there is another element to this bug which, for some unknown reason pre-populates the control with whatever the contents were before you made a change and saved your form.  This is not always the case but it’s certainly worth as a precaution and to ensure that the control acts properly on all users machines.
 

Excel Tips Visual Basic For Applications (VBA)

Excel VBA: The Worksheet_Change event and Application.EnableEvents

I was creating some code which automatically multiplied any numerical value in a range by 1,000 by using the Worksheet_Change event.  This is how the code looked:-

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range(“A1:A10”)) Is Nothing Then
Target = Target * 1000
End If
End Sub

Try entering this yourself and entering a value into cell A1 and you’ll find you get an ‘overflow’ error.  The reason behind this is that, each time the code multiplies the cell value by 1,000 it essentially ‘changes’ the cell and therefore the Worksheet_Change event fires again, and this continues until Excel throws a wobbly and can’t cope anymore.

So, how do we fix this?  The key is to change the Application.EnableEvents property.  If you change the property to false, you’re telling Excel not to allow any events to fire at all until you tell it otherwise.  Here’s how the code should look to prevent the error:-

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not Intersect(Target, Range(“A1:A10”)) Is Nothing Then
Target = Target * 1000
End If
Application.EnableEvents = True
End Sub

In the first line of the subroutine we tell excel to ignore any events that fire.  We then run the code we want (in this case change the cell contents), and then re-enable events firing if something in the specified range changes.  Easy enough, isn’t it?