In Excell VBA the set variable change without clear reason

I

Invisibleman

New Member
Joined
Sep 16, 2015
Messages
16
Dear all,

I have a problem with the VBA in excel, the full problem, I have added below as word document.
Word document

I have an issue and I have no idea why it is happening and also no idea how to solve it.

For my excel do I have a couple of sheets with a single chart and 1 sheet with multiple charts
Chart.jpg

Each chart is called PIE07, PIE08, PIE09, PIE10, BAR11 and BAR12. I started with 07 as the other sheets are charts 01-06. Those left 2 in the code I use them to get the integer number 7, 8, 9, 10, 11, 12. That will used to get my own color index.

Then I have a sheet with all colors and per chart 2-4 columns, representing the bar/pie point and label. And in the a-column the number that is used to get my own color index in a variables (Chart number, bar/point number & label). Where the problem I have is with PIE08, which got the color index 36 & 39.
Index.jpg

Then for testing I have the sheet to see for each chart if the colors do match with that what I want. And also mentioned the color code and color in there.

As you can see the PIE08 is blue and green. However, all charts work perfect, except for the 8, as you can see above the PIE08 is taking the colors from the BAR11 (192 & 24704) even it should be the blue (9063466) and green (4631663), as you may see in this screen.
Testing.jpg

Now as I wanted to get 4 colors in the same range for the gradient, in my code I take the number index and run thru a different procedure to get the 4 colors in a variable.

This will call the procedure to get the 4 colors. As you can see below the chart number is 8 representing the PIE08
Call.jpg

In the end of the 4 color procedure, it will show indeed the correct color code 9063466, representing the blue. . Below I have set a test to see if this was still okay.
Test Value.jpg

So far so good, but then the strange thing is happening, when it jumps out this procedure and go back to set the color, this varBarColors (1, intchart, intline) becomes 192, without anything could happen in between.
Wrong Result.jpg

And as I mentioned, all others working perfect, except this PIE08. I have try and deleted this PIE and created a new one, but it still takes the wrong color.

Can anybody explain to me or help me, how this can happen and what I can do to avoid this?

Just so you know, the intline and fnpoint are all 1.

Thanks in advance.
Hans

Below the code that I use that in the end brings the wrong result.

