Why CreateObject("vbscript.regexp") doesn't work?

[Joe User]

I get a runtime error ("object variable not set") pointing to the assignment
statement when I do the following:

Dim v As Object
v = CreateObject("vbscript.regexp")

I have select Microsoft VBScript Regular Expressions 5.5 (also tried 1.0)
under Tools > References.

But nothing is added under VBAProject \ References in the Project pane, as
there is when I select atpvbaen.xls for example.

Is that indicative of the root cause of the problem, perhaps a missing file?

What is the name of the file that contains the Microsoft VBScript Regular
Expressions 5.5 library?

 

[Chip Pearson]

If you have a reference set to the Regular Expressions library, you
don't need to use CreateObject. Just use the RegEx members as if they
were native VBA code. For example,

Dim RegEx As RegExp

You can also prefix the object name with the library name. I usually
do this for clarity and self-documentation but it usually isn't
necessary:

Dim RegEx As VBScript_RegExp_55.RegExp

This won't show up in the Project window because you are referencing
an COM DLL, not a workbook or XLA add-in. Only workbooks (and xla
add-ins are just workbooks) show up in the Project window.

You don't need to know the file name because you don't open the file.
The file is opened by VBA behind the scenes when necessary. If you
really want to know the file, enter the following in the Immediate
window and press ENTER:

?ThisWorkbook.VBProject.References("VBScript_RegExp_55").FullPath

This will give you the file name, but there isn't anything you can do
with it.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com

 

[Joe User]

If you have a reference set to the Regular Expressions
library, you don't need to use CreateObject.

Thanks. Yes, I made two mistakes: (a) misread instructions that said
__not__ to reference the MS VBSscript RE lib (I overlooked the word "not");
and (b) writing the VB assignment incorrectly. I should have written:

Dim v As Object
Set v = CreateObject("vbscript.regexp")

You don't need to know the file name because you don't open the file.

Not if it exists. But I __might__ need someone to tell me the file name if
its non-existence were the root cause of my problem. I would want to search
to see if the file had been moved or if the file name had been modified in a
discernible way, for example by mangling the extension.

(The latter is a common practice of mine when I am triaging problems or
experimenting. I might have forgotten to undo it.)

If you really want to know the file, enter the following in the Immediate
window and press ENTER:
?ThisWorkbook.VBProject.References("VBScript_RegExp_55").FullPath

That seems to work only if I select the reference MS VBScript Regular
Expressions 5.5.

Not too useful if that is failing because the file does not exist. Klunk!


But that does seem to confirm that setting the reference worked. (Well, by
"worked", I mean: changed the environment.)

Can someone explain why I don't see something added under VBAProject \
References in the Project Explorer pane, as there is when I select
atpvbaen.xls for example?

I must be misinterpreting the significance of the References "folder"
(object?) in the Project Explorer pane.


----- original message -----

 

[Chip Pearson]

Reference to COM objects that one sets from the References dialog
aren't shown in the Project Explorer. Only workbook-to-workbook
references are shown.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com

 

[Joe User]

Reference to COM objects that one sets from the References dialog
aren't shown in the Project Explorer. Only workbook-to-workbook
references are shown.

Thanks. I confess that I do not understand those terms. I conclude that I
really do not need to know; just something for me to accept. But if anyone
would like to take the time to explain this in more detail -- or point me to
document that does -- I'm all ears. (Well, eyes ;->.)

I am a computer and software architect, so I don't need (nor appreciate)
sugar-coating details. But I am not familiar with MSWin architecture and
terminology -- a deficiency that I would really like to correct.

Thanks again for the explanation above.


----- original message -----

 

[Ron Rosenfeld]

Dim v As Object
v = CreateObject("vbscript.regexp")

I have select Microsoft VBScript Regular Expressions 5.5 (also tried 1.0)
under Tools > References.

But nothing is added under VBAProject \ References in the Project pane, as
there is when I select atpvbaen.xls for example.

Is that indicative of the root cause of the problem, perhaps a missing file?

What is the name of the file that contains the Microsoft VBScript Regular
Expressions 5.5 library?

You must SET an object.

Dim v as Object
set v = createobject("vbscript.regexp").

If you select Tools/References VBScript Regular Expressions 5.5, then one
syntax would be:

Dim v as RegExp
Set v = New Regexp
....
....
....
'and maybe
Set v = Nothing
--ron