VB Text Argument as VB Instruction (Excel 2003)

[Antediluvian]

Is there any simple way to pass a text string as an argument to a VB function which will be obeyed as a VB instruction? For example ObeyText("Text","FAIL Text") where "FAIL Text" would be returned if the execution of "Text" actually failed (rather than normal error handling).

The reason for my request is that sheet protection has changed in 2003, and I have a legacy library of routines in Macro 4 language. It provides a programmer interface and I wish to enhance it so that my user passes text down to me and I pass it to this function. My Visual Basic is very weak!! My proposed approach would allow me flexibility with other changes.

[HansV]

I fail to see the connection between sheet protection, Excel 4 macros and calling functions by name.

Anyway, you can call functions in a class module by name, by using the CallByName function, but I doubt you want that. You'd have to rewrite existing code substantially to use that.

Perhaps someone will have a suggestion if you explain in more detail what you want to accomplish.

[Antediluvian]

Hans,

Thank you for your comments. In more detail I have a Macros 4 function which looks like

ZSheetProtect(Sheet,contents,windows,password,obje cts,scenarios)

where

[HansV]

Thanks. I hope someone with Excel 2003 can help (I have Excel 2002 myself)

[SammyB]

If I understand your question (which is doubtful), you just need to use the Excel application method, Evaluate.
For example, put 1,2,3 in cells A1, A2, A3 then put SUM(A1:A3) in cell A4. (not the normal =SUM). Leave A4 as the active cell.

Now, create and execute the following macro:
<pre>Option Explicit

Sub Macro1()
Dim s As String
On Error GoTo reWrite
s = Application.Evaluate(ActiveCell.Text)
MsgBox s
Exit Sub
reWrite:
MsgBox ActiveCell.Text & " cannot be executed."
End Sub
</pre>

You will get the msgbox with 6. Now change A4 to ZSUM(A1:A3) and execute the macro, you get ZSUM(A1:A3) cannot be executed. HTH --Sam

[Antediluvian]

Sam,

This looks perfect to me: just what I wanted. I'll spend some time investigating it. Thanks to both you and Hans.

regards

Geoffrey