In order to solve our problem, we will use some code originally made by Jon Peltier. If you are looking to learn more about this method (and other more robust ones), check out his tutorial in this link. Info |
---|
If you want to follow along, the Excel sheet used can be downloaded by clicking this link. |
First, we need to create two procedures in VBA. FindReplaceSeriesFormula is our core, and will enable us to search and replace text within our charts. The second procedure, FixActiveChart , is the one we will mainly use * 10
'... lots of code
'When we finally display MyVariable, thinking it equals 40
MsgBox MyVariable
'4 is displayedTo avoid this problem, we can use the Option Explicit Statement. It ensures that all variables must be explicitly declared by using the Dim, Private, Public, ReDim or Static statements. Otherwise, it will display an error at compile time. If used, the "Option Explicit" statement must appear in a module before any procedures. Option Explicit
Example()
'Explicitly declaring MyVariable as an integer
Dim MyVariable As Integer
MyVariable = 4
'We want to multiply MyVariable with 10
MyVaiable = MyVariable * 10
'... lots of code
'When we finally display MyVariable, thinking it equals 40
MsgBox MyVariable
'4 is displayed
End SubFindReplaceSeriesFormula(myChart As Chart, OldText As String, NewText As String)
Dim srs As Series
For Each srs In myChart.SeriesCollection
srs.Formula = Replace(srs.Formula, OldText, NewText)
Next
End Sub
Sub FixActiveChart()
FindReplaceSeriesFormula ActiveChart, "Temperature", "Rainfall" ' Replaces all references to "Temperature" with "Rainfall" (where we want our new chart)
End Sub |
Note that in this tutorial, we will only switch "Temperature" with "Rainfall", but this method can easily be expanded on. In the example below, the chart has been duplicated to a new file with a large table. Code Block |
---|
| Sub FixActiveChart()
FindReplaceSeriesFormula ActiveChart, "[Statistics.xlsx]", "" 'This line replaces the reference to the original file with the new file (nothing)
FindReplaceSeriesFormula ActiveChart, "$D$2:$D$16", "$D$4:$D$56" 'This line changes the previous range "D2:D16" with the new range "D4:D56"
End Sub |
Next up, let's check what we are actually changing using these subroutines. To do so, simply copy the chart from sheet "Temperature" into sheet "Rainfall" → right-click on it → select Select Data... In the following window, choose one of the series (e.g. "Maximum temperature") and click Edit. As shown in the images below, the series values are currently set to "=Temperature...." and not the desired"=Rainfall...". You might notice that this is easily manually fixed for the two series we have in this example, but keep in mind that given more series, the manual approach will rapidly increase in time consumption. Image Added Image Added
- To change the references, select the chart and locate the Macros button (ALT + F8) found under the Developer tab. Here, you should see the macro "FixActiveChart". Select this and click Run.
Image Added Image Added
The chart should now update to the new sheet, which can also be checked by following the same approach as described in step 2. Info |
---|
Note that the approach we have displayed here does not replace the title, axis or legends. These have to be changed manually. |
Image Added
Compiling the code above will result in the following error message. Image Removed
If you want to make all your modules have the "Option Explicit" statement without having to remember to write it yourself, follow these instructions: - In your VBA editor, go to "Tools" → "Options".
Image Removed Tick the "Require Variable Declaration" box. Click "OK".
Image Removed
|