CODEWITHSUNDEEP
excelvba
Jarom
Jarom

Reputation: 1077

Why are the input box variables switching to null after the first iteration of a loop?

I wrote some simple VBA code to update the x-axis of a lot of time-series charts in an excel document.

The issue I'm having is that after the first iteration of the loop, start_date and end_date variables become "". I'm not sure why the variables are disappearing. The macro does work for the first chart, but crashes after that because of the variable issue.

This is my code:

Sub xaxis_reset()

Dim start_date As Variant
Dim end_date As Variant
Dim ws As Integer
Dim obj As Integer

start_date = InputBox("Start Date")
end_date = InputBox("End Date")
ws = ActiveWorkbook.Worksheets.Count - 2

         For w = 1 To ws
            obj = Worksheets(w).ChartObjects.Count
                For Z = 1 To obj
                    Worksheets(w).ChartObjects(Z).Activate
                         With ActiveChart
                              .Axes(xlCategory).MinimumScale = start_date
                              .Axes(xlCategory).MaximumScale = end_date
                         End With

                Next Z
         Next w

End Sub

Thank you

Upvotes: 0

Views: 37

Answers (2)

Jon Peltier
Jon Peltier

Reputation: 6063

Aside from declaring all your variables (w, z), you have to be smart about how you pass values to the axis limits. Your input boxes provide dates as strings. The values of start_date and end_date do not become "", but for some reason the strings are only interpreted correctly the first time through the loop. And for some reason, passing strings to a chart axis makes Excel remarkably unstable.

In the code below, I use DateValue() to convert these to actual dates, and pass these into the chart.

Sub xaxis_reset()
  Dim start_date As Variant
  Dim end_date As Variant
  Dim w As Long, ws As Long
  Dim z As Long, obj As Long

  start_date = InputBox("Start Date")
  end_date = InputBox("End Date")
  ws = ActiveWorkbook.Worksheets.Count - 2

  For w = 1 To ws
    obj = Worksheets(w).ChartObjects.Count
    For z = 1 To obj
      Worksheets(w).ChartObjects(z).Activate
      With ActiveChart
        .Axes(xlCategory).MinimumScale = DateValue(start_date)
        .Axes(xlCategory).MaximumScale = DateValue(end_date)
      End With
    Next z
  Next w
End Sub

Upvotes: 1

Vincent G
Vincent G

Reputation: 3188

I've adjusted a little your code (see below), and it seems to be a bug in how Excel set the properties (not always, but often modifying the variable used to set them to other values). A workaround (used in the code below) would be to use a buffer variable.

Sub xaxis_reset()

    Dim start_date As Variant, start_buffer As Variant
    Dim end_date As Variant, end_buffer As Variant
    Dim w As Integer
    Dim obj As ChartObject

    While Not IsDate(start_date): start_date = InputBox("Start Date"): Wend
    While Not IsDate(end_date): end_date = InputBox("End Date"): Wend

    On Error Resume Next
    For w = 1 To ActiveWorkbook.Worksheets.Count - 2
        For Each obj In ActiveWorkbook.Worksheets(w).ChartObjects
            start_buffer = start_date: end_buffer = end_date
            Debug.Print w, start_date, end_date, start_buffer, end_buffer, (start_date = start_buffer), (end_date = end_buffer)
            obj.Chart.Axes(xlCategory).MinimumScale = start_buffer
            Debug.Print w, start_date, end_date, start_buffer, end_buffer, (start_date = start_buffer), (end_date = end_buffer)
            obj.Chart.Axes(xlCategory).MaximumScale = end_buffer
            Debug.Print w, start_date, end_date, start_buffer, end_buffer, (start_date = start_buffer), (end_date = end_buffer)
        Next obj
        Set obj = Nothing
    Next w
End Sub

Upvotes: 1

Related Questions