Sometimes you need to add a slight pause in your VBA code to make things work properly. Here’s a few ways to do just that.

Firstly, using the Timer function. This is what I use most of the time, it’s great if you need to wait a specific amount of time.

Sub Wait(ByVal Seconds As Single)
    Dim CurrentTimer As Variant
    CurrentTimer = Timer
    Do While Timer < CurrentTimer + Seconds
    Loop
End Sub

With this routine you can also pause for parts of a second by using

Wait 0.5

The most common method I find in code is just using a For loop with nothing in it. Change the value of the loop in order to vary the time of the pause.

Dim i as integer
For i = 1 to 1000
Next i

The problem is you don’t know exactly how long it will pause for, and it will vary from computer to computer. It’s not an exact science, but it often solves the problem, and hence why I see it around so much.

Another method is to use the Sleep function that is part of the Windows API

Declare Sub Sleep Lib “kernel32” Alias “Sleep” (ByVal dwMilliseconds As Long)

Sub Wait(ByVal Milliseconds as Long)
   Sleep Milliseconds
End Sub

This method is good as it’s accurate down to the millisecond.

And lastly a method that is a little different to those above as it doesn’t pause your code but instead triggers code to run at a particular time. With this method the rest of your code after the call will continue to run, or if it’s at the end of your routine, then control will pass back to Word while waiting for your code to run.

Application.OnTime Now + TimeValue(“00:00:05”), “NameOfMacroToRun

Just change the “NameOfMacroToRun” to (you guessed it) the name of the macro you want to trigger, and it will run in 5 seconds time. The cool trick you can do with this is use it to run code at a constant interval while Word is running just by getting the macro to call itself again. For example:

Sub CheckSettings
   ‘ Do whatever you need to do…
   If Application.UserInitials <> “cp” then Application.UserInitials = “cp”
   If Application.UserName <> “Mosmar” then Application.UserName = “Mosmar”
   ‘ Run again in 10 seconds time
   Application.OnTime Now + TimeValue(“00:00:10”), “CheckSettings”
End Sub

Whilst this is probably a bad example, you get the idea. Whilst Word has plenty of events you can hook into, there’s lots that don’t exist (like selection change within content controls). By using the above you can replicate the functionality of events and create your own in VBA. Maybe I’ll save that for another blog post.

There are other ways to achieve a pause in your VBA code, but they are generally variations of the above so I won’t cover those. That’s what the rest of the internet is for.

Ever needed to know what registry entries are required to register a COM DLL? Well I did recently.

Sometimes you get caught out with Visual Studio Setup Projects (well that’s if you’re still using VS 2010 – bring back Setup Projects in VS Microsoft!) copying referenced DLLs locally and registering them by default. This often happens with WorkSite or Office addins.

I had an issue the other day where I updated a setup project and removed a reference for a particular DLL that I was having to change to use late binding instead (long story!)

Once I removed the reference and the DLL was no longer part of the install, when the upgrade ran the removal of the previous version unregistered this particular DLL, which then broke another application as the DLL in the original location is no longer registered (as it was overwritten by the local copy in your installer)

Well to get around the problem I needed to re-register the DLL as part of the install process for the new version. After much experimentation I decided it was easiest to simply add the registry entries for the DLL to the new installer.

This is where I came across a handy util that is part of Visual Studio – regcap. With Regcap you can find out what registry values are added when you register a DLL.

Just run regcap with the /o command and the name of the reg file you want to create, plus the name of the DLL you want to capture, as per the example below (the names of the actual DLL have been changed to protect the innocent – actually more likely to protect the guilty!)

C:\Program Files\Microsoft Visual Studio 10.0\Common7\Tools\Deployment>regcap /o c:\SomeAppDLL.reg “C:\Program Files\SomeApp\SomeAppExtension.dll”

Interestingly I had problems running this application on Windows 7 for this particular DLL. I tried several suggestions like setting compatibility mode for regcap, running as admin etc, but nothing worked for me. To get around the issue I copied the DLL to another dev machine running Windows Server 2003, and regcap worked a treat. I then imported the reg file that was created in the installer for my app, and problem solved.

Just a quick tip to save some time debugging classes. Did you know that if you make changes to a class, the changes don’t take effect until you re-instantiate the class? That’s why you don’t see your changes taking effect, or your breakpoints being hit.

So, just re-run the AutoExec routine in the template you are editing (because that is usually where your class is initialised) and you should be in business.

If not, then possibly your class is not initialised in a separate routine, but is declared as a new instance of the class, like so:

Dim oMyClass As New MyClass

where MyClass is the name of your class. Just do a search through your code for the New keyword followed by the name of your class if you don’t know where it is.

If this is the case, then change the declaration to:

Dim oMyClass As MyClass

Then in your AutoExec (or other routine that is called before the class is required), add the following:

Set oMyClass = New MyClass

Now you can make changes to your class, rerun AutoExec (or the other macro that instantiates the class) and you can continue to debug. Saves restarting Word and having to setup all your breakpoints again.

In Word 2003, if you need to know if a document contains any VBA code/macros in it, you can use the following function.

Function ContainsCode(ByVal Doc As Document) As Boolean
ContainsCode = False
Dim comp As Object
For Each comp In Doc.VBProject.VBComponents
  If comp.CodeModule.CountOfLines > 0 Then
    ContainsCode = True
    Exit Function
  End If
  Next
End Function

To get the function to work correctly, you will need to change a security setting and enable the “Trust access to the VBA project object model” setting.

Once you’ve enabled that setting you can do lots of fun stuff within VBA – but I’ll cover that in some other posts one day.

Then just call the function with the document you want to check, such as:

