Results 1 to 8 of 8
  1. #1
    New Lounger
    Join Date
    Mar 2002
    Posts
    23
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Run a macro in a formula (Windows NT)

    Hi
    Could you give me some help please
    is it possible to run a macro through a formula for example
    =IF(A5=3,Application.Run"Macro 1",NO) or something like this, this did not
    work when I tried it.
    Any help would be much appreciated.
    thanks Jonathon

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Run a macro in a formula (Windows NT)

    No, that is not possible. You can use the Worksheet_change event to run a macro when anything has changed to a worksheet.


    - rightclick on the sheet Tab and choose "View Code".
    - in the window that you get, from the lefthand dropdown select "Worksheet", from the righthand select "Change"

    You'll see these lines appear:

    Private Sub Worksheet_Change(ByVal Target As Excel.Range)

    End Sub

    Between these two lines, paste this code:

    Application.EnableEvents = False
    If Intersect(Target, [a1:b2]) Is Nothing Then
    Exit Sub
    End If
    Target.Value = -Target.Value
    Application.EnableEvents = True

    Close the Visual basic editor.

    The example above negates all entries done into the range A1:B2 and does nothing in all other cells.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Run a macro in a formula (Windows NT)

    No, you can not run a macro from a formula. You can run a User Defined Function if that can do what you need to have done. If not, then you would need to use one of the event routines (Worksheet Change might be a good choice in this case) to run the macro. The macro would then have to do the IF in VBA and then do whatever you need to do.
    Legare Coleman

  4. #4
    New Lounger
    Join Date
    Mar 2002
    Posts
    23
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Run a macro in a formula (Windows NT)

    Thanks for the help
    I did not think that it was possible in a formulas but thanks for the help I will have to learn more about Worksheet_change event I appreciate the help

    Thanks jonathon

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Flims, Switzerland
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Run a macro in a formula (Windows NT)

    Jonathon,

    Simply change your Macro from being a Sub to being a Function, and make it return a value. The return value can be whatever you like, but since you've used "NO", in the worksheet function, I'll make it return "YES".

    e.g.<pre>Sub Macro1
    .
    .
    End Sub</pre>

    becomes

    <pre>Function Macro1
    .
    .
    Macro1="YES"
    End Function</pre>


    Then, change your formula to

    <pre>=IF(A5=3,Macro1(),"NO") </pre>

    If A5=3, your macro should run, and the cell should say "YES". If A5<>3, then the macro won't run and the cell will say "NO".

    NOTE: There are certain things that VBA in your Macro can't do if it's being called in this way, from a worksheet function. e.g. change values of other cells, add sheets, etc. In general, anything that alters the appearance of the workbook, other than the calling cell's value, can't be done.

  6. #6
    New Lounger
    Join Date
    Mar 2002
    Posts
    23
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Run a macro in a formula (Windows NT)

    Adam

    I tried the function macro and the formula returned yes but the macro did not run for example this is what I wont the macro to do
    e.g.
    Function Macro1
    .
    ' Macro1 Macro
    ' Macro recorded 21/03/2002 by Office Automation
    '
    Range("C4").Select
    Selection.Copy
    Range("D4").Select
    ActiveSheet.Paste
    Macro1="YES"
    End Sub

    Is there any way to achieve this?
    Thanks for the help
    Jonathon

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Run a macro in a formula (Windows NT)

    No, everything in that procedure is invalid in a function.
    Legare Coleman

  8. #8
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Flims, Switzerland
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Run a macro in a formula (Windows NT)

    The only way to achieve this, is to use the Worksheet_Change event to run the macro, as was suggested before.

    Change Macro1 back to a Sub, and put the following code in the worksheet's code module.
    <pre>Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Application.Intersect(Target, Range("a5")) Is Nothing Then
    If Range("a5").Value = 3 Then Macro1
    End If
    End Sub</pre>


    And, ff you still want the formula to display YES or NO, just change it to a simple
    <pre>=IF(A5=3,Application.Run"Macro 1",NO)</pre>


Posting Permissions

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