Category Archives: Tips

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.

Tips Visual Basic For Applications (VBA)

Creating digital signatures in Office products

Ever seen this screen?

Irritating, isn’t it?  Wouldn’t it be great if you could somehow get rid of this for your own projects so that they’d just open up straight away.  Actually it’d be even better if you could really ramp up your Macro Security levels and yet were still able to open anything created by you which contains them.

Well, you’ll be glad to know you can, and there’s nothing to download.  You should already have the software on your computer – it’s called SelfCert.exe and can be found in your office installation folder (usually C:\Program Files\Microsoft Office\Office (VERSION NO HERE)…

So, how does it work?

SelfCert lets you create your own digital signature which you can then attach to any documents you are working on and, once approved will allow you to open any document it is attached to without having to mess about enabling macros.

Double click on ‘SelfCert.exe and you’ll see the following:-

Pop your name, your companies name or whatever you like into the blank space and click OK.  And that’s it – you’ve just created your digital certificate.  Now I’ll show you how to attach this certificate to your projects.  In the VBA IDE of your Office product, choose ‘Tools>Digital Signature…’

And from here click the ‘Choose…’ button to select the signature you just created:-

Highlight the certificate you want and click OK and you’re done!  Next thing to do is close your Word/Excel/Whatever project down and save it if prompted and you’re away..  The next time you open this project you’ll get the following dialog:-

If you’re happy that you can always trust your own Macros (and why wouldn’t you?), click the check box and then choose ‘Enable Macros’.  Once you’ve done this, this is the last time you’ll be prompted to do this for this document or any others that you assign your digital signature to.  Simples!

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

Using “Is Not Intersect” in VBA code

I’ve been writing some code to capture the event when a particular cell is changed in one of my Excel Worksheets and, using the power of the interpipes found a simple solution to do this.  It looks something like this in code:-

Private Sub Worksheet_Change(ByVal Target As Range)
	If Not Intersect(Target, Range("A1:A10")) Is Nothing Then
		MsgBox "Hello"
	End If
End Sub

Whilst this works perfectly and does what I want it to do I didn’t understand why I should use ‘If Not Intersect….Is Nothing’ when it would be simpler to use ‘If Intersect(Target, Range(“A1:A10”) > 0’.

I soon found out when I changed something outside of the target range (ie A1:A10) and Excel threw an error at me.  Wondering why I had a bit of a browse on the internet and discovered the reason.  The Intersect method returns a range object, but if there is no range found it will simply return an answer of nothing, which causes the error.  Using this code ensures that, if nothing is returned, the IF statement can just end with no errors returned to Excel.  Nice.

It’s probably not the most interesting post, but I thought I’d document it in case you or I ever come across it again.  A full (and much more detailed) examination of the Intersect method in VBA can be found here.

Tips Visual Basic For Applications (VBA)

VBA Quick Tip: Looping through the items in a ListView control

If you’re wanting to loop through the items in a ListView control (for example, to do something for each item that exists in the control) this is how you can do it.  It really bugged me today as I couldn’t remember.

For yourVariable = 1 to yourListView.ListItems.Count