Excel教程|Word教程|VBA应用-ExcelOffice

如何在vba中为OnAction属性指定宏名？

2018年8月14日作者：exceloffice

暂无评论

在excel VBA中按下F2快捷键，在弹出的“对象浏览器”对话框中输入OnAction，会发现只有4个对象具有OnAction属性，如下图所示：

在vba中我们可以为Shape、CommandBarControl、CommandBarComboBox、CommandBarButton对象添加OnAction属性。

OnAction属性的作用是指定单击以上对象时要执行的vba过[……]

阅读全文

如何用vba实现聚光灯高亮行列单元格的功能？

2018年8月14日作者：exceloffice

暂无评论

在wps中有个聚光灯功能，可以实现高亮选中的单元格所在的行和列。

在excel中虽然没有内置这个功能，但是可以通过vba代码实现类似的效果。

如下动画所示：

在Sheet1模块中插入如下的Worksheet_SelectionChange事件代码，即可实现简单的聚光灯功能：

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Count = 1 Then
        Dim oWK As Worksheet
        Dim oRng As Range
        Set oWK = Excel.ActiveSheet
        Set oRng = Target.CurrentRegion
        Dim oFC As FormatCondition
        iRow = Target.Row
        iCol = Target.Column
        sF = "=OR(ROW()=" & iRow & ",COLUMN()=" & iCol & ")"
        iCol = oRng.FormatConditions.Count
        '先删除原来的条件格式
        For Each oFC In oRng.FormatConditions
            oFC.Delete
        Next
        '添加以公式为判断依据的条件格式
        Set oFC = oRng.FormatConditions.Add(xlExpression, , sF)
        With oFC
            '设置条件格式的填充色
            .Interior.Color = vbYellow
        End With
    End If
End Sub

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Count = 1 Then

Dim oWK As Worksheet

Dim oRng As Range

Set oWK = Excel.ActiveSheet

Set oRng = Target.CurrentRegion

Dim oFC As FormatCondition

iRow = Target.Row

iCol = Target.Column

sF = "=OR(ROW()=" & iRow & ",COLUMN()=" & iCol & ")"

iCol = oRng.FormatConditions.Count

'先删除原来的条件格式

For Each oFC In oRng.FormatConditions

oFC.Delete

Set oFC = oRng.FormatConditions.Add(xlExpression, , sF)

With oFC

'设置条件格式的填充色

.Interior.Color = vbYellow

End With

End If

End Sub

以上代码的原理是用条件格式来[……]

阅读全文

如何用vba给单元格区域添加、修改、删除条件格式？

2018年8月14日作者：exceloffice

暂无评论

在vba中FormatCondition对象代表条件格式。

如果要给Range对象添加条件格式，可以用FormatConditions.Add方法，它的语法如下：

expression . Add( Type , Operator , Formula1 , Formula2 )

1	expression . Add( Type , Operator , Formula1 , Formula2 )

其中Type参数是要添加的条件格式的类型，可以是基于值添加的条件格式，也可以是用公式判断的条件格式。

Type参数将影响后面Operator , Form[……]

阅读全文

如何用vba获取相同单元格内容的联合单元格区域？

2018年8月14日作者：exceloffice

暂无评论

在用vba处理单元格数据时，会遇到相同的单元格内容需要批量处理的情况。

如下图所示

A列的品类含有多个相同项，如果要把相同项所在的单元格区域罗列出来，可以使用如下的代码：

Sub QQ1722187970()
    Dim oWK As Worksheet
    Set oWK = Excel.Worksheets("Sheet1")
    Dim oRng As Range
    With oWK
        iRow = .Range("a65536").End(xlUp).Row
    End With
    '定义字典对象变量
    Dim oDic As Object
    '创建字典对象
    Set oDic = CreateObject("Scripting.Dictionary")
    For i = 2 To iRow
        With oDic
            sKey = oWK.Cells(i, 1).Value
            If .exists(sKey) Then
                '添加相同单元格内容的单元格区域
                Set .Item(sKey) = Excel.Application.Union(.Item(sKey), oWK.Cells(i, 1))
            Else
                .Add sKey, oWK.Cells(i, 1)
            End If
        End With
    Next i
    arrKeys = oDic.keys
    For i = 0 To UBound(arrKeys)
        Set oRng = oDic.Item(arrKeys(i))
        Debug.Print oRng.Address
    Next i
End Sub

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

Sub QQ1722187970()

Dim oWK As Worksheet

Set oWK = Excel.Worksheets("Sheet1")

Dim oRng As Range

With oWK

iRow = .Range("a65536").End(xlUp).Row