VBA Code: 
Dim test As String
Dim btChartNumber As Integer
    For PuFn = 7 To 12
    test = Chart_Overview.ChartObjects(PuFn - 6).Name
    btChartNumber = CSng(Mid(Chart_Overview.ChartObjects(PuFn - 6).Name, 4, 2))
     Chart_Overview.ChartObjects(PuFn - 6).Select
   If btChartNumber = 8 Then Stop
        For prifn = 1 To Chart_Overview.ChartObjects(PuFn - 6).Chart.SeriesCollection.Count
            With Chart_Overview.ChartObjects(PuFn - 6).Chart.SeriesCollection(prifn)
                'Type -4102 = Pie
                If .Type = -4102 Then
                    For fnPoints = 1 To Chart_Overview.ChartObjects(PuFn - 6).Chart.SeriesCollection(prifn).Points.Count
                    
                        'To set the 4 type of colors for gradient
                        Call SetChartColor(byteBAR(btChartNumber, fnPoints), byteBarLABEL(btChartNumber, fnPoints), prifn, prifn, 4)
                       
                        .Points(fnPoints).Interior.Color = varBarColors(1, btChartNumber, fnPoints)
                        .Points(fnPoints).Format.Fill.ForeColor.Brightness = varColorSettings(btChartNumber, 4)
                       
                        If byteBarLABEL(btChartNumber, prifn) = Empty Then
                            .HasDataLabels = False
                        Else
                            .HasDataLabels = True
                        End If
                        
                        If .HasDataLabels = True Then
                         
                            .Points(fnPoints).DataLabel.Font.Color = varLabelColors(1, btChartNumber, fnPoints)
                           
                        End If
                    Next
                End If
                
                'Type 3 = BAR
                If .Type = 3 Then
                    Call SetChartColor(byteBAR(btChartNumber, prifn), byteBarLABEL(btChartNumber, prifn), PuFn, prifn, 2)
                    .Format.Fill.ForeColor.RGB = varBarColors(1, btChartNumber, prifn)
                    .Border.Color = vb3DDKShadow
                    If .Fill.Pattern < 0 Then
                        .Format.Fill.ForeColor.RGB = varBarColors(1, btChartNumber, prifn)
                        .Border.Color = vb3DDKShadow
                        If .Fill.Pattern < 0 Then
                            .Format.Fill.TwoColorGradient Left(varColorSettings(6, 2), 2), 1
                            .Format.Fill.GradientStops.Insert varBarColors(1, btChartNumber, prifn), varColorSettings(7, 2)
                            .Format.Fill.GradientStops.Insert varBarColors(2, btChartNumber, prifn), varColorSettings(8, 2)
                            .Format.Fill.GradientStops.Insert varBarColors(2, btChartNumber, prifn), varColorSettings(9, 2)
                            .Format.Fill.GradientStops.Insert varBarColors(4, btChartNumber, prifn), varColorSettings(10, 2)
                            .Format.Fill.ForeColor.Brightness = varColorSettings(5, 2)
                        End If
                    End If
                    
                    If byteBarLABEL(btChartNumber, prifn) = Empty Then
                        .HasDataLabels = False
                    Else
                        .HasDataLabels = True
                    End If
                    
                    If .HasDataLabels = True Then
                        .DataLabels.Interior.Color = varLabelColors(1, btChartNumber, prifn)
                        .DataLabels.Border.Color = vb3DDKShadow
                        .DataLabels.NumberFormat = New_Settings_ChartColors.Range("C113").NumberFormat
                        .DataLabels.Font.Color = varFontColors(btChartNumber, prifn)
                        .DataLabels.Format.Fill.OneColorGradient Left(varColorSettings(6, 5), 2), 1, varColorSettings(7, 5)
                        .DataLabels.Format.Fill.ForeColor.Brightness = varColorSettings(5, 5)
                    End If
                End If
                
                'Type 4 = Line
                If .Type = 4 Then
                    Call SetChartColor(ByteLINE(btChartNumber, prifn), byteLineLABEL(btChartNumber, prifn), PuFn, prifn, 3)
                    .Format.Line.ForeColor.RGB = varBarColors(1, btChartNumber, prifn)
                    
                    If byteLineLABEL(btChartNumber, prifn) = Empty Then
                        .HasDataLabels = False
                    Else
                        .HasDataLabels = True
                    End If
                    
                    If .HasDataLabels = True Then
                        .DataLabels.Interior.Color = varLabelColors(1, btChartNumber, prifn)
                        .DataLabels.Border.Color = vb3DDKShadow
                        .DataLabels.NumberFormat = New_Settings_ChartColors.Range("C112").NumberFormat
                        .DataLabels.Font.Color = varFontColors(btChartNumber, prifn)
                        .DataLabels.Format.Fill.OneColorGradient Left(varColorSettings(6, 5), 2), 1, varColorSettings(7, 5)
                        .DataLabels.Format.Fill.ForeColor.Brightness = varColorSettings(5, 5)
                    End If
                End If
            End With
        Next
        Chart_Overview.ChartObjects(PuFn - 6).Select
        DoEvents
    Next
    varChartColors = New_Settings_ChartColors.Range("A3:AL94")
    
    With New_Settings_ColorsOverview
        .Range("AM3:AP11, AR3:AV11").ClearContents
        .Range("AM3:AP11, AR3:AV11").Interior.Color = -4142
        varGainLossColors = .Range("AK3:AV11")
        
        For PuFn = 1 To 9
            Call SetChartColor(varGainLossColors(PuFn, 2), varGainLossColors(PuFn, 8), PuFn, 1, 3)
            .Range("AM" & PuFn + 2).Value2 = varBarColors(1, PuFn, 1)
            .Range("AM" & PuFn + 2).Interior.Color = varBarColors(1, PuFn, 1)
            .Range("AN" & PuFn + 2).Value2 = varBarColors(2, PuFn, 1)
            .Range("AN" & PuFn + 2).Interior.Color = varBarColors(2, PuFn, 1)
            .Range("AO" & PuFn + 2).Value2 = varBarColors(3, PuFn, 1)
            .Range("AO" & PuFn + 2).Interior.Color = varBarColors(3, PuFn, 1)
            .Range("AP" & PuFn + 2).Value2 = varBarColors(4, PuFn, 1)
            .Range("AP" & PuFn + 2).Interior.Color = varBarColors(4, PuFn, 1)
            
            .Range("AS" & PuFn + 2).Value2 = varLabelColors(1, PuFn, 1)
            .Range("AS" & PuFn + 2).Interior.Color = varLabelColors(1, PuFn, 1)
            .Range("AT" & PuFn + 2).Value2 = varLabelColors(2, PuFn, 1)
            .Range("AT" & PuFn + 2).Interior.Color = varLabelColors(2, PuFn, 1)
            .Range("AU" & PuFn + 2).Value2 = varLabelColors(3, PuFn, 1)
            .Range("AU" & PuFn + 2).Interior.Color = varLabelColors(3, PuFn, 1)
            .Range("AV" & PuFn + 2).Value2 = varLabelColors(4, PuFn, 1)
            .Range("AV" & PuFn + 2).Interior.Color = varLabelColors(4, PuFn, 1)
            
            .Range("AR" & PuFn + 2).Value2 = varChartColors(varGainLossColors(PuFn, 7), 4)
        Next
        
        Dim col As Byte, src As Byte
        
        For prifn = 1 To 32
            .Cells(1, 50 + (2 * prifn)).Value2 = New_Settings_ColorPicker.Cells(2, prifn).Value2
            If New_Settings_ColorPicker.Cells(204, prifn).Value2 = "Changed" Or .Range("AL" & prifn + 13).Value2 & "," & .Range("AM" & prifn + 13).Value2 & "," & .Range("AN" & prifn + 13).Value2 <> .Range("AO" & prifn + 13).Value2 Then
                .Range("AO" & prifn + 13).Value2 = "Changed"
            End If
            
            If .Range("AO" & prifn + 13).Value2 = "Changed" Then
                .Range(Cells(3, 50 + (2 * prifn)), Cells(203, 51 + (2 * prifn))).ClearContents
                .Range(Cells(3, 50 + (2 * prifn)), Cells(203, 51 + (2 * prifn))).Interior.Color = -4142
            End If
        Next
        
        For PuFn = 3 To 203
            col = 14
            src = 0
            For prifn = 1 To 32
                src = src + 1
                If .Range("AM" & prifn + 13).Value2 = "Yes" And .Range("AO" & prifn + 13).Value2 = "Changed" Then
                    If .Range("AN" & prifn + 13).Value2 = "From Dark to Light" Then
                        col = .Range("AX" & PuFn).Value2 + 2
                    Else
                        col = .Range("AY" & PuFn).Value2 + 2
                    End If
                .Cells(PuFn, 50 + (2 * prifn)).Value2 = New_Settings_ColorPicker.Cells(col, src).Value2
                .Cells(PuFn, 50 + (2 * prifn)).Interior.Color = New_Settings_ColorPicker.Cells(col, src).Value2
                .Cells(PuFn, 51 + (2 * prifn)).Value2 = New_Settings_ColorPicker.Cells(col, src).Font.Color
                
                End If
            Next
        Next
        
        For PuFn = 14 To 45
            .Range("AO" & PuFn).Value2 = .Range("AL" & PuFn).Value2 & "," & .Range("AM" & PuFn).Value2 & "," & .Range("AN" & PuFn).Value2
            New_Settings_ColorPicker.Cells(204, PuFn - 13).Value2 = ""
        Next
    End With

