Macro to run code written in cells BA1 to BA2000

[bobaol]

Hi, I wrote this code inside the excel spreadsheet itself. The code sits in BA1 to BA2000. I would then copy and paste this code to a VB module, then run it. As the data changes, the SchemeColor number changes. My question... is there a way to run write a macro in VB and have it use the code in cells BA1 to BA2000 so that I do not have to copy and paste it into the VB Module and run it? Manually running it is ok with me, but I do not want to copy and paste the code into VB each and every time. Any help is appreciated. Thanks in advance.


Here is my code:
Sub Macro2_()

ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.SeriesCollection(1).Select
ActiveChart.SeriesCollection(1).Points(1).Select
With Selection.Border
.Weight = xlThin
.LineStyle = xlAutomatic
End With
Selection.Shadow = False
Selection.InvertIfNegative = False
Selection.Fill.Patterned Pattern:=msoPatternDarkUpwardDiagonal
With Selection
.Fill.Visible = True
.Fill.ForeColor.SchemeColor = 50
.Fill.BackColor.SchemeColor = 50
End With
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.SeriesCollection(1).Select
ActiveChart.SeriesCollection(1).Points(99).Select
With Selection.Border
.Weight = xlThin
.LineStyle = xlAutomatic
End With
Selection.Shadow = False
Selection.InvertIfNegative = False
Selection.Fill.Patterned Pattern:=msoPatternDarkUpwardDiagonal
With Selection
.Fill.Visible = True
.Fill.ForeColor.SchemeColor = 37
.Fill.BackColor.SchemeColor = 37
End With
end sub

 

[rcbunting]

Boboal

In the strictest sense of what is possible, the answer is yes, you can write self modifying VBA code. You would use the CodeModule object to do this. (ThisWorkbook.VBProject.VBComponents.Item("...").CodeModule...).

But this is a pretty far off from best practices. I really recommend that you find a way to write static code and simply update the necessary variables, instead of actually updating the macro itself!

 

[bobaol]

Hi, It's been about a year, but as life progresses, it always seem to circle back... anyway, back to this issue. the data changes and the graph changes. i really do not want to manually do this, I want a macro to run BA1 to BA2000. The code in BA1 to BA2000 changes as the data changes. If anyone can write a concrete example as how to run the code in cells BA1 to BA5, it would help me a whole lot. Assume for this example, cell BA1 is Sub Macro2_(), and BA2 to BA4 is the code, and BA5 is End Sub. So, what is the code in the macro to run BA2, BA3, BA4? thanks again.