Search code examples
excelvbams-office

Excel VBA - Creating Scatter Plot Issues

I haven't used excel VBA before, and I'm finding it a bit frustrating compared to octave/MATLAB..

Anywho, at the moment I'm trying to do something pretty basic.

  1. Take user defined cell values and assign them to variables within VBA code
  2. Create an output array and an x-axis array to record values to plot
  3. For loop through an equation while recording the values and inserting them into respective arrays
  4. scatterplot the output array vs the x-axis array

The code I've done so far is below. The scatter plot attempt creates the X-axis correctly from what I can tell, but I only get 1 point on the y-axis, which is the last data value in the Ion() array.

What am I missing to plot the entire Ion array?

Private Sub Generate_Ip_vs_time_Click()
  
 Dim ws As Worksheet
 
 Dim Tsw As Double
 Dim Don As Double
 Dim Lp As Double
 Dim Vin_min As Double
 Dim Imax As Double
 Dim upperBound As Double
 Dim i As Double
 
 Tsw = Range("C38").Value
 Don = Range("T26").Value
 Lp = Range("O29").Value / 1000000
 Vin_min = Range("C32").Value
 Imax = Range("O32").Value
 lowerbound = 0.0000001
 upperBound = Tsw

Dim Ion() As Double
Dim Xvalue_x() As Double

ReDim Ion(lowerbound To upperBound)
ReDim Xvalue_x(lowerbound To upperBound)

' Calculate Primary Current (Up slope)
 For i = lowerbound To upperBound Step 0.00000001
    Ion(i) = Vin_min / Lp * i
    Xvalue_x(i) = i
    Debug.Print Xvalue_x(i)
        If Ion(i) > Imax Then
        Exit For
        End If
 Next i

  Dim MyChart As Chart
  Set MyChart = Worksheets("Sheet1").Shapes.AddChart2(227, xlXYScatter).Chart


With MyChart
 .SeriesCollection.NewSeries
 
       With .SeriesCollection(1)
            .Values = Ion
            .XValues = Xvalue_x
            .MarkerStyle = -4168
            .MarkerSize = 5
        End With
        
.HasTitle = True
.ChartTitle.Text = "Ip vs time(s)"

End With

End Sub

EDIT:

added this to my code:

ReDim Ion(lowerbound To upperBound)
ReDim Xvalue_x(lowerbound To upperBound)

' Calculate Primary Current (Up slope)
 For i = lowerbound To upperBound Step 0.00000001
    Ion(i) = Vin_min / Lp * i
    Xvalue_x(i) = i
'added the 2 debug statements below
    **Debug.Print Xvalue_x(i)
    Debug.Print Ion(i)**
        If Ion(i) > Imax Then
        Exit For
        End If
 Next i


'Added the below debug statements
**Debug.Print Tsw
Debug.Print Don
Debug.Print Lp
Debug.Print Vin_min
Debug.Print Imax**

  Dim MyChart As Chart
  Set MyChart = Worksheets("Sheet1").Shapes.AddChart2(227, xlXYScatter).Chart

What I get in the immediate window is the following:

It makes sense the two values outputting together, since those are the in the for loop. I deleted a lot of the earlier values only to show where the if statement kicks in. What doesn't make sense to me is why the 5 debug statements I added are looping as well. But, idk if that's the immediate "now" issue or not.

4.87999999999996E-06

1.30831099195709

4.88999999999996E-06

1.31099195710455

4.89999999999996E-06

1.313672922252

4.90999999999996E-06

1.31635388739945

4.91999999999996E-06

1.31903485254691

4.92999999999996E-06

1.32171581769436

0.0000125

0.4

0.000373

100

1.32024128686327

0.0000125

0.4

0.000373

100

1.32024128686327

0.0000125

0.4

0.000373

100

1.32024128686327

0.0000125

0.4

0.000373

100

1.32024128686327

The current scatterplot I'm getting is below: Ip vs Time Scatter Plot

So after listening to Tin Williams, and seeing he was right about indexing, all I did was change the following to map it out. The only issue with this is the scaling factor is hardcoded, but that can be easily changed. Code that works and gives what I need

Solution

  • Array bounds and indexes in VBA must be whole numbers. If you try to use fractional values they will get rounded, but will not raise an error:

    For example:

    Dim arr()

'setting bounds
ReDim arr(0.0000001 To 0.000005)
Debug.Print LBound(arr), UBound(arr)       '>> 0 0

ReDim arr(0.0000001 To 1.5)
Debug.Print LBound(arr), UBound(arr)       '>> 0 2

ReDim arr(0.0000001 To 1.49)
Debug.Print LBound(arr), UBound(arr)       '>> 0 1

'access by index
ReDim arr(1 To 3)
arr(1) = 1
arr(2) = 2
arr(3) = 3
Debug.Print arr(1.5) '>> 2   (banker's rounding)
Debug.Print arr(2.5) '>> 2

    Maybe try a different approach (untested)

    Private Sub Generate_Ip_vs_time_Click()
  
    Dim ws As Worksheet
    
    Dim Tsw As Double, Don As Double, Lp As Double, Vin_min As Double, Imax As Double
    Dim n As Long
    Dim i As Double, x As Double, Ion() As Double, Xvalue_x() As Double
    
    With ActiveSheet ' or some specific sheet
        Tsw = .Range("C38").Value
        Don = .Range("T26").Value
        Lp = .Range("O29").Value / 1000000
        Vin_min = .Range("C32").Value
        Imax = .Range("O32").Value
    End With
    
    x = 0.0000001
    n = 0
    Do While x <= Tsw
        n = n + 1
        ReDim Preserve Xvalue_x(1 To n)
        ReDim Preserve Ion(1 To n)
        Xvalue_x(n) = x
        Ion(n) = Vin_min / Lp * x
        If Ion(n) > Imax Then Exit Do
        x = x + 0.00000001
    Loop

    With Worksheets("Sheet1").Shapes.AddChart2(227, xlXYScatter).Chart
        With .SeriesCollection.NewSeries
            .values = Ion
            .XValues = Xvalue_x
            .MarkerStyle = -4168
            .MarkerSize = 5
        End With
        .HasTitle = True
        .ChartTitle.Text = "Ip vs time(s)"
    End With

End Sub