End Sub

Private Function SetChartColor(iBar As Variant, iLabel As Variant, intChart As Integer, intLine As Integer, byteChart As Byte)
    
    On Error Resume Next
    
    Dim intLabel As Long, intbar As Long, test As Variant
    
       If Not IsEmpty(iLabel) Then
         varFontColors(intChart, intLine) = varChartColors(iLabel, 4)
        End If
      
    If iBar <> 0 Then
        intbar = varChartColors(iBar, 3)
        puRED = (intbar Mod 256) * varColorSettings(1, byteChart)
        puGREEN = ((intbar \ 256) Mod 256) * varColorSettings(1, byteChart)
        puBLUE = (intbar \ 65536) * varColorSettings(1, byteChart)
        varBarColors(1, intChart, intLine) = RGB(puRED, puGREEN, puBLUE)
        
        puRED = (intbar Mod 256) * varColorSettings(2, byteChart)
        puGREEN = ((intbar \ 256) Mod 256) * varColorSettings(2, byteChart)
        puBLUE = (intbar \ 65536) * varColorSettings(2, byteChart)
        varBarColors(2, intChart, intLine) = RGB(puRED, puGREEN, puBLUE)
        
        puRED = (intbar Mod 256) * varColorSettings(3, byteChart)
        puGREEN = ((intbar \ 256) Mod 256) * varColorSettings(3, byteChart)
        puBLUE = (intbar \ 65536) * varColorSettings(3, byteChart)
        varBarColors(3, intChart, intLine) = RGB(puRED, puGREEN, puBLUE)
        
        puRED = (intbar Mod 256) * varColorSettings(4, byteChart)
        puGREEN = ((intbar \ 256) Mod 256) * varColorSettings(4, byteChart)
        puBLUE = (intbar \ 65536) * varColorSettings(4, byteChart)
        varBarColors(4, intChart, intLine) = RGB(puRED, puGREEN, puBLUE)
        
    Else
        varBarColors(1, intChart, intLine) = -4142
        varBarColors(2, intChart, intLine) = -4142
        varBarColors(3, intChart, intLine) = -4142
        varBarColors(3, intChart, intLine) = -4142
    End If
    
    If iLabel <> 0 Then
        intLabel = varChartColors(iLabel, 3)
        puRED = (intLabel Mod 256) * varColorSettings(1, 5)
        puGREEN = ((intLabel \ 256) Mod 256) * varColorSettings(1, 5)
        puBLUE = (intLabel \ 65536) * varColorSettings(1, 5)
        varLabelColors(1, intChart, intLine) = RGB(puRED, puGREEN, puBLUE)
        
        puRED = (intLabel Mod 256) * varColorSettings(2, 5)
        puGREEN = ((intLabel \ 256) Mod 256) * varColorSettings(2, 5)
        puBLUE = (intLabel \ 65536) * varColorSettings(2, 5)
        varLabelColors(2, intChart, intLine) = RGB(puRED, puGREEN, puBLUE)
        
        puRED = (intLabel Mod 256) * varColorSettings(3, 5)
        puGREEN = ((intLabel \ 256) Mod 256) * varColorSettings(3, 5)
        puBLUE = (intLabel \ 65536) * varColorSettings(3, 5)
        varLabelColors(3, intChart, intLine) = RGB(puRED, puGREEN, puBLUE)
        
        puRED = (intLabel Mod 256) * varColorSettings(4, 5)
        puGREEN = ((intLabel \ 256) Mod 256) * varColorSettings(4, 5)
        puBLUE = (intLabel \ 65536) * varColorSettings(4, 5)
        varLabelColors(4, intChart, intLine) = RGB(puRED, puGREEN, puBLUE)
    Else
        varLabelColors(1, intChart, intLine) = -4142
        varLabelColors(2, intChart, intLine) = -4142
        varLabelColors(3, intChart, intLine) = -4142
        varLabelColors(4, intChart, intLine) = -4142
    End If
    
    If iLabel = 0 Then
        varLabelColors(1, intChart, intLine) = varBarColors(1, intChart, intLine)
        varLabelColors(2, intChart, intLine) = varBarColors(2, intChart, intLine)
        varLabelColors(3, intChart, intLine) = varBarColors(3, intChart, intLine)
        varLabelColors(4, intChart, intLine) = varBarColors(4, intChart, intLine)
    End If
