零基础学Excel VBA(3)

第 8 章   单 元 格 的 相 关 操 作

单 元 格 泛 指 工 作 表 中 的 一 个 或 多 个 单 元 格,在 操 作 单 元 格 之 前, 首 先 需 要 选 取 单 元 格。

单 个 单 元 格 的 选 取

在 Excel VBA 当 中, 通 常 使 用 Range 对 象 和 Cells 对 象 来 表 示 单 元 格。 使 用 Range 对 象 和 Cells 对 象 选 取 单 元 格 主 要 有 以 下 几 种 表 示 的 方 法。

使 用 Range 对 象 选 取 单 元 格

(1) 通 过 单 元 格 的 地 址 选 取 单 元 格, 语 法 格 式 如 下 所 示。

Range(" 单 元 格 地 址"). Select

示例:Range("A6").Select

(2) 通 过 单 元 格 的 列 标 和 行 数 确 定 选 取 的 单 元 格

语 法 格 式 如 下 所 示。

Range(" 列 标"&" 行 数"). Select

如 选 取 B6 单 元 格 的 程 序 代 码 为: Range(" B"& 6). Select

(3) 根 据 定 义 的 单 元 格 名 称 选 取 单 元 格

语 法 格 式 如 下 所 示。

Range(" 单 元 格 名 称"). Select

如 选 取 单 元 格 名 称 为" MyCells" 单 元 格 的 程 序 代 码 为: Range(" MyCells"). Select

使 用 Cells 对 象 选 取 单 元 格

(1) 通 过 确 定 单 元 格 所 在 的 行 数 和 列 数 选 取 单 元 格

语 法 格 式 如 下 所 示。 Cells( 行 数, 列 数). Select

如 选 取 单 元 格 A1 的 代 码 为: Cells( 1,1). Select

(2) 通 过 确 定 单 元 格 所 在 的 行 数 和 列 标 选 取 单 元 格

语 法 格 式 如 下 所 示。

Cells( 行 数, 列 标). Select

如 选 取 单 元 格 A1 的 代 码 为: Cells( 1," A"). Select

(3) 根 据 单 元 格 的 序 号 选 取 单 元 格

语 法 格 式 如 下 所 示。

Cells( 单 元 格 序 号). Select

如 选 取 表 示 序 号 为 2 的 单 元 格 的 代 码 为: Cells( 2). Select

选 取 特 定 单 元 格

1. 选 取 当 前 已 使 用 的 单 元 格

当 前 已 使 用 的 单 元 格 是 指 当 前 工 作 表 中 已 经 使 用 过 的 单 元 格, 对应的是有数据的单元格的最上,最下,最左,最右单元格组成的矩形.

使 用 工 作 表 的 UsedRange 属 性 可 以 选 取 当 前 已 使 用 的 单 元 格

Sub cells_used_selected()
    Sheets("SheetVBA").Activate
    ActiveSheet.UsedRange.Select
End Sub

2. 根 据 具 体 要 求 选 取 单 元 格

在 Excel VBA 中, 通 过 使 用 Range 对 象 的 End 属 性 可 以 实 现 通 过 按 下 Ctrl + 方 向 键 选 取 单 元 格 的 效 果, 具 体 的 实 现 方 法 如 下 所 示。

  • End( xlup): 相 当 于 按 下 Ctrl + 向 上 箭 头 ↑,从当前位置向上跳转到第一个非空单元格,如果都是空数据,跳到最上.下同
  • End( xldown): 相 当 于 按 下 Ctrl + 向 下 箭 头 ↓。
  • End( xltoleft): 相 当 于 按 下 Ctrl + 向 左 箭 头 ←。
  • End( xltoright): 相 当 于 按 下 Ctrl + 向 右 箭 头 →。

在 选 取 B 列 最 后 1 个 非 空 单 元 格 时, 需 要 从 B 列 最 后 一 个 单 元 格 B1048576 向 上 进 行 查 找。

Sub select_lastUsed_column()
    Range("B1048576").End(xlUp).Select
