header image
Toggle all Charts between Logarithmic and Linear Scale
September 1st, 2015 under Excel. [ Comments: none ]

I had multiple chart in one Excel Worksheet and wanted to change the scale of the x-axis and y-axis to the logarithmic scale. The following macro toggles between the linear and logarithmic scale for all charts:

1
2
3
4
5
6
7
8
9
10
11
12
13
Sub ChartsToggle()
    For i = 1 To ActiveSheet.ChartObjects.Count      
      Application.DisplayAlerts = False
      If ActiveSheet.ChartObjects(i).Chart.Axes(xlCategory).ScaleType = xlLinear Then
        ActiveSheet.ChartObjects(i).Chart.Axes(xlCategory).ScaleType = xlLogarithmic
        ActiveSheet.ChartObjects(i).Chart.Axes(xlValue).ScaleType = xlLogarithmic
      Else
        ActiveSheet.ChartObjects(i).Chart.Axes(xlCategory).ScaleType = xlLinear
        ActiveSheet.ChartObjects(i).Chart.Axes(xlValue).ScaleType = xlLinear
      End If
    Next i
  Application.DisplayAlerts = True
End Sub
Sub ChartsToggle()
    For i = 1 To ActiveSheet.ChartObjects.Count      
      Application.DisplayAlerts = False
      If ActiveSheet.ChartObjects(i).Chart.Axes(xlCategory).ScaleType = xlLinear Then
        ActiveSheet.ChartObjects(i).Chart.Axes(xlCategory).ScaleType = xlLogarithmic
        ActiveSheet.ChartObjects(i).Chart.Axes(xlValue).ScaleType = xlLogarithmic
      Else
        ActiveSheet.ChartObjects(i).Chart.Axes(xlCategory).ScaleType = xlLinear
        ActiveSheet.ChartObjects(i).Chart.Axes(xlValue).ScaleType = xlLinear
      End If
    Next i
  Application.DisplayAlerts = True
End Sub

Note the

1
Application.DisplayAlerts = False
Application.DisplayAlerts = False
. This prevents Excel from opening a dialog box, for every chart, informing you that negative values cannot be displayed. The dialog is just shown once at the end. I had to put this line inside the for-loop because it got changed back to true after the chart modification.


Last value of a data range
June 25th, 2015 under Excel. [ Comments: none ]

I was faced with the problem of finding the last value of a data range in Excel. Assume the range resides in the column A. Today there are 50 values in A1:A50. Over the time new values will be added and the goal is to get the most recent (the last) value.

The solution is the INDEX() function. It allows you to select a value in a range by index. Therefore, the following solves my issue if I know there won’t be more than 100 values

1
INDEX(A1:A100;COUNT(A1:A100))
INDEX(A1:A100;COUNT(A1:A100))

Otherwise

1
INDEX(A:A;COUNT(A:A))
INDEX(A:A;COUNT(A:A))
gives the answer as long as there aren’t any empty cells between the values.