Is there any regexp and hashtable management in Excel VBA?

[Patrice]

Hello,

I am a newbie in this newsgroup. The question is in the subject: I was
recently installed Excel XP (2002) on my job computer, since I needed to
manage regexps and hashtables. But I was surprised, not to see any help
about these two features (I tried typing "regexp" and "hash" in the index
field of the help window), which really disappointed me. Are there some
"hidden" functions dedicated to regexp and hashtables in the Excel VBA
language, or not? Maybe they are available in additional packages?

Thank you in advance for your answers,

P.C.

 

[Guest]

http://support.microsoft.com/default.aspx?scid=kb;en-us;818802&Product=vbb
HOW TO: Use Regular Expressions in Microsoft Visual Basic 6.0

Should work for VBA as well.

I can't say anything about hashtables except there is nothing built in.

 

[Patrice]

http://support.microsoft.com/default.aspx?scid=kb;en-us;818802&Product=vbb
HOW TO: Use Regular Expressions in Microsoft Visual Basic 6.0

Should work for VBA as well.

I can't say anything about hashtables except there is nothing built
in.

Mmmm... I tried a lot of things, but it seems evident that even Excel for XP
does not manage Regexps. I also tried to create a variable:

Dim X as Regexp

But the keyword "Regexp" is not available. That is too bad: I was forced to
use Perl in order to perform some lexical treatment (removing blang spaces,
formatting chains, and so on). THis was not convenient since I had to save
the text on the hard disk, perform the treatment with Perl, then import the
resulting text from the hard disk. Some things did not change since 1980...

 

[Ron Rosenfeld]

Mmmm... I tried a lot of things, but it seems evident that even Excel for XP
does not manage Regexps. I also tried to create a variable:

Dim X as Regexp

But the keyword "Regexp" is not available. That is too bad: I was forced to
use Perl in order to perform some lexical treatment (removing blang spaces,
formatting chains, and so on). THis was not convenient since I had to save
the text on the hard disk, perform the treatment with Perl, then import the
resulting text from the hard disk. Some things did not change since 1980...

You need to first set a reference:

One method -- on the VBA Top Menu Bar select Tools/References and select
Microsoft VBScript Regular Expressions 5.5




--ron

 

[P.C.]

You need to first set a reference:

One method -- on the VBA Top Menu Bar select Tools/References and select
Microsoft VBScript Regular Expressions 5.5

--ron- Masquer le texte des messages précédents -

- Afficher le texte des messages précédents -

Thank you, but once done, how can I have some help in introducing
regular expressions in my VBA scripts? Are there additional files to
install in order to have help on these? Why do Microsoft not include
regexp programming in native, rather than making things so
complicated? Or maybe it is done in the last release of Vista
Office....

Thank you once more for answering all of these questions.

 

[Ron Rosenfeld]

Thank you, but once done, how can I have some help in introducing
regular expressions in my VBA scripts? Are there additional files to
install in order to have help on these? Why do Microsoft not include
regexp programming in native, rather than making things so
complicated? Or maybe it is done in the last release of Vista
Office....

Thank you once more for answering all of these questions.

I don't know of any specific VB Help files.

Try this reference for information on the syntax and flavor, as implemented in
VB

http://msdn.microsoft.com/library/d...html/2380d458-3366-402b-996c-9363906a7353.asp

There are some differences from PERL. I recall that VB does not have
look-behind assertions. But there are a few other differences, also.

and this one for an example of its use.

http://support.microsoft.com/default.aspx?scid=kb;en-us;818802&Product=vbb

Searching the .excel. newsgroups for "Regular Expression" or "Regex" should
also provide examples.

Once you have set the Reference, you can also use the Object Browser in the VBA
Editor.
--ron

 

[Nick Hebb]

Patrice,

REGEX
======
The Regexp is very limited. If you want to use real regular
expressions, you're better off buying a 3rd party library. I thought I
was going to need one at one point, and the only one I found is this
one for $99 from Aivosto: http://www.aivosto.com/regexpr.html

HASH TABLES
============
The best way to create hash tables in Excel VBA is to set a reference
to the Microsoft Scripting Runtime library. It contains a Dictionary
object that works like a Perl hash structure. The help samples all use
strings, but you can also store objects in them.

Quick Tip 1: Word's object model also includes a Dictionary object, so
if you're doing any automation of Word from Excel make sure to fully
declare your variables like "Dim d As Scripting.Dictionary" instead of
just "Dim d As Dictionary".

Quick Tip 2: After installing and setting references to the libraries,
hit F2 to open the Object Browser. Select the regex or scripting
library from the drop down to see the available properties and
methods. Excel's VBA usually won't launch the Help file for a
referenced library when you hit F1, so this is often the fastest,
easiest way to find out what you want to know.

HTH,

Nicholas Hebb
BreezeTree Software
http://www.breezetree.com