Run Macro from if(function)

Dutchie

New Member
Joined
Nov 6, 2002
Messages
4
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.
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Maybe you can use the event CALCULATE :

Private Sub Worksheet_Calculate()
'your macro code here ...
'check the date etc.
End Sub
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
if you want to check it's friday,change philR's code
if myA=5 then
to
If WeekDay(myA) = 6 Then
 
Upvote 0
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
 
Upvote 0
Thanks, Tom,

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

Forum statistics

Threads
1,213,557
Messages
6,114,293
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top