If ContainsCode(ActiveDocument) then MsgBox “This document contains macros”

Life is much easier in Word 2007 and 2010. You can just use the HasVBProject property of the document. It returns TRUE if there is code in the document, otherwise it’s FALSE.

If ActiveDocument.HasVBProject then MsgBox “This document contains macros”

Too easy! Microsoft was even nice enough to add it to not only the Document object model, but also to the Workbook and Presentation objects, so it is also available in Excel and PowerPoint. Nice one – it’s about time there were some more consistencies between the object models in Office.

I like it when my code becomes redundant for a good reason. Smile

I spend lots of time developing for different clients, and the problem is most clients have their own drive mappings pointing to specific network locations. In a lot of cases, I need to replicate these drive mappings as it’s usually where they store their Workgroup templates for Word, Excel and PowerPoint.

So instead of setting up all these drive mappings that point to network locations, I use the old SUBST command from a command prompt  to create a “fake” drive mapping that points to a local directory.

Generally I setup a single “Clients” directory on my C: drive, and then create a subdirectory for each client in there.

Then, I use the subst command to map the drive:

subst t: “C:\Clients\Client 1”

Now you have a new T: drive mapping that you can use like any other drive mapping.

Once you’re finished with the mapping, you can remove it with the following command:

subst t: /d

If you want more info on using SUBST, just check out the Wikipedia article which contains some good additional information.

So who says men can’t fake it? They can now! Winking smile

Now all we need is a utility to change all of the Word File Locations and to set these drive mappings, then we could easily switch between different Word environments for clients or testing.

Well as it turns out, I’ve already written one (Ah, nothing like a bit of self promotion) If you’re interested in trying it out, let me know – it’s very handy if you’re a Word coder, developer or tester, and best of all it’s free. I’ll get around to putting it on the site one day!

In the meantime, here’s a screenshot of the latest build of WordConfig…

 

Do you have lots of custom toolbars in Word 2003 and can’t work out where they are coming from? I’ve had this problem so many times, especially when preparing lots of templates to move to 2010.

Well, turns out it’s really simple – just run the following command in the immediate window in the VBA Editor (obviously change toolbarname with the name of the toolbar your after):

? Commandbars(“Toolbarname”).Context

and then you’ll get the full path to file that contains the toolbar. And yes, include the ‘?’ at the start – it means “print” so it will return the value onscreen.

One of my fave features in Word 2010 is the new custom undo records. It allows you to group your changes via code into blocks that can be undone in a single go – plus you can name the records.

Take the following sub:

Sub InsertSomeText()
Selection.TypeText “This is a test”
Selection.TypeParagraph
Selection.TypeText “This is some more text”
End Sub

If you ran this sub, and then went to the undo option, you’d see the 3 entries for each line of code that changed the document.

 

Now, here’s the same sub using a custom undo record.

Sub InsertSomeText()
Dim myUndoRecord As UndoRecord
Set myUndoRecord = Application.UndoRecord
myUndoRecord.StartCustomRecord (“My text insert to undo”)
Selection.TypeText “This is a test”
Selection.TypeParagraph
Selection.TypeText “This is some more text”
myUndoRecord.EndCustomRecord
End Sub

And after running that code, here’s what the undo options look like.

 

Nice!

You can also do nested undo records if you like, not that I’ve had a real need for them yet. But I may do very soon as I’m just about to do some work where I need to audit changes and have multiple levels of rolling back. Fingers crossed I’ll be able to do it all with nested undo records.

Maybe I should dig out my old Weezer CDs and listen to “Undone – The Sweater Song” when I’m working on that code! Ah, what a great song. I won’t get much coding done with Weezer belting out…

Have you ever tried to get the edit time from the IManDocument or NRTDocument objects within Autonomy WorkSite?

There’s the AccessTime property which returns the date plus time, but the EditDate property is just the date, and the time part is missing (set to 12:00am). So how do you get the date and time?

Turns out you need to use the GetAttributeValueByID method instead, which exposes both the EditDate and EditTime values, so you can just add them together to get the full DateTime value.

wsDocument.GetAttributeValueByID(imProfileAttributeID.imProfileEditTime) + wsDocument.GetAttributeValueByID(imProfileAttributeID.imProfileEditDate)

It’s easy when you know how. Just annoying that they can’t make it easier by exposing a single property directly on the object. Maybe in WorkSite 9.0 perhaps? ;-)

Usually the cause of documents being slow to open in Word is when the attached template is missing – especially when the template is located on a network. Turns out with Word 2010 there can be another reason.

We had a client where some documents were taking between 25 and 30 seconds to open, but only for some users. Word displayed Validating. Press Esc to Cancel in the status bar, but pressing Escape didn’t help.

A bit of research about what the validation process does led me to this article http://blogs.technet.com/b/office2010/archive/2009/12/16/office-2010-file-validation.aspx.

I then found that disabling the validation process by setting the following registry value made the documents open instantly.

[HKEY_CURRENT_USER\Software\Microsoft\Office\14.0\Word\Security\FileValidation]
“EnableOnLoad”=dword:00000000

Interestingly the offending documents appear in the list contained in the HKEY_CURRENT_USER\Software\Microsoft\Office\14.0\Word\Security\FileValidation\FailedFiles key, so there is something up with them, but we won’t go into that.

Now the other point the article made was that the process only runs on untrusted documents. This turned out to be the key. I re-enabled validation, and added the path to where the attached template was located as a trusted location, and voila, the document opened quickly.

In the end the problem turned out to be a group of users who weren’t getting their Workgroup Template path set correctly, so therefore the network templates were not being trusted.

© Copyright - Mosmar