Graphique (abscisse) : interprétation d'un variant.

Charly88

XLDnaute Occasionnel
Graphe, abscisse : variant [Bug SP1, partiellement résolu]

Le message d'origine :

Bonjour le forum, je continue le développement de mon appli VB, sous 2003, sur les graphes et j'ai un ultime souci sous 2007 : il ne comprend pas la formulation de mon abscisse dont voici un code :
Code:
Dim Abscisse As Variant

blabla
       Abscisse = Carma.Perio.List(p, 0)
Else
       Abscisse = Abscisse & "," & Carma.Perio.List(p, 0)

blabla

     For i = 1 To CbDeSéries
                ActiveChart.SeriesCollection(i).XValues = Abscisse
     Next i

Carma est une USF, perio une listbox, rien de révolutionnaire et 2003 n'a pas problème pour comprendre ce que je lui dis alors que 2007 n'affiche rien.

J'ai essayé de remplacer la , par le ; mais rien à faire, je crois que ça se traduit par par =T1,T2,T3,T4 dans les coordonnées de l'abscisse mais rien ne s'affiche ce qui est normal vu qu'il devrait rajouter les {} et \.
L'aide de 2007 sur XValues ressemble trait pour trait à celle de 2003.

Au pire, je peux la lui faire en texte mais il me semble que j'étais justement passé au variant pour une bonne raison qui maintenant m'échappe.

Merci d'avance à qui pourrait éclairer ma lanterne.


La solution sur ce lien (anglophone). La fonction du tout dernier message.

Comme indiqué dans le titre, il s'agit d'un bug apparemment lié à SP1, le premier message explique que si la variant n'a qu'une donnée, ça passe, au-delà ça casse. Et celui qui a levé le lièvre a rédigé une fonction corrective.
Dans mon cas précis, je n'ai pas encore testé, je vais d'abord tenter la solution de secours texte et aviser le cas échéant.

