目录

示例：

利用Value属性给单元格赋值

For Each---Next循环

使用For Each---Next循环的注意事项

利用Value属性给单元格区域赋值

单元格( Range)对象的Address属性

示例：

    如图所示，该表为某公司员工的工资表。现公司政策调整，需要将所有员工的补贴统一为200元，该如何用VBA实现？

Option Explicit

Sub 批量设置补贴()
    '设置计数变量
    Dim RowN As Long
    
    '利用For循环
    For RowN = 2 To 37
        '以下两个语句任选一句
        Cells(RowN, "D").Value = 200
        'Range("D" & RowN).Value = 200
    Next
End Sub

利用Value属性给单元格赋值

    Value属性是单元格的默认属性，无论是Range对象还是Cells对象，都可以使用Value属性进行赋值。当需要修改单元格的值时，可以在Value属性中写入某个值。Value属性可以接受任何非对象的基本数据类型。当需要给单元格区域批量赋值时，通常采用循环结构给单元格赋值。
    由于能够准确得知单元格范围，本例中采用了For---Next循环对某一列的单元格区域进行赋值。若需要对某个多行多列的矩形区域进行赋值，如B2：D37，可以采用如下嵌套的循环结构：

Sub 批量设置补贴2()
    Dim RowN As Long
    Dim ColN As Long
    
    For RowN = 2 To 37
        For ColN = 2 To 4
            Cells(RowN, ColN).Value = 100
        Next
    Next
End Sub

    此时，由于列数也在产生变化，而通常采用Cells对象给单元格赋值。在大部分情况下，由于行号和列号均能进行数字化，因而Cells对象使用频率要超过Range对象。

For Each---Next循环

For Each---Next循环可以用来访问某个集合中所有的元素，其语法格式为

For Each element In group
    [statements]
    [Exit For]
    [statements]
Next [element]

    其中，参数element表示集合中某个元素的变量，group表示某个集合。group所表示的集合可以是单元格区域、工作表集合、工作簿集合、数组集合等。在使用该循环结构时，element所表示的变量的类型必须与group集合中的元素类型一致。

Sub 批量设置补贴2()
    '设置单元格变量
    Dim Rng As Range
    
    For Each Rng In Range("D2:D37")
        Rng.Value = 200
    Next
End Sub

使用For Each---Next循环的注意事项

    使用For Each---Next循环可以极大地方便开发者对对象进行批量的操作，但在使用中应注意以下两点。

• 表示元素的对象应显式声明，并且避免使用Variant对象。
• 当需要进行属性更改时，即使是默认属性，该属性也不能省略。

    如果出现上述错误，则VBA程序会对开发者的实际意图进行误判，而造成结果的错误。比如以下程序将不会修改任何一个单元格。

Sub 此程序不会修改任何单元格()
    '设置单元格变量
    Dim Rng As Variant
    
    For Each Rng In Range("D2:D37")
        Rng.Value = 200
    Next
End Sub

利用Value属性给单元格区域赋值

    当引用单元格区域的Value属性时，则表示该区域所有单元格的Value属性，因而通过此简单的赋值语句可以实现批量赋值。
    同样，能够使用该方法进行批量修改的单元格属性有：NumberFormat属性、NumberFormatLocal属性、Font属性（字体）、Interior属性（填充）和Border属性等。绝大部分能够通过工作表中批量操作的属性，均能够用VBA访问其相应的属性来进行批量修改。

Sub 批量设置补贴3()
    Range("D2:D37").Value = 200
End Sub

单元格( Range)对象的Address属性

    Address属性是Range对象一个非常有用的属性。通过该属性，可以获取各种形式的单元格地址，其语法如下：   

Range.Address(RowAbsolute,ColumnAbsolute,ReferenceStyle,External,RelativeTo)

    其中，Range为一个单元格或单元格区域对象。
    参数RowAbsolute为逻辑值，当其为True时，表示行以绝对引用返回，反之表示相对引用
    参数ColumnAbsolute为逻辑值，当其为True时，表示列以绝对引用返回，反之表示相对引用。
    参数ReferenceStyle表示引用的形式，默认为xIA1（A1引用方式），也可设置为xIR1C1(R1C1引用方式)。
    参数External为逻辑值，当其为True时，表示以外部引用的方式返回单元格地址，其中包括工作簿、工作表的引用。其默认值为False时，表示仅返回单元格的地址。
    参数RelativeTo适用于R1C1引用时的相对引用，则此时该参数需要设置一个单元格对象，表示其相对引用的起始位置。
 

