Avatar of cpatte7372
cpatte7372
Flag for United Kingdom of Great Britain and Northern Ireland asked on

Streamline Excel Macros

Hello Experts,

I have four macros that I'm sure could be combined in one. I was wondering if someone could combine the following four macros into one macro.

Also, is there a way to get the macro to run as soon as I open the spreadsheet?

And one last thing. I was wondering if it was possible to remove the macros completely and have it as a formula?

Cheers


Carlton
Microsoft Excel

Avatar of undefined
Last Comment
cpatte7372

8/22/2022 - Mon
Rory Archibald

It's pretty hard without the macros, but I'd be surprised if 4 macros can be combined into a formula.
StephenJR

I think you forgot the macros.
ASKER CERTIFIED SOLUTION
SafetyFish

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Rory Archibald

For the record, it is really quite unlikely that simply converting a sub to a function and supplying a return value will allow it to work from a worksheet cell.
Your help has saved me hundreds of hours of internet surfing.
fblack61
cpatte7372

ASKER
Crap!

Yep I forgot the macros...

Public Sub timer8()
Sheets("mini sized DOW").Range("BM4:BM33").Value = Sheets("mini sized DOW").Range("BL4:BL33").Value
Application.OnTime CVDate((Int(Now * 7.5 * 24) + 1) / 24 / 7.5), "timer8"
End Sub
Public Sub timer6()
Sheets("mini sized DOW").Range("BO4:BO33").Value = Sheets("mini sized DOW").Range("BN4:BN33").Value
Application.OnTime CVDate((Int(Now * 10 * 24) + 1) / 24 / 10), "timer6"
End Sub
Public Sub timer4()
Sheets("mini sized DOW").Range("BQ4:BQ33").Value = Sheets("mini sized DOW").Range("BP4:BP33").Value
Application.OnTime CVDate((Int(Now * 15 * 24) + 1) / 24 / 15), "timer4"
End Sub
Public Sub timer2()
Sheets("mini sized DOW").Range("BS4:BS33").Value = Sheets("mini sized DOW").Range("BR4:BR33").Value
Application.OnTime CVDate((Int(Now * 30 * 24) + 1) / 24 / 30), "timer2"
End Sub


Thanks
SafetyFish

Rorya,

I'll take your word for it! Although I know I have done it a few times when coding myself.

cpatte7372

ASKER
SafetyFish,

Thats a fantastic reply.

To have the macros as a formula are you suggesting I do the following:

Public Sub Combine()

Call timer2
Call timer4
Call timer6
Call timer8

End Sub

Carlton
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
SafetyFish

Carlton,

That's exactly what I'm suggesting. Let us know how it works for you.

SafetyFish

Oops, Carlton, on closer inspection the code you posted in your last comment is not a "formula" or a Function. It is a basic sub routine, or "Macro", that can be attached to a button. In order for it to be a function (as posted in my original comment) you would have to do this:

Public Function Combine() as Integer

Call timer2
Call timer4
Call timer6
Call timer8

Combine = 1

End Function

Rorya says that this is unlikely to work straight away, but in absence of the workbook you are using I can't try it out to make sure. I can't for the life of me figure out why you would need a function to call 4 macros... this sounds unwise to me, too. If you're dead set on doing it, give it a go.

In general, one should create a spreadsheet function if there is a tedious calculation that you are having to repeatedly enter into lots of cells that cannot be copied, pasted, and edited faster than you can write and debug the code. If you do have a valid reason for having this code (I'd say 10-20+ cells which require the function) then that means every time the sheet changes you are going to have to wait for excel to run each macro 10-20 times...
Rory Archibald

Since the subs try to change the Excel environment, they will not work when called from a worksheet cell.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
SafetyFish

Go rorya for knowing everything!
SafetyFish

In retrospect, this sounds sarcastic, but I really am starting to think you are omniscient. And omnipresent. At least on this forum.
Rory Archibald

I'm not omniscient, but I do get around. :)
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
cpatte7372

ASKER
Thanks