End With

'定义字典对象变量

Dim oDic As Object

'创建字典对象

Set oDic = CreateObject("Scripting.Dictionary")

For i = 2 To iRow

With oDic

sKey = oWK.Cells(i, 1).Value

If .exists(sKey) Then

'添加相同单元格内容的单元格区域

Set .Item(sKey) = Excel.Application.Union(.Item(sKey), oWK.Cells(i, 1))

Else

.Add sKey, oWK.Cells(i, 1)

End If

End With

Next i

arrKeys = oDic.keys

For i = 0 To UBound(arrKeys)

Set oRng = oDic.Item(arrKeys(i))

Debug.Print oRng.Address

Next i

End Sub

如何用vba返回多个单元格区域的联合区域？

2018年8月14日作者：exceloffice

暂无评论

在vba中可以使用Application对象的Union方法返回多个单元格区域的联合区域，相当于在excel函数公式中使用了”,”单元格区域联合运算符。

以下是一个示例代码：

Sub QQ1722187970()
    Dim oWK  As Worksheet
    Set oWK = Excel.ActiveSheet
    Dim oRng As Range
    Set oRng = Excel.Application.Union(oWK.Range("a1"), oWK.Range("c3:c5"))
    Debug.Print oRng.Address
End Sub

1

2

3

4

5

6

7

Sub QQ1722187970()

Dim oWK As Worksheet

Set oWK = Excel.ActiveSheet

Dim oRng As Range

Set oRng = Excel.Application.Union(oWK.Range("a1"), oWK.Range("c3:c5"))

Debug.Print oRng.Address

End Sub

如何用vba删除（关闭）或者添加（显示）图表的网格线？

2018年8月14日作者：exceloffice

暂无评论

在图表中有4种网格线，分别是主轴主要水平网格线、主轴主要垂直网格线、主轴次要水平网格线、主轴次要垂直网格线。

在vba中可以用轴对象Axis的属性HasMinorGridlines、HasMajorGridlines来显示（添加）或关闭（删除）主次网格线。

所有轴对象组成了轴集合对象Axes。