End Sub

从Excel 2007 开始,"大网格"将每个工作表的最大行数从65,536 增加到超过100 万,将列数从256 (IV) 增加到16,384 (XFD)

选中所有单元格,包括没有数据的:

Sub select_all()
    'Rows.Select
    Columns.Select
End Sub

在 Excel 工 作 表 当 中, 单 击 工 作 表 左 上 角 的【 全 选】 按 钮, 也 可 以 选 中 工 作 表 中 所 有 的 单 元 格,

选 取 单 元 格 区 域

除 了 上 述 讲 解 的 几 种 选 取 单 元 格 的 方 法 之 外, 还 可 以 按 照 指 定 的 区 域 选 取 单 元 格。

选 取 单 元 格 区 域 主 要 分 为 选 取 连 续 单 元 格 区 域 和 选 取 不 连 续 单 元 格 区 域 两 种。

1. 选 取 连 续 单 元 格 区 域

连 续 单 元 格 区 域 是 指 在 工 作 表 中, 从 某 个 单 元 格 到 另 一 个 单 元 格 之 间 的 区 域

Sub select_continus_cells()
    Dim cell_start As String
    Dim cell_end As String
    cell_start = InputBox("Input Start Cell Address:")
    cell_end = InputBox("Input End Cell Addredd:")
    Range(cell_start, cell_end).Select
End Sub

2. 选 取 不 连 续 单 元 格 区 域

Sub select_uncontinus_cells()
    Range("B2:c10,e1:e20,g3:g15").Select
End Sub

移 动 单 元 格 的 选 取 区 域

通 过 Excel VBA 程 序 代 码 还 可 以 动 态 移 动 或 改 变 单 元 格 的 选 取 区 域。 在 Excel VBA 中, 通 过 使 用 Offset 函 数 和 Resize 函 数 来 完 成 改 变 单 元 格 选 取 范 围 的 功 能, 函 数 原 形 如 下 所 示。

Offset( 移 动 行 数, 移 动 列 数) 
Resize( 总 行 数, 总 列 数) 

函 数 中 的 各 项 参 数 说 明 如 下 所 示。

  • 移 动 列 数: 如 果 为 正 值, 表 示 向 右 移 动 指 定 的 列 数; 如 果 为 负 值, 则 表 示 向 左 移 动 指 定 的 列 数。
  • 移 动 行 数: 如 果 为 正 值, 表 示 向 下 移 动 指 定 的 行 数; 如 果 为 负 值, 则 表 示 向 上 移 动 指 定 的 行 数。
  • 总 行 数: 表 示 移 动 后 的 单 元 格 区 域 的 行 数。
  • 总 列 数: 表 示 移 动 后 的 单 元 格 区 域 的 列 数。

移 动 单 元 格 的 选 取 区 域 并不会移动数据, 只是选定的区域移动了.移动单元格的选取区域这种描述并不准确, 实际上用相对位置定位选取区域更合适,包括: 1.相对位置的点, 2.起始位置的计算, 3.行和列的数量

Sub moveCells()
    Range("B5").Offset(3, 1).Resize(6, 3).Select
End Sub

该示例选取如下区域: 区域的起点为: 相对与"B5" 向下3,向右1, 大小为6行3列

选 取 整 行 和 整 列 单 元 格

除 了 通 过 上 述 的 方 法 选 取 单 元 格 之 外, 在 工 作 表 中 还 可 以 选 取 一 行 或 一 列, 多 行 或 多 列 的 单 元 格。

在 Excel VBA 中 可 以 用 Rows 属 性、 Columns 属 性 来 表 示 行 和 列, 同 时 也 可 以 用 Range 来 表 示 行 和 列。

Sub select_rows_columns()
    ' 选 取 单 行 单 元 格
    'Range("5:5").Select
    ' 选 取 多 行 单 元 格
    'Range("5:8").Select
    ' 选 取 单 列 单 元 格
    'Columns(2).Select
    ' 选 取 多 列 单 元 格
    Range("B:E").Select