test = varBarColors(1, intChart, intLine)
End Function
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
Sort by date Sort by votes
Did you step through the code and use either the Watch window or Locals window and see when the variable value changes? You don't show where varBarColors is declared so I'm guessing that it, or one or more of its parameters are at the module level and something is being changed as a result.
 
Upvote 0
Hello Micron,

Yes, but there is nothing to see. The step when it's at the end of the procedure (Correct value; test = varBarColors(1, intChart, intLine). then the first step after this is showing the result and the result (.Points(fnPoints).Interior.Color = varBarColors(1, btChartNumber, fnPoints)_ is wrong. There is no step in between, so I can't see in the Watch where it is going wrong and also why only with number 8.
 
Upvote 0
Then I guess I don't know what you mean by
Invisibleman said:
when it jumps out this procedure and go back to set the color
Click to expand...
because if it's "jumping out" of a procedure or a loop and going back to where it was before it entered that procedure/loop then I'd say there is something to see. I'm sticking with the idea of a variable being in the scope of 2 or more procedures as I've said. However, I admit that if it's only a problem for 8 then that may not be the issue. Maybe if you posted a copy of the wb on a file share we might be able to spot the problem.
 
Upvote 0
Hello Micron,

After days, I suddenly found the error.

Call SetChartColor(byteBAR(btChartNumber, fnPoints), byteBarLABEL(btChartNumber, fnPoints), btChartNumber, prifn, 4) < Here I changed the 1st Prifn to btChartNumber

.Points(fnPoints).Interior.Color = varBarColors(1, btChartNumber, prifn) <Here I changed the fnPoints to prifn

And this seems to work. So stupid to spend days and not finding and then suddenly. You'll find.

Thanks for the help.

Regards,
Hans
 
Upvote 0
Solution
Glad you solved it! You should mark your thread as solved to take it off the list of threads needing a solution.
 
Upvote 0
You must log in or register to reply here.

Similar threads

togenmoser
  • Solved
Help with VBA code error "For variable already in use"
2 3
Replies
22
Views
1K
MARK858
MARK858
montecarlo2012
  • Question Question
change display from horizontal to vertical
Replies
2
Views
159
montecarlo2012
montecarlo2012
S
  • Solved
Copying Formulas with Formula.R1C1 Results in Rows and Columns Incrementing by 2
Replies
5
Views
643
Alex Blakenburg
A
B
  • Question Question
Excel VBA Searching Unique Variables to Match against Exported Schedule Files to Determine Date
Replies
1
Views
480
sijpie
sijpie
L
  • Locked
  • Question Question
How set range for chart in powerpoint created from vba
Replies
0
Views
138
Legacy 519704
L

Forum statistics

Threads
1,225,216
Messages
6,183,632
Members
453,177
Latest member
GregL65

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top