Category Archives: Visual Basic For Applications (VBA)

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?

 

Access Tips Visual Basic For Applications (VBA)

Making Intellisense work in Access VBA

I’ve just had a little problem where I was programming with a ListView control in Access and, for some reason my Intellisense broke in VBA so I couldn’t get access to the handy dropdown of methods and properties that comes in so useful as you’re developing.  I tried changing settings in VBA to no avail, but eventually found a solution.

If you’re suffering similar problems try the following steps:-

Create a module-level variable with type as ListView, for example:-

Dim MyListView as ListView

Then, in the Form_Load event, add code to reference the new variable to your listview control, like this:-

Set MyListView = [your listview name here].Object

And from this point refer to your listview control using the variable name rather than the name of the actual control. All your intellisense will come back perfectly. I know it seems a long-winded way around fixing things but hey, it works!  I’ve not tried it with everything, but I would assume that the same method will work for any controls for whom Intellisense seems broken.

2toria Access Downloadable content Visual Basic For Applications (VBA)

A very basic chat program in MS-Access

I‘ve just come across this little plaything I developed a year or so ago and thought I’d share it with you guys.  It’s a very basic chat program that you can use with your colleagues if you all have access to a shared network.  If you all open the MDB file you can chat with each other.

The code uses your network log-in name to define who you are and saves the ‘chat’ to a table within the database.  I’ve left everything open so you can look at the code etc, although there isn’t a great deal really – I’m actually surprised in hindsight just how simple it really is!

You will need to clear ‘tblConversation’ each time you’re done with the application, otherwise it will show the whole previous conversation.

I might someday develop this further, but for now as I said just thought I’d share with you.  If you can think of any uses for it, or if you want to have a go at amending it yourself please do and let me know how you get on.

Access Visual Basic For Applications (VBA)

DAO vs ADO?

I’ve been programming with Access for a good ten years or so now and I’ve always used DAO when manipulating tables through VBA.  I seem to recall a while ago (and this could well be years) somebody talking about how ADO was the way to go and that DAO would soon become extinct and, at the time I thought I should learn it.  I never did.

Anyway, for some reason today the thought popped up in my mind as I was working on a project – should I replace my DAO code with ADO and start using it from now on?  I thought I’d ask Mr Internet what he thought.

Turns out that the majority of Access experts out there still recommend using DAO if you’re only working with mdb files, as DAO was designed for Jet databases.  As the bulk of my work is still with mdb’s that gives me no big reason to change over, unless I start querying SQL server or other ODBC-compliant databases, which I doubt I’ll be doing any time soon.

Thank you Mr Internet!!  I might still learn ADO at some point, but for the work I currently do, DAO it is…