Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Run Macro from if(function)

This is a discussion on Run Macro from if(function) within the Excel Questions forums, part of the Question Forums category; Can anybody tell me if its possible to run a macro from an if statement as in =if(weekday(A1)=5,Executemacro,"") Or else ...

  1. #1
    New Member
    Join Date
    Nov 2002
    Posts
    4

    Default

    Can anybody tell me if its possible to run a macro from an if statement as in
    =if(weekday(A1)=5,Executemacro,"")

    Or else how to program the result of a macro to a cell when a certain condition exists?

    I already have a macro that takes data from an active cell with a running number:

    Range("B5").Select
    Selection.Copy
    Range("E26").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

    Now I need to run this Macro when its friday (example)

    Can't find it in the VB help files.
    Appreciate a pro's eye on this.

  2. #2
    Board Regular Bruno's Avatar
    Join Date
    Feb 2002
    Posts
    473

    Default

    Maybe you can use the event CALCULATE :

    Private Sub Worksheet_Calculate()
    'your macro code here ...
    'check the date etc.
    End Sub


  3. #3
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824

    Default

    Hi Dutchie.
    You cannot run a macro from an Excel function. You can, however, test for a value or condition and then run the macro based upon that value or condition.
    For example, to automatically run some code on Friday, assuming that the workbook will be opened on any given Friday, when your workbook is initially opened, place your code within the confines of an event. In this case, the workbook open event. Whenever your workbook is opened, any code which resides here will run. To duplicate your code above, paste this in the correct procedure(see below):

    Range("E26").Value = Range("B5").Value

    Right click on the Excel icon located immediately to the left of the "File" menu item, choose view code, paste in the following:

    Private Sub Workbook_Open()
    With Sheets("YourSheetNameInTheseQuotes")
    .Range("E26").Value = .Range("B5").Value
    End With
    End Sub

    If this macro can only run once on the 6th day of the week, we will need a bit more work in this case.

    Tom

  4. #4
    New Member
    Join Date
    Nov 2002
    Posts
    4

    Default

    Ok thanks, Bruno and Tom. Much appreciated. Will start working on this and may hopefully take it a step further myself based on Tom's last remark.
    Running a Macro from an Excel function would a nice piece of Excel coding though.

    Ciao

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Location
    Sheffield, UK
    Posts
    249

    Default

    You can run a macro from a function if it is a user defined function. In a module you will need the following:

    Function CheckDay(myA)
    if myA=5 then
    Macro1
    CheckDay="Done"
    Else
    CheckDay="Not Done"
    End If
    End Function

    Sub Macro1()
    ..whatever..
    End Sub

    If you then put this formula in a cell on your spreadsheet:

    =CheckDay(A1)

    then macro1 will run (and the cell will show "Done") whenever the value in A1 changes to 5. Hope this helps.

    PhilR

  6. #6

    Join Date
    Oct 2002
    Posts
    49

    Default

    PhilR
    Have you tested what you are suggesting?

  7. #7
    New Member
    Join Date
    Nov 2002
    Posts
    4

    Default

    Thanks PhilR and Hi Pear,

    I just read your posts. Haven't had a chance to get on it. Is Pear suggesting something else here?
    I have not found any posts on this subject before.

    Dutchie



  8. #8
    Board Regular Bruno's Avatar
    Join Date
    Feb 2002
    Posts
    473

    Default

    if you want to check it's friday,change philR's code
    if myA=5 then
    to
    If WeekDay(myA) = 6 Then



  9. #9
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824

    Default

    Phil is correct but this does not solve the problem. You can call 100 procedures from within a function and you are still confined to the same rules. A function can only return a value. That's it. It cannot change properties in other objects, ect...
    There are concrete reasons for these rules which made sense when I read about them. Don't remember what the reasons were though.

    tom

  10. #10
    New Member
    Join Date
    Nov 2002
    Posts
    4

    Default

    Thanks, Tom,

    I will work on all of this and will report back with the solution aprox in a weeks' time.




Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com