VBA 小知识

1. 循环

Dim i As Integer

For i = 0 To 100
    'body
Next

While i < 100
    'body
Wend

 

2. 键值数据结构

    'create dictionary object
    Set dictMembers = CreateObject("scripting.dictionary")
    'add a key
    If Not dictMembers.exists(Key) Then
        'add value
        Dim value(1 To 3) As Integer
        tempdata(1) = 0
        tempdata(2) = 0
        tempdata(3) = 0
        dictMembers.Add Key, value
    End If
    
    Dim keys()
    keys = dictMembers.keys
    
    'read dictionary
    Dim value
    For i = 0 To dictMembers.Count - 1
        Key = keys(i)
        value = dictMembers.Item(keys(i))
        Me.Cells(i + 1, 2).value = value(1)
        Me.Cells(i + 1, 3).value = value(2)
        Me.Cells(i + 1, 4).value = value(3)
    Next

3. 数据图表chart

    ‘get the first chart
    Me.ChartObjects(1).Activate
    ‘set datasource
    ActiveChart.SetSourceData Source:=Range("A8:F" & i - 1)
    While ActiveChart.SeriesCollection.Count > 2
        ‘remove useless series
        ActiveChart.SeriesCollection(1).Delete
        ActiveChart.SeriesCollection(1).Delete
        ActiveChart.SeriesCollection(1).Delete
    Wend
    'display value
    ActiveChart.SeriesCollection(1).ApplyDataLabels
    ActiveChart.SeriesCollection(2).ApplyDataLabels