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.


 


Flickr
Scuba DivingEigerEiger, MönchKleine Scheidegg

View All Photos

Recent Posts
Quotes
Boyd: God says you look for excuses to be alone.
Dr. House: See, that is exactly the kind of brilliance that sounds deep, but you could say it about any person who doesn't pine for the social approval of everyone he meets - which you were cleverly able to deduce about me by not being a moron. Next time, tell God to be more specific.”
IP
[FSF Associate Member]
[FSF Associate Member]
Michael Pfeuti
Profil von Michael Pfeuti auf LinkedIn anzeigen
profile for Michael Pfeuti at Stack Overflow, Q&A for professional and enthusiast programmers