MID function no longer working in Word 2003 macro

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Calvin Crumrine - 09 Apr 2007 19:36 GMT

We have a number of macros we've developed over the years and some of
them have suddenly quit working on some machines. All our machines run
Windows XP and Office 2003, but we've narrowed the problem down to only
those machines which were 'built fresh' with Office 2003-the others
started with Office 2000 & were upgraded to 2003.

By comparing the machines on which it works to those on which it doesn't
it appears the problem is a missing reference to the Microsoft Common
Controls 2 but the problem appears with the Mid function. I find it hard
to believe that the Mid function comes from the Common Controls 2-it's
simply too basic a function.

I can believe that it's part of the move to .Net, which replaces the Mid
function with a .Substring method, but so far I haven't found a
Substring method/function/whatever in Word VBA.

Am I missing something?

Reply to this Message

Perry - 09 Apr 2007 20:38 GMT

The problem is not related to the Mid() function.

Y've got a broken reference in yr VBA project, you need to track down.
Once the correct reference is restored, the problem is solved.
In yr case, you already mentioned that a reference to the Common Controls
library.
(file name: MSCOMCTL.ocx)

In VBE (ALT + F11), check under menu: Tools | Reference whether
a library/activeX comp is reported missing. Uncheck the missing item.
or
Locate the MSCOMCTL.ocx file on yr system, and try to point yr VBA reference
to the location where the MSCOMCTL.ocx file is residing on your system.
(usually: %Windows%\System32)
After one of the two above options, save the template and do a
shutdown/restart of MS Word.

If this doesn't remedy, you have to find the differences in the machines
where the addins work and those were the addins fail to work, possibly
in collaboration with IT ops.

--
Krgrds,
Perry

System:
Vista/Office Ultimate
VS2005/VSTO2005 SE

> We have a number of macros we've developed over the years and some of them
> have suddenly quit working on some machines. All our machines run Windows
[quoted text clipped - 13 lines]
>
> Am I missing something?

Reply to this Message

Calvin Crumrine - 09 Apr 2007 22:20 GMT

1) I am IT ops.
2) The missing library is Common Controls 2.

I could understand it if the problem was with the DatePicker which, if I
remember correctly, is in Common Controls 2-but it just doesn't seem
reasonable that the MID function is. Could the missing reference be
screwing up functions that don't require it? IOW could the simple fact
of the reference being missing cause problems unrelated to anything
contained in the reference.

I have not yet gone back to see whether or not the affected macros
contain a DatePicker-I know that some of them do, but haven't yet
checked this specific one.

I'm also wondering why Common Controls 2 is present when Office is
upgraded from 2000 to 2003 but not when 2003 is installed directly. (Of
course it's also possible that it was installed when the macros were
written, but if so nobody documented it.)

Thanks.

> The problem is not related to the Mid() function.
>
[quoted text clipped - 35 lines]
>>
>> Am I missing something?

Reply to this Message

Perry - 09 Apr 2007 22:55 GMT

> 1) I am IT ops.

If y're from IT ops, you could also look for conflicts in SMS mngmt console
(the conflicts table regarding system installation files on the target
machines)

> I could understand it if the problem was with the DatePicker which, if I
> remember correctly, is in Common Controls 2-but it just doesn't seem
> reasonable that the MID function is.

Believe me, it isn't related to the MID() function.
The broken reference to the MSCOMCTL.ocx file only causes the VBA compiler
to stop at this part of the code (the Mid() function).

> I have not yet gone back to see whether or not the affected macros contain
> a DatePicker-I know that some of them do, but haven't yet checked this
> specific one.

Even if the macros or userforms don't use the DatePicker, if the developer
didn't remove the reference to this ActiveX control, it's still present in
yr
VBA project.
The compiler will always look for it's resources in MSCOMCTL.ocx file
on the system; and if it can't find it, it will throw in the compile error
and will force the
code to stop at what appears to be a Mid() function error but really is the
broken reference to
the MSCOMCTL.ocx file ...

Good luck.

--
Krgrds,
Perry

System:
Vista/Office Ultimate
VS2005/VSTO2005 SE

> 1) I am IT ops.
> 2) The missing library is Common Controls 2.
[quoted text clipped - 56 lines]
>>>
>>> Am I missing something?

Reply to this Message

Calvin Crumrine - 10 Apr 2007 00:19 GMT

Thanks for your help. Since some of our macros do use items from the
MSCOMCT2.ocx it looks like we need to install that on all our machines.
I could (and have) fixed this particular macro by removing the reference
to that ocx, but there are others that legitimately need it-so we've got
to have it installed. Surprised that the problem took this long to
surface but now I'm starting to think that it was an 'undocumented'
installation from when we had Office 2000 installed.

Thanks.

>> 1) I am IT ops.
> If y're from IT ops, you could also look for conflicts in SMS mngmt console
> (the conflicts table regarding system installation files on the target
> machines)

I'll try that, thanks.

>> I could understand it if the problem was with the DatePicker which, if
>> I remember correctly, is in Common Controls 2-but it just doesn't seem
[quoted text clipped - 3 lines]
> The broken reference to the MSCOMCTL.ocx file only causes the VBA compiler
> to stop at this part of the code (the Mid() function).

Except that the broken reference is to MSCOMCT2.ocx

>> I have not yet gone back to see whether or not the affected macros
>> contain a DatePicker-I know that some of them do, but haven't yet
[quoted text clipped - 4 lines]
> in yr
> VBA project.

I knew that. <Bop self on head>. I suppose I was thinking that if it
didn't use anything from that ocx it wouldn't check to see whether it
was present or not. But given the poor way I expressed myself I'm not
entirely certain just what I was thinking when I said that-because yes,
I do know that if the developer includes a reference then it doesn't
really matter whether or not he uses anything from it-the reference is
still in the project. Sorry for the distraction.

> The compiler will always look for it's resources in MSCOMCTL.ocx file
> on the system; and if it can't find it, it will throw in the compile
> error and will force the
> code to stop at what appears to be a Mid() function error but really is
> the broken reference to
> the MSCOMCTL.ocx file ...

So if I understand what you're saying, when a reference is missing the
compiler generates a random function error? Still doesn't make sense to
me but what can I say-that certainly fits the symptoms.

> Good luck.
>
[quoted text clipped - 59 lines]
>>>>
>>>> Am I missing something?

Reply to this Message