有了以上的知识，可以使用如下的代码显示（添加）或关闭（删除）网格线：
[crayon[……]

阅读全文

如何用vba生成多系列气泡图？

2018年8月14日作者：exceloffice

暂无评论

在如何用vba创建气泡图？一文中我们介绍了用vba创建气泡图的要点，并且给出了用vba创建单系列气泡图的代码。

今天，跟大家一起分享如何用vba快速地创建一个多系列的气泡图，以达到不同的系列用不同的颜色显示气泡的目的。

如下图所示：

可以使用如下的代码生成上图的多系列气泡图：

Sub QQ1722187970()
    Dim oWK As Worksheet
    Set oWK = Excel.Worksheets("Sheet1")
    With oWK
        iRow = .Range("a65536").End(xlUp).Row
    End With
    With oWK.Sort
        '删除原来的排序字段
        .SortFields.Clear
        '添加新的排序字段
        .SortFields.Add Key:=oWK.Range("a1"), SortOn:=xlSortOnValues, Order:=xlDescending
        '设置排序的单元格区域
        .SetRange oWK.Range("a1:e" & iRow)
        '设置排序的方向
        .Orientation = xlSortColumns
        '设置是否包含列标题
        .Header = xlYes
        '开始排序
        .Apply
    End With
    '定义字典对象变量
    Dim oDic As Object
    '创建字典对象
    Set oDic = CreateObject("Scripting.Dictionary")
    '先存储相同的系列对应的单元格区域
    For i = 2 To iRow
        With oDic
            sKey = oWK.Cells(i, 1).Value
            If .exists(sKey) Then
                Set .Item(sKey) = Excel.Application.Union(.Item(sKey), oWK.Cells(i, 1))
            Else
                .Add sKey, oWK.Cells(i, 1)
            End If
        End With
    Next i
    arrKeys = oDic.keys
     Dim oChart As Chart
    Dim oSeries As Series
    Dim oSC As SeriesCollection
    Dim oFSC As FullSeriesCollection
    '先创建一个空白的图形壳
    Set oChartObject = oWK.ChartObjects.Add(100, 50, 500, 300)
    Set oChart = oChartObject.Chart
    '对空白的图形进行设置
    With oChart
        .ChartWizard Source:=oWK.Range("c2:e" & iRow), gallery:=xlBubble3DEffect, PlotBy:=xlColumns, HasLegend:=False
        iCount = .FullSeriesCollection.Count
            For i = .FullSeriesCollection.Count To 1 Step -1
                .SeriesCollection(i).Delete
            Next i
        '遍历创建所有系列
        For i = 0 To UBound(arrKeys)
            Dim oRng As Range
            Set oRng = oDic.Item(arrKeys(i))
            Debug.Print oRng.Address
            Set oSeries = .SeriesCollection.NewSeries
            With oSeries
                .Name = arrKeys(i)
                .XValues = oRng.Offset(0, 2)
                .Values = oRng.Offset(0, 3)
                .BubbleSizes = oRng.Offset(0, 4)
            End With
        Next i
    End With
End Sub

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

Sub QQ1722187970()

Dim oWK As Worksheet

Set oWK = Excel.Worksheets("Sheet1")

With oWK

iRow = .Range("a65536").End(xlUp).Row

End With

With oWK.Sort

'删除原来的排序字段

.SortFields.Clear

'添加新的排序字段

.SortFields.Add Key:=oWK.Range("a1"), SortOn:=xlSortOnValues, Order:=xlDescending

'设置排序的单元格区域

.SetRange oWK.Range("a1:e" & iRow)

'设置排序的方向

.Orientation = xlSortColumns

'设置是否包含列标题

.Header = xlYes

'开始排序

.Apply

End With

'定义字典对象变量

Dim oDic As Object

'创建字典对象

Set oDic = CreateObject("Scripting.Dictionary")

'先存储相同的系列对应的单元格区域

For i = 2 To iRow

With oDic

sKey = oWK.Cells(i, 1).Value

If .exists(sKey) Then

Set .Item(sKey) = Excel.Application.Union(.Item(sKey), oWK.Cells(i, 1))

Else

.Add sKey, oWK.Cells(i, 1)

End If

End With

Next i

arrKeys = oDic.keys

Dim oChart As Chart

Dim oSeries As Series

Dim oSC As SeriesCollection

Dim oFSC As FullSeriesCollection

'先创建一个空白的图形壳

Set oChartObject = oWK.ChartObjects.Add(100, 50, 500, 300)

Set oChart = oChartObject.Chart

'对空白的图形进行设置

With oChart

.ChartWizard Source:=oWK.Range("c2:e" & iRow), gallery:=xlBubble3DEffect, PlotBy:=xlColumns, HasLegend:=False

iCount = .FullSeriesCollection.Count

For i = .FullSeriesCollection.Count To 1 Step -1

.SeriesCollection(i).Delete

Next i

'遍历创建所有系列

For i = 0 To UBound(arrKeys)

Dim oRng As Range

Set oRng = oDic.Item(arrKeys(i))

Debug.Print oRng.Address

Set oSeries = .SeriesCollection.NewSeries

With oSeries

.Name = arrKeys(i)

.XValues = oRng.Offset(0, 2)

.Values = oRng.Offset(0, 3)

.BubbleSizes = oRng.Offset(0, 4)

End With

Next i

End With

End Sub

以上是用vba创建多系列气[……]

阅读全文

如何用vba创建气泡图？

2018年8月14日作者：exceloffice

暂无评论

在如何在excel中用vba创建图表? 一文中我们介绍了用vba创建图表的基本步骤和通用代码。

但是如果用上文中的代码创建的气泡图是错的，默认会创建多个系列的杂乱无章的气泡图。

气泡图主要用于对比三组数据，其中一组数据用于显示气泡的大小，另外两组数据分别显示X轴和Y轴对应的值。

在用vba创建气泡图时，X值和Y值以及气泡的大小三组值的设置至关重用，如果设置错了，创建的气泡图就会[……]

阅读全文

如何区别Series、SeriesCollection、FullSeriesCollection对象？

2018年8月13日作者：exceloffice

暂无评论

用vba处理图表，经常需要处理图表的系列。

在vba中图表的系列有Series、SeriesCollection、FullSeriesCollection等3个对象。

这3个对象有点类似，但是又有区别，其中

Series对象表示图表中每个独立的系列。

SeriesCollection、FullSeriesCollection对象均表示图表中的所有系列集合，但是SeriesC[……]

阅读全文

如何用vba判断打开的文件是否处于受保护视图？

2018年8月13日作者：exceloffice

暂无评论

当用vba代码遍历文件时，如果文件是从互联网上下载的，会弹出提示以受保护视图打开，无法继续编辑，此时代码会报错。

vba中ProtectedViewWindow对象是与受保护的视图相关的对象。

在vba中可以使用Application对象的ActiveProtectedViewWindow属性返回ProtectedViewWindow对象。

当返回了ProtectedViewWi[……]

阅读全文

近期文章

关键字

功能