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.
 

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

Bruno

Active Member
Joined
Feb 17, 2002
Messages
491
Maybe you can use the event CALCULATE :

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

Legacy 98055

Guest
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
 

Dutchie

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

philR

Active Member
Joined
Feb 25, 2002
Messages
257

ADVERTISEMENT

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
 

Dutchie

New Member
Joined
Nov 6, 2002
Messages
4

ADVERTISEMENT

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
 

Bruno

Active Member
Joined
Feb 17, 2002
Messages
491
if you want to check it's friday,change philR's code
if myA=5 then
to
If WeekDay(myA) = 6 Then
 
L

Legacy 98055

Guest
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
 

Dutchie

New Member
Joined
Nov 6, 2002
Messages
4
Thanks, Tom,

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

Watch MrExcel Video

Forum statistics

Threads
1,127,199
Messages
5,623,323
Members
415,966
Latest member
ctorohuamanchumo

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
Top