View Single Post
 
Old 06-09-2012, 09:13 AM
Colin Legg's Avatar
Colin Legg Colin Legg is offline Windows 7 32bit Office 2010 32bit
Expert
 
Join Date: Jan 2011
Location: UK
Posts: 369
Colin Legg will become famous soon enough
Default

Your code has a lot of problems. You are using late binding to automate Word (ie. You do not have the MS Word library referenced) which means that Excel does not know what all the Word enumerated constants in your code are.

Because you have not enforced variable declaration, the VBA compiler scans your code and creates Variant variables for anything in your code which it does not recognise. So, wdStartOfRangeRowNumber is implicitly a Variant with a value of Empty.

To fix your code you should follow best coding practices and put an Option Explicit statement at the top of your code module (you should always do this). Then go to Debug > Compile to identify your undeclared variables such as oMatch1. You should declare these variables as appropriate types. As you repeat this process, you will also get compile errors for all those word enumerated constants which are unrecognised. For these you need to replace them with the numbers they represent. For example, wdStartOfRangeRowNumber should be replaced by 13. Since 13 is a pretty meaningless number, what I do is declare constants for them. eg
Code:
Const lngSTART_OF_RANGE_ROW_NUMBER As Long = 13
Then you can use this constant in your code. To get the correct values you can look them up in the MS Word VBA Object Browser.


The last point I'll make for now is a problem with the way you have declared some of your variables. Take this example:
Code:
Dim oDoc, oLetter As Object
In VBA, if you declare your variables like this, oDoc will be a Variant and not an Object. If you want to declare them both on the same line then you need to use:
Code:
Dim oDoc As Object, oLetter As Object
Here's another one:
Code:
Dim sPosition, sDelivery As String
In this case, sPosition will be a Variant. To declare them both as String types use:
Code:
Dim sPosition As String, sDelivery As String


I think that's enough for you to work on for now. Once you've fixed those issues we can then address the unqualified Word references in your code.
__________________
Colin

RAD Excel Blog
Reply With Quote