1. Choose the "Developer" tab and open the VBA editor. If you don't locate the "Developer" tab, follow this guide. The following window will then open.
   
2. To write our first macro, choose "Insert" -> "Module".
   
   
3. First, we will make a Sub procedure that we may later add to our button. All Sub procedures have to begin with Sub and end with End Sub. A Sub can not return a value, and may be compared with a program in other programming languages, where as a function is an alternative. We then call the MsgBox function to create a new window displaying our message.
   
   
   

4. Lets now return to our Excel sheet to add the button. The insert button feature is shown in the image below.

   Tips: To align the size of the button with existing cells, hold down ALT while resizing. This applies to all sizings!

   
   

5. A new window will open. Choose the "Hello World" macro we just created and click "OK".
   
   
   
6. We may then rename our button by right-clicking on it and choosing "Edit text".
   
   
   
   
7. Finally, we can test our macro by clicking on the button. A window displaying the message "Hello World!" should then open.
   If it does, congratulations! You have just made your first macro using VBA.
   
   

Hello World video tutorial (no audio)

1. Make a simple sheet to duplicate.
   
2. Locate the Developer tab, and click on Record Macro.
   
3. A new window should then open. You always want to name your macros something meaningful, making it easy to understand what macro does by just the name. This applies especially when the number of macros increase. Click then OK.
   
4. VBA is now recording our actions.
   
5. To duplicate a sheet in Excel, right-click on the sheet name, and click on Move or Copy...
   
6. In the window appearing, mark off for (move to end) on the location, and tick off for Create a copy. Click then OK.
   
7. A duplicate has now been made.
   
8. Time to stop the recording. This is done by clicking on Stop Recording, located the same place as the Record Macro was.
   
9. To look at the code created by VBA, open the Visual Basic Editor.
   
10. Open Module1, and the code is displayed.
    
11. If we want to run the code again, we can click somewhere inside the subroutine DuplicateSheet() and then click on the play icon.
    
12. Our Sheet1 is then duplicated again.
    
13. If we wanted, we could also have made e.i. a button and attached our macro to it, just as in the Hello World tutorial.

DuplicateSheet video (no audio)

1. Open the VBA editor as before and insert a new module. If you don't rememeber how, check out the Hello World tutorial. Your window should then look like the one below.
   

2. Make a Function. Since this is an example, we will just do a simple calculation involving the input parameter.

   Function myFunction(num As Integer)
   
   ' Remember that the variable named the same as the function is returned
   myFunction = num / 2 + 7
   
   End Function

   

3. Our function is now done and we can go back to the excel sheet to try it out. We have already inserted some numbers to do the calculations on. As with any other function, start with "=" + "function name". Excel will then autocomplete the function name, and all we have to do is choose the cell (or just write a number) to do the calculation on.
   
4. You have now made your first custom function in Excel, congratulations!
   
   
   

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.

1. 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.

   Sub FindReplaceSeriesFormula(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.

   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

2. 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.

   

3. 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.
   
   
   

4. The chart should now update to the new sheet, which can also be checked by following the same approach as described in step 2.

   Note that the approach we have displayed here does not replace the title, axis or legends. These have to be changed manually.