End Sub

excel VBA常用快捷键

F2 对象浏览器
F4 属性窗口
F5 运行子过程/用户窗体
F7 代码窗口
F8 逐语句执行代码
F9 设置/取消断点
Ctrl+Shift+F9   清除所有断点
Ctrl+G  立即窗口
Ctrl+R  工程窗口
Shift+F8    逐过程执行代码
Shift+F10   显示右键菜单
Alt+F11 返回Excel界面
Ctrl+F  查找
Ctrl+H  替换
F3  查找下一个
Shift+F3    查找上一个
Ctrl+上箭头    前一个过程
Ctrl+下箭头    下一个过程
Shift+F2    查看定义
Tab 缩进
Shift+Tab   突出
Ctrl+Break  中断

单 元 格 内 容 的 输 入

向 Excel 的 单 元 格 中 输 入 数 据 主 要 包 括 :

  • 向 单 元 格 中 输 入 常 量 信 息
  • 使 用 公 式 向 单 元 格 中 输 入 数 据 两 个 方 面

1. 向 单 元 格 中 输 入 常 量 (这里用标量更合适,常量通常是指代不变的量)

数 据 常 量 主 要 是 指 数 字 和 字 符 等, 在 Excel VBA 中, 通 过 使 用 单 元 格 对 象 的 Value 属 性 来 实 现 向 单 元 格 中 输 入 常 量 数 据 的 功 能。

Sub set_scalar_value()
    Range("B1:c10").Value = "VBA"

    Dim i As Integer
    'row,column 的下标是从1开始的 ^-^
    For i = 1 To 10
             Cells(i, 1).Value = i ^ 3
    Next i
End Sub

2. 使 用 公 式 向 单 元 格 中 输 入 数 据

在 Excel VBA 中, 通 过 使 用 Formula 属 性 可 以 在 单 元 格 中 输 入 公 式 并 且 取 得 单 元 格 的 公 式。

在 单 元 格 中 根 据 公 式 输 入 数 据 分 为 使 用 固 定 公 式 和 可 变 公 式 两 种 情 况。

固 定 公 式 是 指 使 用 的 公 式 在 程 序 中 已 经 存 在, 而 可可 变 公 式 是 指 所 输 入 的 公 式 位 置 不 固 定。

Sub formula()
Range("c20").formula = "=a20*b20"
'效果同上
Range("d20") = "=a20*b20"
End Sub

若 想 计 算 出 相 关 的 数 据 信 息, 必 须 每 一 行 都 输 入 一 个 固 定 的 计 算 公 式。 按 照 这 样 的 写 法, 那 么 如 果 数 据 表 中 有 上 百 条 数 据, 就 需 要 有 上 百 个 计 算 公 式, 这 样 显 然 是 不 符 合 实 际 应 用 的。 使 用 可 变 的 公 式 就 可 以 解 决 这 一 问 题。

Sub formula()
    'i5 开始计算d~h列的和
    Dim i As Integer
    For i = 5 To Range("D1048576").End(xlUp).Row '获取最后一行的行标
        'Address(0, 0) 为所选区域的第一个单元格地址
        '计算的输出类似: =SUM(D6:H6)
        Cells(i, 9).formula = "=sum(" & Cells(i, 9).Offset(0, -5).Address(0, 0) & ":" & Cells(i, 9).Offset(0, -1).Address(0, 0) & ")"
    Next i
End Sub

清 除 单 元 格 中 的 信 息

Excel 工 作 表 中 的 单 元 格 信 息 由

  • 单 元 格 内 容
  • 单 元 格 格 式
  • 单 元 格 批 注 3 部 分 组 成。

如 果 单 元 格 中 的 数 据 信 息 不 再 需 要, 可 以 将 其 清 除。 清 除 单 元 格 中 的 数 据 信 息 分 为

  • 清 除 单 元 格 格 式、
  • 清 除 单 元 格 内 容、
  • 清 除 单 元 格 批 注
  • 清 除 单 元 格 全 部 4 种 情 况,
