Excel VBA 如何实现两个单元格区域的锁定保护属性互换

实现两个选中区域数值+锁定属性互换的VBA代码 #

Sub SwapTwoRangesValueAndLocked()
    Dim rng As Range
    Dim StoredVal1 As Variant, StoredVal2 As Variant
    Dim StoredLocked1 As Variant, StoredLocked2 As Variant
    Dim i As Long, j As Long
    
    ' 选中区域合法性校验
    If Selection.Areas.Count <> 2 Then
        MsgBox "请按住Ctrl选中2个需要互换的单元格区域", vbExclamation
        Exit Sub
    End If
    If Selection.Areas(1).Cells.Count <> Selection.Areas(2).Cells.Count Then
        MsgBox "两个选中区域的单元格数量必须一致", vbExclamation
        Exit Sub
    End If
    
    Set rng = Selection
    
    ' 存储第一个区域的原始数值和锁定状态
    StoredVal1 = rng.Areas(1).Value
    ReDim StoredLocked1(1 To rng.Areas(1).Rows.Count, 1 To rng.Areas(1).Columns.Count)
    For i = 1 To rng.Areas(1).Rows.Count
        For j = 1 To rng.Areas(1).Columns.Count
            StoredLocked1(i, j) = rng.Areas(1).Cells(i, j).Locked
        Next j
    Next i
    
    ' 存储第二个区域的原始数值和锁定状态
    StoredVal2 = rng.Areas(2).Value
    ReDim StoredLocked2(1 To rng.Areas(2).Rows.Count, 1 To rng.Areas(2).Columns.Count)
    For i = 1 To rng.Areas(2).Rows.Count
        For j = 1 To rng.Areas(2).Columns.Count
            StoredLocked2(i, j) = rng.Areas(2).Cells(i, j).Locked
        Next j
    Next i
    
    ' 给第一个区域赋值第二个区域的数值和锁定状态
    rng.Areas(1).Value = StoredVal2
    For i = 1 To rng.Areas(1).Rows.Count
        For j = 1 To rng.Areas(1).Columns.Count
            rng.Areas(1).Cells(i, j).Locked = StoredLocked2(i, j)
        Next j
    Next i
    
    ' 给第二个区域赋值第一个区域的数值和锁定状态
    rng.Areas(2).Value = StoredVal1
    For i = 1 To rng.Areas(2).Rows.Count
        For j = 1 To rng.Areas(2).Columns.Count
            rng.Areas(2).Cells(i, j).Locked = StoredLocked1(i, j)
        Next j
    Next i
End Sub

代码说明 #

• 修复了原有代码错误的锁定属性设置逻辑：原有代码直接判断整个区域的Locked属性，当区域内存在不同锁定状态时该属性会返回Null，无法正确识别单个单元格的锁定状态
• 新增数组存储两个区域所有单元格的原始锁定状态，数值互换完成后交叉赋值，支持区域内部分单元格锁定、部分未锁定的场景
• 新增操作合法性校验，避免不规范操作导致的运行报错

内容的提问来源于stack exchange，提问作者Mohamad Bachrouche