Category Archives: Visual Basic For Applications (VBA)

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.
 

Outlook Visual Basic For Applications (VBA)

Outlook: VBA code to display a random email from your inbox

Got loads of emails you need to action but don’t know where to start?  Run this outlook vba code or attach it to a menu item/button and it will display a randomly chosen email from your inbox.  I find this quite useful when I’m looking at the endless list of emails I don’t want to deal with – I just run the routine and make sure I deal with whatever email appears thrown at me.  It makes working through my email list more fun, too!


Sub ShowRandomEmails()

Dim myOLApp
Dim myNameSpace
Dim myFolder
Dim myItem

Set myOLApp = CreateObject("Outlook.Application")
Set myNameSpace = myOLApp.GetNamespace("MAPI")

Set myFolder = myNameSpace.GetDefaultFolder(olFolderInbox)

Dim intRandom
intRandom = Int(Rnd() * myFolder.Items.Count) + 1

Set myItem = myFolder.Items(intRandom)
myItem.Display

End Sub