| Expand |
|---|
| title | Duplicate chart with references tutorial |
|---|
|
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. | Code Block |
|---|
| 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. | 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
|
| Expand |
|---|
| title | Comparison operators |
|---|
|
Comparison operators are used to compare expressions. They may return True, False or Null. Below is a table showing the basic comparison operators in VBA.
| Operator | Explanation (True if) |
|---|
| A < B | A less than B |
| A < = B | A less than or equal to B |
| A > B | A greater than B |
| A > = B | A greater than or equal to B |
| A = B | A equal to B |
| A < > B | A not equal to B |
| Info |
|---|
The operators Like and Is are described further down, as they have some special functionalities. |
To learn when the compartion operators return Null, read the offical documentation on the comparison operators. Here, you will also find more detailed information regarding the actual comparisons done when using the operators. This includes outcome of comparing different variant types, such as a string and a double.
Let's look at some exampels:
| Code Block |
|---|
|
Dim MyResult, Var1, Var2 ' Variables are declared as type "Variant"
Dim str1 As String
Dim num1 As Integer
MyResult = (10 < 5) ' Returns False.
MyResult = (1 <> 2) ' Returns True.
MyResult = ("5" > "4") ' Returns True.
MyResult = ("hi" > "hello") ' Returns True, since ("i" > "e").
MyResult = ("hi" > "Hi") ' Returns True, since the ASCII values for lowercase letters are higher than for uppercase letters.
Var1 = 5: Var2 = Empty
MyResult = (Var1 > Var2) ' Returns True, since "Empty" is evaluated as 0.
Var1 = 5: Var2 = Empty
MyResult = (Var1 > Var2) ' Returns False, since "Empty" is evaluated as 0.
Var1 = 0: Var2 = Empty
MyResult = (Var1 = Var2) ' Returns True, since "Empty" is evaluated as 0.
Var1 = "1": Var2 = 4
MyResult = (Var1 > Var2) ' Returns True, since a "Variant of type string" is always greater than a "Variant of type numeric".
str1 = "1": num1 = 4
MyResult = (str1 > num1) ' Returns False, since the "string" may be converted to a number, thus using a numerical comparison.
str1 = "a": num1 = 4
MyResult = (str1 > num1) ' Returns an error of type "Type missmatch", this because the "string" can not be converted to a number.
|
As we can see, you may compare almost anything in VBA. Knowing what the outcome of each comparison is, will give you a great advantage when coding.
In addition to the operators above, there are two more comparison operators, Like and Is. They have some special functionalities, and were therefore not described alongside the others.
The Is-operator is used to combine two reference variables. If both objects refer to the same object, the comparison returns True; if they do not, it returns False. An example is shown below:
| Code Block |
|---|
|
Dim A As ThisWorkbook ' A is declared as a valid object in order for "Is" to work.
Dim B, C, myResult
Set C = A ' C refers to object A.
Set B = A ' B also refers to object A.
myResult = C Is B ' Returns True, since both variabels refer to the same object. |
The Like-operator is used to compare strings. Its syntax is "result = string Like pattern", and it returns True if the string matches the pattern. If not, it returns False, and if either is Null, it returns Null.
| Characters in pattern | Matches in string |
|---|
| ? | Any single character |
| * | Zero or more characters. |
| # | Any single digit (0-9). |
| [ charlist ] | Any single character in charlist. |
| [ !charlist ] | Any single character not in charlist. |
| Code Block |
|---|
|
Dim myResult
myResult = "I" Like "[A-Z]" ' Returns True, since "I" is between "A" and "Z"
myResult = "i" Like "[A-Z]" ' Returns False, since lowercase characters are places elsewhere in the ASCII-system
myResult = "a2a" Like "a#a" ' Returns True, since the pattern requires any single digit between the a's.
myResult = "a5" Like "a #" ' Returns False, since the pattern has a space between "a" and "#", which is not in the string
myResult = "ab" Like "a*b" ' Returns True, since the * requires zero or more characters.
myResult = "a*b" Like "a[*]b" ' Returns True, since the pattern requires a single "*" between "a" and "b".
myResult = "a**b" Like "a[*]b" ' Returns False.
myResult = "aBBBa" Like "a*a" ' Returns True. |
| Expand |
|---|
|
| Expand |
|---|
| title | Concatenation operators (used to combine strings) |
|---|
|
Explicit vs implicit variable declaration
When writing larger pieces of code, knowing the difference between explicit and implicit variable declaration may save you several hours of debugging.
| Expand |
|---|
| title | Explicit vs implicit variable declaration |
|---|
|
Before we look at the different variable declarations, we need to establish the difference between the words explicit and implicit.
- explicit is defined as: "stated clearly and in detail, leaving no room for confusion or doubt."
- implicit is defined as: "implied, rather than expressly stated"
When coding in Visual Basic, you are by default not obligated to declare your variables before using them. This an example of an implicit variable declaration.
| Code Block |
|---|
|
MyVariable = 10 |
When doing so, VBA will automatically declare it as a variant type, provided that the variable has not been declared before. This is a nice feature, but may cause trouble when writing larger scripts. Let's look at an example:
We have already written a lot of code using the variable "MyVariable". If we were to misspell "MyVariable" with "MyVaiable" once, VBA will think of it as a new variable, and declare it automatically, thus not informing us of our misspell.
| Code Block |
|---|
|
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 |
To 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.
| Code Block |
|---|
|
Option Explicit
Sub 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 Sub |
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 RemovedThe Format function is a helpful tool when formatting strings to your preference. Since Microsoft already has made a really in-depth explanation including some examples, we have chosen to only show you some additional examples here.
| Code Block |
|---|
| language | vb |
|---|
| title | Format function examples |
|---|
| collapse | true |
|---|
|
myVar = Format(50000)
'myVar is now equal to the String "50000".
myVar = Format(50000, "Currency")
'myVar is now equal to the String "kr 50 000,00".
myVar = Format(50000, "#,##0.0")
'myVar is now equal to the String "50,000.0".
myVar = Format(0.88, "Percent")
'myVar is now equal to the String "88.00%".
myVar = Format(0.88, "0.0")
'myVar is now equal to the String "0.9".
myVar = Format("Ola Nordmann", ">")
'myVar is now equal to the String "OLA NORDMANN".
myVar = Format("Ola Nordmann", "<")
'myVar is now equal to the String "ola nordmann".
myVar = Format("123456789", "@@@-@@@-@@@")
'myVar is now equal to the String "123-456-789".
|
