Graphe, abscisse : variant [Bug SP1, partiellement résolu]
Le message d'origine :
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 :
Morale de l'histoire, google est mon ami.
Au moins la solution est aussi arrivée ici...
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: