我试图将选定的值从list box
inExcel
传递到图表中的图例。具体来说,我具有以下格式的某些公司的数据
我还有一个列表框globalList,其中包含可以选择的公司名称。然后,选定的公司数据将使用VBA传递到图表上。
但是,我遇到的问题在以下几节中:
初始化变量以保存在globalList中选择的值
listMax = globalList.ListCount - 1
`this creates the upper bound for the list box
For i = 0 To (globalList.ListCount - 1)
If globalList.Selected(i) = True Then
companiesSelected = companiesSelected + 1
End If
If i = listMax Then
Exit For
End If
Next i
`the above is used to retrieve the number of companies that have been selected by a user - whether =0 or > 0
Dim myLegend() As String
ReDim myLegend(0 To (globalList.ListCount - 1))
For i = 0 To (globalList.ListCount - 1)
If globalList.Selected(i) = True Then
myLegend(i) = globalList.List(i)
End If
If i = listMax Then
Exit For
End If
Next i
`this is the array object in which I intend to store company names selected in the list box.
问题是,即使上面创建了myLegend
字符串数组,它也包含了用户可能未在列表框中选择的公司的空数组项。即使我能够从数组中删除这些空项目,也会出现以下问题
将保留值从变量传递到图表
For i = 1 To companiesSelected
myChart.SeriesCollection(i).Name = myLegend(i)
Next i
这里的问题是myLegend
数组从0开始,而数组SeriesCollection
似乎从1开始。因此,我无法将所选项目的字符串值传递给图表的图例。
有人可以指出如何避免这些问题吗?提前谢谢了!
这是将所选项目提取到一个基于字符串的String数组中的代码(无空项目):
Dim i As Integer
Dim iCount As Integer
Dim myLegend() As String
iCount = 0
With globalList
ReDim myLegend(1 To .ListCount)
For i = 0 To .ListCount - 1
If .Selected(i) = True Then
iCount = iCount + 1
myLegend(iCount) = .List(i)
End If
Next i
End With
If iCount > 0 Then
ReDim Preserve myLegend(1 To iCount)
Else
ReDim myLegend(1 To 1)
myLegend(1) = "Nothing here!"
End If
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句