Option Explicit

'iRng 为上一次选中的单元格,全局变量
Dim iRng As Object
'val为上一次选中单元格的值
Dim val

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    '本功能可以实现通过判断表格第26列的布尔值来决定是否允许用户修改单元格的值
    '不允许修改的逻辑为:保存上一次选中单元格的range和range的value
    '不允许修改的核心方法是给上一次选中的单元格赋值修改前的值
    '保存上一次选中单元格的range是为了知道上一个选中单元格的地址,存值是因为上一个选中的单元格值修改就找不到了
    'bug:如果选中的一个区域而不是单元格则无效
    
    '如果是操作锁定列,或表格之外的区域则退出sub
    If Target.Column > 25 Then Exit Sub

    
    '初始化状态栏
    Application.StatusBar = ""
    If Cells(Target.Row, 26) Then
        '在状态栏显示单元格锁定信息,没有锁定则无需显示
        Application.StatusBar = Application.StatusBar & "----当前单元格已锁定"
    End If
    
    '如果选中的不是一个区域则
    If Not IsArray(Target.Value) Then
        '如果iRng没有赋值则现在赋值
        If iRng Is Nothing Then
            Set iRng = Target
            val = iRng.Formula
        Else
            '判断本列是否锁定
            If Cells(iRng.Row, 26) Then
                '如果锁定了,且现在的值不等于原来的值则,还原原来的值
                If iRng.Formula <> val Then
                    '写入修改的日志信息
                    iLog Target
                    '时光倒流
                    iRng.Formula = val
                    '写入修改失败的日志信息
                    log.Cells(log.Cells(1, 10).Value - 1, 5).Value = False
                End If
            Else

                '如果没有锁定,且现在的值不等于原来的值
                If iRng.Formula <> val Then
                    '写入日志
                    iLog Target
                    '写入修改成功的日志信息
                    log.Cells(log.Cells(1, 10).Value - 1, 5).Value = True
                End If
            End If
        End If
        '保留时光
        Set iRng = Target
        '保留时光公式
        val = iRng.Formula
    End If
End Sub

Private Sub iLog(ByVal Target As Range)
    Debug.Print log.Cells(1, 10).Value
    '在第1列写入时间
    log.Cells(log.Cells(1, 10).Value, 1) = Now
    '在第2列写入单元格地址
    log.Cells(log.Cells(1, 10).Value, 2) = Target.Address
    '在第3列写入单元格原值
    log.Cells(log.Cells(1, 10).Value, 3) = val
    '在第4列写入单元格新值
    log.Cells(log.Cells(1, 10).Value, 4).Value = iRng.Formula
    '在第10列第1行写入下一次写入新的日志的行号
    log.Cells(1, 10).Value = log.Cells(1, 10).Value + 1
End Sub

 下面是运行效果, 代码放在sheet1里面使用,再新建一个sheet,在VBA界面属性里面修改新建的sheet2名称为log

在工作中,常常会遇到引用不连续单元格的情况,经过摸索总结,有以下方法：

一、对象变量引用单元格本身

假如有三个不连续的单元格：A1,B2,B5

则用vba方法选中这三个不连续单元格的代码如下:

1, range("A1,B2,B5").select

2,Union([A1], [B2], [B5]).selcet

以上均可使用range对象变量进行直接引用.

dim c as range

set c=range("A1,B2,B5")  或者 set c=Union([A1], [B2], [B5])

二、数组元素引用单元格数据

为了便于循环引用，可将不连续单元格数据使用array()函数组成自定义数组,然后循环引用.

比如上述三个单元格,

dim brr

brr = Array([a1], [b2], [b5])

当然,考虑到通用性问题,可以将array中的元素使用变量代替

dim brr

dim a,b,c

set a=[a1]:set b=[b2]:set c=[b5]

brr= Array(a,b,c)

组成自定义数组后,便可使用brr(i)的方式进行相应单元格数据的引用.

三、不连续单元格一次性赋值

上面一、二我们得到了不连续单元格的对象引用以及数组方式的数据引用.为此将二者结合起来,可以进行不连续单元格的批量赋值.

采用循环,一对一进行赋值.

dim rng as range

i=0

for each rng in c

rng=brr(i)

i=i+1

next