La fonction de Lyndess :
Code:
Function RealXValues(srs As Series) As Variant
'
' Receives a chart series and returns the proper X-values in that series
' as a (base 1) variant array.  Normally, the X-values for a series can be
' retrieved using the Series.XValues property (which, if used with an index
' value, returns a specific X-value for that point).  However, SP1 for Excel
' 2007 introduced a bug where a call to the XValues property for a scatter
' plot with two or more series would simply return the index number and not
' the X-value.  For example, in a series "srs" with X-values {5.5, 8.7, 6.9},
' a call to srs.XValues(2) would return 2, not 8.7.  So far, this bug has been
' shown to occur in scatter plots with two or more series after applying SP1
' to Excel 2007.  For more information about this bug, see:
' http://www.excelforum.com/showthread.php?t=632572.
'
' Note that his workaround function does NOT check for the bug; it assumes that
' the developer will implement such checks in his/her code.
'
' This function works by reading the x-values parameter in the series function,
' and then using that formula to look up the original x-values.
'
' The SERIES function has four parameters (five in the case of bubble charts).
' The first two are optional parameters, but commas are still required in the formula;
' the second parameter is the X-values reference.
'
' This workaround can accommodate SERIES formulas that use named ranges
' or arrays of data (i.e., no range references), in addition to contiguous
' ranges and non-contiguous ranges (i.e., ranges with multiple areas).
'
' For information on the SERIES function, see:
' http://office.microsoft.com/en-us/excel/HA011098011033.aspx
'
' This function initially worked by using the VBA "Split" function (see
' http://j-walk.com/ss/excel/tips/tip93.htm) on the SERIES formula,
' but this later proved to be too simplistic (as it did not capture all
' the permutations of formats that can occur in the SERIES formula).
' "Split" still proved useful in one case, though, so I am still including
' the reference.
'
' Information on returning arrays from functions (esp. 2D arrays):
' http://www.cpearson.com/excel/returningarraysfromvba.aspx
'
' Additional information that was also helpful:
' http://peltiertech.com/Excel/Charts/ChgSrsFmla.html
' http://peltiertech.com/Excel/ChartsHowTo/DelinkChartData.html
'

  ' Variables
  Dim strSeriesFormula As String      ' Working string that initially contains the entire SERIES formula
  Dim rngXValues As Range             ' The range that contains the X-values
         
         
  ' Print current x-values
  ' Dim intCurrentPoint As Integer
  ' Dim strXValues As String
  ' For intCurrentPoint = 1 To srs.Points.Count
  '   strXValues = strXValues & srs.XValues(intCurrentPoint) & ","
  ' Next intCurrentPoint
  ' strXValues = Left(strXValues, Len(strXValues) - 1)
  ' Debug.Print "Current XValues: " & strXValues
         
         
  ' Store full SERIES formula in a string
  strSeriesFormula = srs.Formula
  
  ' Drop "=SERIES(" prefix
  strSeriesFormula = Right(strSeriesFormula, Len(strSeriesFormula) - InStr(strSeriesFormula, "("))

  ' If first character is a quotation mark, then the title could contain one
  ' or more commas; find the next comma after an even number of quotation marks
  ' by repeatedly "chopping off" the beginning part of the formula string between
  ' two quotation marks.  (For example, the SERIES formula could be:
  '   =SERIES("My ""series"" title, with quotes and comma",...)
  ' resulting in a series name of:
  '  My "series" title, with quotes and comma
  ' Note: Chr(34) is the quotation mark character
  Do While (Left(strSeriesFormula, 1) = Chr(34))
    strSeriesFormula = Right(strSeriesFormula, Len(strSeriesFormula) - InStr(2, strSeriesFormula, Chr(34)))
  Loop
  
  ' Chop off the first parameter, including comma
  strSeriesFormula = Right(strSeriesFormula, Len(strSeriesFormula) - InStr(strSeriesFormula, ","))
  
  ' Second parameter contains the X-values; it could be a range reference,
  ' a named range, or an array of values.  If the second parameter is a
  ' range reference, and the reference contains discontiguous areas (i.e.,
  ' more than one range is references, separated with commas), then they
  ' will be separated by commas, and the entire reference will be enclosed
  ' in parentheses.  If the second parameter is an array of values, then it
  ' will be enclosed in curly braces.  In all other cases (?), the second
  ' parameter terminates at the next comma.
  
  ' Check the first character of the current formula string
  Select Case Left(strSeriesFormula, 1)
    
    Case "{"    ' Array of values: return array and exit function
      ' Strip the outer braces and obtain the string of values
      strSeriesFormula = Mid(strSeriesFormula, 2, InStr(strSeriesFormula, "}") - 2)
      
      ' Temporary arrays used to convert from base 0 to base 1
      Dim varTemp1XValues As Variant, varTemp2XValues As Variant
      
      ' Use Split function to convert string of x-values to an array
      ' NOTE: By default, resulting array will be base 0 (unless Option Base 1 is specified)
      varTemp1XValues = Split(strSeriesFormula, ",")
      
      ' Set second temporary array to base 1 (same size as first temporary array)
      ReDim varTemp2XValues(1 To (UBound(varTemp1XValues) - LBound(varTemp1XValues) + 1))
      
      ' Loop through arrays and transfer values
      Dim lngCurrent1XValue As Long, lngCurrent2XValue As Long    ' Loop counters
      lngCurrent2XValue = 1                                       ' Initialize second loop counter to 1
      For lngCurrent1XValue = LBound(varTemp1XValues) To UBound(varTemp1XValues)
        varTemp2XValues(lngCurrent2XValue) = varTemp1XValues(lngCurrent1XValue)
        lngCurrent2XValue = lngCurrent2XValue + 1
      Next lngCurrent1XValue
      
      ' Return the array
      RealXValues = varTemp2XValues
      
    Case "("    ' Range with multiple areas: create the range, read the values
      
      ' Obtain the string that contains the multi-range reference (including enclosing parentheses)
      strSeriesFormula = Left(strSeriesFormula, InStr(strSeriesFormula, ")"))
      
      ' Initialize variant array to hold x-values
      Dim varXValues As Variant
      
      ' Create Range object based on formula
      Set rngXValues = Range(strSeriesFormula)
      
      ' Set proper size for array
      ReDim varXValues(1 To rngXValues.Count)
      
      ' Loop through each cell and add its value to the array
      ' (Because the range contains multiple areas, we cannot simply set
      ' the range equal to the array, and instead must loop through each
      ' cell and read its value)
      Dim cell As Range
      Dim lngCellNumber As Long           ' Loop counter
      lngCellNumber = LBound(varXValues)  ' Initialize the loop counter
      For Each cell In rngXValues
        varXValues(lngCellNumber) = cell.Value
        lngCellNumber = lngCellNumber + 1
      Next cell
      
      ' Return the array of x-values
      RealXValues = varXValues
      
    Case Else   ' Continguous range of cells or named range
      
      ' Obtain the string that contains the range reference
      strSeriesFormula = Left(strSeriesFormula, InStr(strSeriesFormula, ",") - 1)
      
      ' Create Range object based on formula
      Set rngXValues = Range(strSeriesFormula)
  
      ' Return array (but transpose it if original data is in a column, rather than in a row)
      ' Note the the Transpose function converts the array to a single dimension, so
      ' we can apply it twice if the original data is in a row in order to return a
      ' one-dimensional array
  
      If rngXValues.Rows.Count > 1 Then
        RealXValues = Application.Transpose(rngXValues)
      Else
        RealXValues = Application.Transpose(Application.Transpose(rngXValues))  ' Transpose twice to return 1 dimension
      End If
   
  End Select
  

  ' NOTE: What if commas, parentheses, or braces appear within a given point (a
  ' text value) in an array of values? In this case, Excel 2007 ignores the
  ' array of x-values (text values make no sense in an XY chart) and simply
  ' uses a sequence of integers (1, 2, 3, ...) as the x-values.
    
End Function

Morale de l'histoire, google est mon ami.
Au moins la solution est aussi arrivée ici...
 
Dernière édition:

Discussions similaires

Réponses
1
Affichages
119