Results 1 to 6 of 6
  1. #1
    Star Lounger
    Join Date
    Feb 2005
    Posts
    50
    Thanks
    6
    Thanked 0 Times in 0 Posts

    VB Text Argument as VB Instruction (Excel 2003)

    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.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VB Text Argument as VB Instruction (Excel 2003)

    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.

  3. #3
    Star Lounger
    Join Date
    Feb 2005
    Posts
    50
    Thanks
    6
    Thanked 0 Times in 0 Posts

    Re: VB Text Argument as VB Instruction (Excel 2003

    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

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VB Text Argument as VB Instruction (Excel 2003

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

  5. #5
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: VB Text Argument as VB Instruction (Excel 2003

    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
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  6. #6
    Star Lounger
    Join Date
    Feb 2005
    Posts
    50
    Thanks
    6
    Thanked 0 Times in 0 Posts

    Thank you

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •