Link to home
Create AccountLog in
Avatar of cpatte7372
cpatte7372Flag 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
Avatar of Rory Archibald
Rory Archibald
Flag of United Kingdom of Great Britain and Northern Ireland image

It's pretty hard without the macros, but I'd be surprised if 4 macros can be combined into a formula.
I think you forgot the macros.
ASKER CERTIFIED SOLUTION
Avatar of SafetyFish
SafetyFish
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
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.
Avatar of 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
Rorya,

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

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
Carlton,

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

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...
Since the subs try to change the Excel environment, they will not work when called from a worksheet cell.
Go rorya for knowing everything!
In retrospect, this sounds sarcastic, but I really am starting to think you are omniscient. And omnipresent. At least on this forum.
I'm not omniscient, but I do get around. :)
Thanks