Macros designed to edit Macros?

dilshod_k

Board Regular
Joined
Feb 13, 2018
Messages
79
I have number of workbooks with almost identical Macros and quite often I have to make corrections to Macros in order to optimise performance. Is there any way to write code which would open all files one by one and made changes in Macros? Multiplying by copy and paste is not an option as there are small differences between the files contents and Macros. Thanks in advance for any suggestions.
 
Don't want to waste your time because I probable will not be able to help you.
But in this script show what might you need to change.
You said you had to change Macros frequently.

In the script I published earlier nothing needs to be changed. I published it in order to explain how it works :). But some of the formulas (highlighted in red color) in one of these macros need to be changed quite often, and doing it manually in each file is pretty tedious.:

Sub A_BUY_SELL_Signals_MasterSheet()
'
' BUY_SMA10_30_Crossover_SELL_Price_SMA10_Crossover Macro
'


'
Dim ws As Worksheet


For Each ws In Sheets

If ws.Name <> "Control" And ws.Name <> "Response" Then

ws.Activate
Range("I1").Select
Selection.NumberFormat = "?#,##0.00"
ActiveCell.FormulaR1C1 = "SMA10"
Range("I2").Select
ActiveCell.FormulaR1C1 = "=AVERAGE(RC[-3]:R[9]C[-3])"
Range("I2").Select
Selection.AutoFill Destination:=Range("I2:I84")
Range("I2:I84").Select

Range("J1").Select
Selection.NumberFormat = "?#,##0.00"
ActiveCell.FormulaR1C1 = "SMA30"
Range("J2").Select
ActiveCell.FormulaR1C1 = "=AVERAGE(RC[-4]:R[29]C[-4])"
Range("J2").Select
Selection.AutoFill Destination:=Range("J2:J84")
Range("J2:J84").Select

Range("K1").Select
Selection.NumberFormat = "?#,##0.00"
ActiveCell.FormulaR1C1 = "BUY/SELL SMA10"
Range("K2").Select
Columns("K:K").EntireColumn.AutoFit
ActiveCell.FormulaR1C1 = _
"=IF(AND(RC[-5]>RC[-2],R[1]C[-5]<R[1]C[-2],RC[-2]>RC[-1]),""BUY"",IF(AND(RC[-5]<RC[-2],R[1]C[-5]>R[1]C[-2]),""SELL"",""""))"
Range("K2").Select
Selection.AutoFill Destination:=Range("K2:K84")
Range("K2:K84").Select


' Range("L1").Select
' Selection.NumberFormat = "?#,##0.00"
' ActiveCell.FormulaR1C1 = "BUY/SELL SMA30"
' Range("L2").Select
' Columns("L:L").EntireColumn.AutoFit
' ActiveCell.FormulaR1C1 = _
' "=IF(AND(RC[-6]>RC[-2],R[1]C[-6]<R[1]C[-2]),""BUY"",IF(AND(RC[-6]<RC[-2],R[1]C[-6]>R[1]C[-2]),""SELL"",""""))"
' Range("L2").Select
' Selection.AutoFill Destination:=Range("L2:L84")
' Range("L2:L84").Select
' ActiveWindow.ScrollColumn = 2
' ActiveWindow.SmallScroll ToRight:=7
'
' Range("M1").Select
' Selection.NumberFormat = "?#,##0.00"
' ActiveCell.FormulaR1C1 = "BUY/SELL SMA CROSSOVER"
' Range("M1").Select
' Columns("M:M").EntireColumn.AutoFit
' Range("M2").Select
'
' ActiveCell.FormulaR1C1 = _
' "=IF(AND(RC[-4]>RC[-3],R[1]C[-4]<R[1]C[-3]),""BUY"",IF(AND(RC[-7]<RC[-4],R[1]C[-7]>R[1]C[-4]),""SELL"",""""))"
' Range("M2").Select
'
' Selection.AutoFill Destination:=Range("M2:M84")
' Range("M2:M84").Select
Range("N2").Select

End If
Next ws

ActiveWorkbook.Save
 
Upvote 0

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Just curious again:

Why do you have all these ' in front of all these lines of code?

When you put a ' in front of the line of code your telling the script to not do that line of code.

See here you have:

' Range("L1").Select
' Selection.NumberFormat = "?#,##0.00"
' ActiveCell.FormulaR1C1 = "BUY/SELL SMA30"
' Range("L2").Select
 
Upvote 0
Just curious again:

Why do you have all these ' in front of all these lines of code?

When you put a ' in front of the line of code your telling the script to not do that line of code.

See here you have:

' Range("L1").Select
' Selection.NumberFormat = "?#,##0.00"
' ActiveCell.FormulaR1C1 = "BUY/SELL SMA30"
' Range("L2").Select

When I do not need all the data for analysis I simply inactivate part of the code. I might need it sometime later so I do not erase it. This time I did not need data in columns L and M.
 
Upvote 0
Ok. And now what was your logical reason for wanting to not run theses lines of code.

See if there is some logical reasoning we can use a if statement.

Like for example If Today is Monday do not run these lines of code.

Is there some standard logic here?

Or if Range("A1") value="Mom" do not run these line of code.
 