Sub clean_cell()
    Range("c1:d10").ClearFormats
    Range("c1:d10").ClearContents
    Range("c1:d10").ClearComments
    Range("c1:d10").Clear
End Sub

插 入 单 元 格

1. 在 工 作 表 中 插 入 一 个 或 多 个 空 行,空列

Sub insertRows()
    '插入空行作为第6行
    Rows(6).Insert

    '插入4行,位置为6,7,8,9
    Rows("6:9").Insert
End Sub

插入空列类似:

Sub insert_columns()
    Columns(2).Insert
    Columns("c:e").Insert
End Sub

2.在指定位置插入1个或多个空白单元格

Sub insert_blank_cells()
    Range("b2:c5").Insert shift:=xlDown '原来的单元格下移动
End Sub

删 除 单 元 格

通 常 情 况 下, 删 除 单 元 格 主 要 分 为 以 下 几 种 情 况。

  • 删 除 后 单 元 格 左 移: 是 指 在 删 除 指 定 的 单 元 格 后, 其 右 侧 的 单 元 格 左 移。
  • 删 除 后 单 元 格 上 移: 是 指 在 删 除 指 定 的 单 元 格 后, 其 下 方 的 单 元 格 上 移。
  • 删 除 整 行: 将 单 元 格 所 在 的 行 删 除。
  • 删 除 整 列: 将 单 元 格 所 在 的 列 删 除。
Sub del_cells()
    Range("b2:c5").Delete shift:=xlToLeft
    Range("b2:c5").Delete shift:=xlUp
    Range("a1").EntireRow.Delete
    Range("a1").EntireColumn.Delete
End Sub

当 自 变 量 Shift 为 xlToLeft 时, 则 删 除 单 元 格 后 右 侧 的 单 元 格 左 移; 当 自 变 量 Shift 为 xlUp 时, 则 删 除 单 元 格 后 下 方 的 单 元 格 上 移。

单 元 格 的 隐 藏

Sub hide_cells()
    '整行隐藏
    Rows("4:6").Hidden = True
    '整列隐藏
    Columns("a:c").Hidden = True
    Columns(10).Resize(, 3).Hidden = True '10,11,12隐藏
End Sub

在 Excel 中, 无 法 实 现 对 单 个 单 元 格 的 隐 藏, 只 能 够 对 单 元 格 所 在 的 行 或 列 进 行 隐 藏。

Sub hide_cell()
    Range("b3").EntireRow.Hidden = True
    Range("b3").EntireColumn.Hidden = True
End Sub

单 元 格 的 查 找

1.使 用 工 作 表 函 数 进 行 查 找

在 Excel VBA 中, 使 用 工 作 表 函 数 可 以 查 找 单 元 格。 Excel 中 用 于 查 找 单 元 格 的 工 作 表 函 数 主 要 有 以 下 两 种。

  • Match 函 数: 用 于 查 找 单 元 格 的 位 置。
  • Vlookup 函 数: 用 于 查 找 并 返 回 单 元 格 的 内 容。

使用 Match 函数在某行或某列查找

Sub search_position()
    'https://docs.microsoft.com/en-us/office/vba/api/excel.worksheetfunction.match
    'https://support.microsoft.com/zh-cn/office/match-%E5%87%BD%E6%95%B0-e8dffd45-c762-47d6-bf89-533f4a37673a
    Dim strSearch As String
    Dim position As String
    strSearch = Range("F20").Value '需要查找的字符串

    '第二个参数必须是某行或者某列
    'position = Application.Match(strSearch, Columns(3), 0)
    position = Application.Match(strSearch, Rows(4), 0)

    Range("F21").Value = position

End Sub

(本章以下略)

© Licensed under CC BY-NC-SA 4.0

尊严不值钱,却是我唯一真正拥有的!—— V For Vendetta

发表我的评论
取消评论
表情

Hi,您需要填写昵称和邮箱!