Macros designed to edit Macros?

[dilshod_k]

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.

 

[Kyle123]

Have a look here: http://www.cpearson.com/excel/vbe.aspx

 

[dilshod_k]

Thanks a lot. Appreciate it.

 

[My Aswer Is This]

Have you considered storing your macros in your Personal Workbook.

Then just modify them there.

Then if you had Workbook One Open run Macro1

Then if you had Workbook Two Open run Macro2

​

 

[dilshod_k]

Have you considered storing your macros in your Personal Workbook.

Then just modify them there.

Then if you had Workbook One Open run Macro1

Then if you had Workbook Two Open run Macro2

​

I did not think about it. Thanks for suggestion.
Each macro assigned to certain file, there are 10-15 files in each group. Each group tests certain algorithm. Even if I'll keep all macros in one file, I'll still have to be able to make changes to a group of 10-15 macros at a time. As long as I have to change algoritms pretty often, doing it manually is inconvenient.

 

[My Aswer Is This]

You said:

changes to a group of 10-15 macros at a time

Are these Module Macros or Private Macros





​

 

[dilshod_k]

My speciality is far away from coding. I'm anaesthetist. I'm not fully oriented in terms and definitions. I found on internet workbook which downloads historical stock price data and modified it to my needs, sometimes by recording macros, sometimes by inserting pieces of codes found on internet again in the form of macros. So each workbook has set of macros which is triggered in certain order by master macros named Macros3. Basically for job to be done, after opening file I just have to run Macros3. But if I want to make amendments to one of the macros (lets say Macros1) responsible for analysis algorithm, I have to open each file manually and make changes manually.

 

[My Aswer Is This]

I hear you:

Not knowing how Macros work exactly and then trying to make them dynamic so they can be modified easily can be difficult.

I'm sure you have your reasons but the least number Workbooks you have make things much easier.

For example if you wanted to follow Stock Prices on 20 Different stocks it would be easier to have 20 sheets in one Workbook and use each sheet for a different stock.

I have no ideal how a script could modify another script let alone have one script modify 10 different scripts in 10 different workbooks.

Maybe if you showed us one script and explained what you might need to change would give us a ideal you could use.

 

[dilshod_k]

I hear you:

Not knowing how Macros work exactly and then trying to make them dynamic so they can be modified easily can be difficult.

I'm sure you have your reasons but the least number Workbooks you have make things much easier.

For example if you wanted to follow Stock Prices on 20 Different stocks it would be easier to have 20 sheets in one Workbook and use each sheet for a different stock.

I have no ideal how a script could modify another script let alone have one script modify 10 different scripts in 10 different workbooks.

Maybe if you showed us one script and explained what you might need to change would give us a ideal you could use.

I'll try to explain the reason for multiple workbooks.
Typical workbook has list of stock symbols in column A, you just specify Date and number of periods (days, weeks or months) and run the code. The code retrieves historical data as it follows below (info for the last 3 days):

[TABLE="width: 528"]
<colgroup><col><col><col span="6"></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]
[/TR]
[TR]
[TD]ADM[/TD]
[TD="align: right"]01-Feb-2019[/TD]
[TD="align: right"]€45.04[/TD]
[TD="align: right"]€45.13[/TD]
[TD="align: right"]€44.76[/TD]
[TD="align: right"]€44.89[/TD]
[TD="align: right"]€44.89[/TD]
[TD="align: right"]2,337,700[/TD]
[/TR]
[TR]
[TD]ADM[/TD]
[TD="align: right"]31-Jan-2019[/TD]
[TD="align: right"]€44.47[/TD]
[TD="align: right"]€45.04[/TD]
[TD="align: right"]€44.41[/TD]
[TD="align: right"]€44.90[/TD]
[TD="align: right"]€44.90[/TD]
[TD="align: right"]5,076,200[/TD]
[/TR]
[TR]
[TD]ADM[/TD]
[TD="align: right"]30-Jan-2019[/TD]
[TD="align: right"]€44.37[/TD]
[TD="align: right"]€44.88[/TD]
[TD="align: right"]€44.18[/TD]
[TD="align: right"]€44.69[/TD]
[TD="align: right"]€44.69[/TD]
[TD="align: right"]2,857,600[/TD]
[/TR]
</tbody>[/TABLE]

of each stock symbol in the list for a given number of Days (I normally download data for the last 60 days). For each stock symbol it adds new Sheet with the same name where it pastes stock symbol data, so if you have 20 stocks symbols in your list you gonna have 100 new sheets with tables. As I was interested in analysing around 1000 - 1500 stocks I found that the speed of data download progressively decreases after 100 symbols. That is why I divided the whole list of stock symbols into 10-15 separate workbooks with 100 stocks symbols in each one.
This data without analysis is of no use, so I've added some macros as I was not able to incorporate them in the original code. One of the advantages of this system is that I can easily find macros responsible for analysis algorithm and make changes to it whenever I want. As long as I'm testing several different algorithms, I had to divide files into several separate groups 10-15 in each one. When it finishes with analysis it exports selected data to master DataFile. For the time being I have to open each file in the group manually (lets say 1 to 10), enter date (up to which historical data downloaded and run master macros. My aim is to write Macros () which would operate from DataFile and retrieve information for the whole group of files automatically. The example of master macros (Macros3) you may find below:

Sub Macro3()
'


Dim intResponse As Integer
Dim wsWorksheet As Worksheet
Dim i As Integer


' intResponse = MsgBox("Confirm that you want to remove all existing Historical Data Worksheets?" & vbNewLine & vbNewLine & _
' "If you proceed all worksheets in this workbook will be removed apart from this worksheet 'Control'" & vbNewLine & vbNewLine & _
' "Select 'Yes' to DELETE all Historical Worksheets" & vbNewLine & _
' "Select 'No' to cancel" & vbNewLine & vbNewLine & _
' "Note: You can manually delete worksheets if required to retain any personal worksheets", vbYesNo, "Confirm - DELETE Historical Worksheets?")
' If intResponse = vbNo Then Exit Sub

Application.DisplayAlerts = False

For Each wsWorksheet In ThisWorkbook.Worksheets
If wsWorksheet.Name <> "Control" And wsWorksheet.Name <> "Response" Then
wsWorksheet.Delete
i = i + 1
End If
Next


Application.DisplayAlerts = False

Application.Run _
"'SMA Crossover (1).xlsm'!ExtractHistoricalData"
Application.CutCopyMode = False
Application.Run _
"'SMA Crossover (1).xlsm'!A_BUY_SELL_Signals_MasterSheet"
Application.Run _
"'SMA Crossover (1).xlsm'!B_Delete_Empty_Rows"
Application.Run _
"'SMA Crossover (1).xlsm'!Export_Data"


End Sub


My apologies if explanation is confusing. English is not my native language.
Thanks in advance for any suggestions.

 

[My Aswer Is This]

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.