Upvote 0
Ok. And now what was your logical reason for wanting to not run theses lines of code.

See if there is some logical reasoning we can use a if statement.

Like for example If Today is Monday do not run these lines of code.

Is there some standard logic here?

Or if Range("A1") value="Mom" do not run these line of code.

Some of these columns contain indexes (SMA10 - Simple Moving Average for 10 days; SMA30 - for 30 days (in technical analysis of the stock price graphs there are dozens of various indexes)), others designed for BUY/SELL signals (based on conditions like you mentioned: =IF(AND(F2>I2,F3<I3,I2>J2),"BUY",IF(AND(F2<I2,F3>I3),"SELL","")).

[TABLE="width: 762"]
<colgroup><col><col><col span="8"><col></colgroup><tbody>[TR]
[TD]Security Code[/TD]
[TD]Date[/TD]
[TD]Open[/TD]
[TD]High[/TD]
[TD]Low[/TD]
[TD]Close[/TD]
[TD]Adj Close[/TD]
[TD]Volume[/TD]
[TD]SMA10[/TD]
[TD]SMA30[/TD]
[TD]BUY/SELL SMA10[/TD]
[/TR]
[TR]
[TD]ABB[/TD]
[TD="align: right"]01-Feb-2019[/TD]
[TD="align: right"]€19.31[/TD]
[TD="align: right"]€19.36[/TD]
[TD="align: right"]€19.22[/TD]
[TD="align: right"]€19.24[/TD]
[TD="align: right"]€19.24[/TD]
[TD="align: right"]2,671,100[/TD]
[TD="align: right"]€19.22[/TD]
[TD="align: right"]€19.11[/TD]
[TD]BUY[/TD]
[/TR]
[TR]
[TD]ABB[/TD]
[TD="align: right"]31-Jan-2019[/TD]
[TD="align: right"]€19.09[/TD]
[TD="align: right"]€19.17[/TD]
[TD="align: right"]€18.96[/TD]
[TD="align: right"]€19.15[/TD]
[TD="align: right"]€19.15[/TD]
[TD="align: right"]9,149,300[/TD]
[TD="align: right"]€19.21[/TD]
[TD="align: right"]€19.12[/TD]
[TD]SELL[/TD]
[/TR]
[TR]
[TD]ABB[/TD]
[TD="align: right"]30-Jan-2019[/TD]
[TD="align: right"]€19.12[/TD]
[TD="align: right"]€19.39[/TD]
[TD="align: right"]€19.06[/TD]
[TD="align: right"]€19.36[/TD]
[TD="align: right"]€19.36[/TD]
[TD="align: right"]4,633,000[/TD]
[TD="align: right"]€19.21[/TD]
[TD="align: right"]€19.12[/TD]
[TD]BUY[/TD]
[/TR]
[TR]
[TD]ABB[/TD]
[TD="align: right"]29-Jan-2019[/TD]
[TD="align: right"]€19.17[/TD]
[TD="align: right"]€19.25[/TD]
[TD="align: right"]€19.02[/TD]
[TD="align: right"]€19.02[/TD]
[TD="align: right"]€19.02[/TD]
[TD="align: right"]4,735,600[/TD]
[TD="align: right"]€19.20[/TD]
[TD="align: right"]€19.12[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ABB[/TD]
[TD="align: right"]28-Jan-2019[/TD]
[TD="align: right"]€19.15[/TD]
[TD="align: right"]€19.23[/TD]
[TD="align: right"]€19.02[/TD]
[TD="align: right"]€19.21[/TD]
[TD="align: right"]€19.21[/TD]
[TD="align: right"]2,334,600[/TD]
[TD="align: right"]€19.24[/TD]
[TD="align: right"]€19.12[/TD]
[TD]SELL[/TD]
[/TR]
[TR]
[TD]ABB[/TD]
[TD="align: right"]25-Jan-2019[/TD]
[TD="align: right"]€19.42[/TD]
[TD="align: right"]€19.57[/TD]
[TD="align: right"]€19.41[/TD]
[TD="align: right"]€19.49[/TD]
[TD="align: right"]€19.49[/TD]
[TD="align: right"]1,979,000[/TD]
[TD="align: right"]€19.26[/TD]
[TD="align: right"]€19.12[/TD]
[TD]BUY[/TD]
[/TR]
</tbody>[/TABLE]

But these are just different algorithms which I'm trying to figure out empirically. That is why they need frequent tweaking. The logic is based on position of current price in relation to various types of indexes, and interrelationship between indexes. Unfortunately maths and logic are not sufficient to make BUY/SELL decisions, many things depend on shape of the linear indexes on the graphs. Conditions like "when price crosses SMA10 above or below" are not enough because there will be too many false signals. That is why all signals after selection must be validated by checking graphs of these stocks. The aim of tweaking is to reduce number of false signals. What you suggesting I'm already using in algorithm formulas. Those "rigid" logical conditions would be more applicable during reverse backtesting, when you want to test profitability of your trading strategy/algorithm based on historical price data, but I'm just making first steps in programming and it would be too complicated for me.
Thank for your willingness to help anyway.
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,183
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

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