Interpreting variables as code

[rci]

Hi all...

perhaps a bit of an unusual notion... but I'm sure I'm not the first...

is there a way to cause Excel to interpret a string variable as code?

I'm thinking of allowing users of an excel tool to write a text file that
may contain standard excel formula and/or VBA code (minus flow structures).

Possible? A better or different approach?

Thanks in advance...

Mike

 

[Frank Kabel]

Hi Mike
you do not want to go in the 'parsing/compiler' business - don't you

Though this can be done, this would be quite difficult to do and would
(IMHO) require a large amount of code. This is NOT a small utility

For what do you want this (which VBA function could be required for
your users) How should they enter the text file (you may add to your
requirement list some syntax checking while editing/writing)

 

[rci]

Hi Frank....

no, no... I am trying to *avoid* the parsing/compliler business... that's
the entire point of the exercise!

Imagine where AsCode() is a function to intrepret strings as code:

a = "nMyAddress = 142"
b = "msgbox nMyAddress"

AsCode(a)
AsCode(b)



if a and b are set by reading in lines from a text file, then the code will
be generated from the text file... (and if it crashes, then too bad


Mike


: Hi Mike
: you do not want to go in the 'parsing/compiler' business - don't you
:
: Though this can be done, this would be quite difficult to do and would
: (IMHO) require a large amount of code. This is NOT a small utility

: For what do you want this (which VBA function could be required for
: your users) How should they enter the text file (you may add to your
: requirement list some syntax checking while editing/writing)


: --
: Regards
: Frank Kabel
: Frankfurt, Germany

: :>
:> Hi all...
:>
:> perhaps a bit of an unusual notion... but I'm sure I'm not the
: first...
:>
:> is there a way to cause Excel to interpret a string variable as code?
:>
:> I'm thinking of allowing users of an excel tool to write a text file
: that
:> may contain standard excel formula and/or VBA code (minus flow
: structures).
:>
:> Possible? A better or different approach?
:>
:> Thanks in advance...
:>
:> Mike

 

[Frank Kabel]

Hi
I still don't see the benefit for your user. And YES you're in the
'parsing' business as you have to implement this function AsCode()
In total you need something like (as you wanted to mix VBA + worksheet
functions):
- Program to read text files (which the user creates in a text editor
without ANY syntax checking"
- A 'parsing' algorithmn (search Google for 'Yacc' to get an idea about
this)
- A kind of pre-compiler to convert your text into VBA
- somehow the result of this has to be put somethere. So you have to
add some output routines

So IMHO you're in the Parsing/Compiler business
So why don't write VBA code directly?

Maybe you can give an example what kind of functionality should be
accessible for your user. There're probably other solutions for this

 

[rci]

Hi Frank,

well, the point is that I don't want the user into my code, but I want them
to be able to define routines to be applied to data that my tool manages.

Thanks so much!

Mike


: Hi
: I still don't see the benefit for your user. And YES you're in the
: 'parsing' business as you have to implement this function AsCode()
: In total you need something like (as you wanted to mix VBA + worksheet
: functions):
: - Program to read text files (which the user creates in a text editor
: without ANY syntax checking"
: - A 'parsing' algorithmn (search Google for 'Yacc' to get an idea about
: this)
: - A kind of pre-compiler to convert your text into VBA
: - somehow the result of this has to be put somethere. So you have to
: add some output routines

: So IMHO you're in the Parsing/Compiler business
: So why don't write VBA code directly?

: Maybe you can give an example what kind of functionality should be
: accessible for your user. There're probably other solutions for this

: --
: Regards
: Frank Kabel
: Frankfurt, Germany

: :>
:> Hi Frank....
:>
:> no, no... I am trying to *avoid* the parsing/compliler business...
: that's
:> the entire point of the exercise!
:>
:> Imagine where AsCode() is a function to intrepret strings as code:
:>
:> a = "nMyAddress = 142"
:> b = "msgbox nMyAddress"
:>
:> AsCode(a)
:> AsCode(b)
:>
:>
:>
:> if a and b are set by reading in lines from a text file, then the
: code will
:> be generated from the text file... (and if it crashes, then too bad
:
:>
:>
:> Mike
:>
:>
:> : Hi Mike
:> : you do not want to go in the 'parsing/compiler' business - don't
: you
:> :
:> : Though this can be done, this would be quite difficult to do and
: would
:> : (IMHO) require a large amount of code. This is NOT a small utility
:>
:> : For what do you want this (which VBA function could be required for
:> : your users) How should they enter the text file (you may add to
: your
:> : requirement list some syntax checking while editing/writing)
:>
:>
:> : --
:> : Regards
:> : Frank Kabel
:> : Frankfurt, Germany
:>
:> : :> :>
:> :> Hi all...
:> :>
:> :> perhaps a bit of an unusual notion... but I'm sure I'm not the
:> : first...
:> :>
:> :> is there a way to cause Excel to interpret a string variable as
: code?
:> :>
:> :> I'm thinking of allowing users of an excel tool to write a text
: file
:> : that
:> :> may contain standard excel formula and/or VBA code (minus flow
:> : structures).
:> :>
:> :> Possible? A better or different approach?
:> :>
:> :> Thanks in advance...
:> :>
:> :> Mike
:>

 

[Frank Kabel]

Hi
not possible (IMHO) withou much coding

 

[Tushar Mehta]

Well, you could define positions in your code from where you will call
a user-defined subroutine, if it exists. For example, in the SIMULATE
software available from my web site, I allow the user to write VBA
routines that will be called at specific times during each iteration of
the simulation.

It does require that you test and verify the routines exist, and that
they are callable. Even then, you have to rely on the user code
executing correctly and efficiently.

Alternatively, you could make certain entry points in your code
available to the user. In this case, your code would act very much
like XL functions (or Windows APIs) and the user code would call the
appropriate function as required.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[onedaywhen]

Sounds like COM events to me. Put your code in a compiled component
e.g. VB6 ActiveX DLL. Expose classes with events, passing parameters
as necessary. The client can declare Withevents objects based on your
classes and write code in local event handlers, responding to and
possibly changing the ByRef parameters etc etc.

 

[rci]

Whoa... one more time, in english, please

I'm sure that's a great idea... well, it *sounds* great. Wish I knew what
all that meant...

Obviously I have a bit of study ahead of me... any web sites or good primers
for this catetgory of programming? Is there a good term to use in searching
for information on the subject(s)?

My code is very much integrated into the excel envirmnment (on a userform
utilizing a wide array of objects/components as well as excel functions).
In such a case, is it still reasonable to move it out of the excel
environment and into a VB6 library?

All the best,

Mike


: Sounds like COM events to me. Put your code in a compiled component
: e.g. VB6 ActiveX DLL. Expose classes with events, passing parameters
: as necessary. The client can declare Withevents objects based on your
: classes and write code in local event handlers, responding to and
: possibly changing the ByRef parameters etc etc.

: --


:> I don't want the user into my code, but I want them
:> to be able to define routines to be applied to data that my tool manages

 

[onedaywhen]

D'accord, cette fois en anglais ;-)

For the moment, forget the VB6 thing, I was simply addressing your
requirement, 'I don't want the user into my code' i.e. if it's
compiled they won't be able to see or change your code.

Take for example, an Excel Worksheet object. This has lots of
(compiled) code behind it, probably written in C/C++, to do all the
wonderful things a worksheet does. The Excel developers don't want
users in *their* code either. Instead they provide you with events,
for example, the Worksheet_SelectionChange event which passes a
(ByVal) Range object as a parameter This is your 'entry' into their
code. If the parameter was ByRef you would have greater influence over
the outcome of the event...

What I'm saying is that you could write you own class which, like the
Worksheet class, provides events the user can respond to. Admittedly,
writing code for other developers to use takes some shifting of
perspective! How to find out more? Look for the VBA keywords
'RaiseEvent' and 'WithEvents'. Where to look? The Excel help files
won't get you very far so try MSDN and the google groups Usenet
archive.

Or plead for someone to post a good example (sorry, I don't have
anything simple to hand).