关于VBA的简单使用

落寞红颜玉玫瑰

已于 2024-06-27 00:46:24 修改

阅读量829

点赞数 24

分类专栏：自动化办公文章标签： excel 学习方法

于 2024-06-27 00:43:34 首次发布

本文链接：https://blog.csdn.net/weixiyaya/article/details/139909128

版权

自动化办公专栏收录该内容

2 篇文章 0 订阅

订阅专栏

再回首，对这句“但见光阴流似箭头，岂知天道曲如弓”深有感触。你我只需：埋头将那光阴赶，晴时行行雨未停，趁得闲来看百花，惊言已过五六年。

在实际工作或者学习中，Excel已经是我们离开不了的工具之一，其简单易学，提供基本的数据输入和计算功能，也支持复杂的数据分析、图表制作与呈现。本文章只介绍VBA的基本使用，更高级的界面呈现可以做，但没必要，在现在，随着互联网的快速发展，可以用python等配合mysql或者JAVAScript与mysql做出适合于小组作业的界面呈现。

“时代在不断发生变化，互联网时代、人工智能、物联网时代的到来，大大改变了我们原先的生存环境，对我们的职场生存空间产生了极大的挑战。以往更多需要人工参与的工作将逐渐被机器所取代。原先的工作方式将被更加先进智能高效的模式所替代。掌握编程语言转型为复合型人才将是未来职场的标配。这个世界上绝大多数人都是在职场环境中谋求生存和发展，办公室格子间中做表格处理数据是职场人的人生写照。在时代发展的洪流中顺时代大势而为先人一步掌握更加智慧高效的数据操作方法，将是我们鹤立鸡群，在职场中屹立不倒的途径之一。这个世界上已经有很多编程语言了，有我们熟知的Java，Javascript，C，C++，C#，VB，VBA，PHP，Python等等等等。在这些语言当中，唯有VBA是与我们日常工作息息相关，VBA中更是以Excel VBA称雄职场，是一门实操性极强的干货级技能语言。”（推荐一篇文章：神奇的VBA，这位大哥的文章写的太好了，上面这段话就是来自他的文章；同时，他设计的VBA就是我们追求的最终结果，希望各位都能学到这种水平。）

工作困境

lookup是Excel中常用的一个函数，包含三种形式，分别是lookup，vlookup，hlookup；其应用足以覆盖大多数工作场景。但当表格中数据过多时，则容易存在串行，匹配慢等问题。且当源数据表丢失时，匹配结果相应的出错，这个是比较致命的。当然对于比较复杂的多项匹配来说，VBA更是最优的选择。下面是我们使用到多项匹配的场景：一个特征值无法确定唯一的值，需要多个指标协同确认，关键字所在列（行）在匹配值所在列（行）后面，且不能移动列（行）时，多项匹配便是简单快捷的得到最终结果的函数，如下：

= vlookup(key_1&key_2&key_3,if({1,0},keyCol_1&keyCol_2&keyCol_3,valCol_1&valCol_2),2,0)
# key 是用于匹配的关键字，此值应该是唯一值。
# if 函数用于返回重组数据，是一个整体。
# 2 表示匹配结果是重组数据的第二列。
# 0 表示精确匹配，1 表示模糊匹配。
# lookup，hlookup，用法同理。

#························***·························#
#  valCol_1  keyCol_1  valCol_2  keyCol_2  keyCol_3  #
#   val1_1    key1_1    val2_1    key2_1    key3_1   #
#   val1_2    key1_2    val2_2    key2_2    key3_2   #
#   val1_3    key1_3    val2_3    key2_3    key3_3   #
#     ·         ·         ·         ·          ·     #
#     ·         ·         ·         ·          ·     #
#     ·         ·         ·         ·          ·     #
#   val1_n    key1_n    val2_n    key2_n    key3_n   #
#>>>>>>>>>>>>>>通过if函数重组后的数据格式<<<<<<<<<<<<<<#

#++++++++++++++++++++++<<<>>>++++++++++++++++++++++++#
#             keyCol                  valCol         #
#    keyCol_1&keyCol_2&keyCol_3  valCol_1&valCol_2   #
#++++++++++++++++++++++<<<>>>++++++++++++++++++++++++#

# 注：其中keyCol_1&keyCol_2&keyCol_3的顺序应该与key_1&key_2&key_3保持一致。

通过使用VBA可以解决上面问题，应用change函数可以在key改变时返回val值，源数据丢失时，也不会影响到已匹配到的值。

认识VBA

本文将VBA分为三部分来认识，下面是我对VBA的理解：

1、“数据”类型：VBA通过 dim 来提前定义所使用/接受数据的类型，常用的有 integer（整数），Range（单元格，至少一个单元格）Object（对象类型数据，比较多，比如dict【字典】，regex【正则表达式】……），string（文本），数组等等。

2、语法使用：就是vb的语法使用，常用的有if语句，for循环以及whie-loop语句。

3、框架构建：可使用系统自带的框架结构：单选，多选，文本，输入框等等。

基础语法

1、if语句：常用于多个条件判断时，不同的输入值将导向不同的结果。

其应该做到：从所有的判断体拿出任意一个或多个条件，其与剩余条件的和对立且相互之间互斥。

if 判断1 then
    结果1
elseif 判断2 then
    结果2
else
    结果3
end if

2、for语句：用于多个重复步骤的执行，将结构体反复执行需要的次数。其可转化为until以及while语句。

#语法格式1：涉及次数的循环执行，步长默认为1
for i=start To end step 1 
    执行语句块
Next

#语句格式2：用于对整体中的单个个体执行同一步骤
for Each single in all
    执行语句块
Next

3、while和Until语句：while-当满足条件时执行语句体；until-直到满足条件时，放弃执行语句体。

实际应用

1、当我们选择vba中的Worksheet -->Change 后会出现以下界面（图片为实际界面操作），其中代码为自己写的，不是函数自带。

Private Sub Worksheet_Change(ByVal Target As Range)

End Sub()

2、定义所使用的名称及数据类型。

Dim keys(), items()        #定义两个数组，分别是keys和items
Dim max_lim As Integer     #定义一个整数来储存Sheet表的存在数据的最大行（默认数据不跳行储存）
Dim use As Object          #定义use作为一个对象
Set use = CreateObject("scripting.dictionary") #给use对象赋予字典类型，使其储存字典对象数据。
'''Dim use As New Dictionary #这条语句是直接创建字典对象，与上面的区别是，这样建立的对象无法跨设备                                                            使用，也就是当你把文件分享给别人后，使用该VBA会出现错误提示。'''
Dim maxRow, e As Integer   #同上，定义整死用于储存值
Dim temp As String         #定义一个中间变量为字符串类型数据

Dim bl As Boolean          #定义一个布尔值，用来做阀门开关
Dim now_row As Integer
Dim single_cell As Range
Dim target_cell As Range


#注：VBA中使用系统自带以外字符时，需提前定义其类型以及名称。

3、得到目标Sheet的最大行数，以及给需要的名称赋值。

max_lim = Worksheets("运费").Range("A1").End(xlDown).row #得到运费此表的最大行

keys = Worksheets("运费").Range("A2:C" & max_lim).Value  #将前三列的值给数据keys，现在它是一个n行3列的数据。
maxRow = Range("A7").End(xlDown).row - 1

Rem MsgBox Cells(2, maxRow).Value  #在vba中可用“rem” 或“'”后面跟说明来表示注释

Dim k_maxr As Integer, s As Integer #下面的代码，重复了上面的操作，操作对象为表格：矿点对应
Dim kd()

k_maxr = Worksheets("矿点对应").Range("A1").End(xlDown).row
write_row = k_maxr + 1

kd = Worksheets("矿点对应").Range("A2:B" & k_maxr).Value

Dim kd_dc As Object
Set kd_dc = CreateObject("scripting.dictionary")

'Dim kd_dc As New Dictionary
Dim result As String

'MsgBox k_maxr

4、当前这个函数，当我们改变目标表（热值排序）中任何一位置的值时，函数都会被触发，接下来就是用函数来使只有改变某一区域值时，函数才会被触发。

If (Not Application.Intersect(Range("B2:B" & maxRow), Target) Is Nothing) And (Target.Address Like "*B*") Then
# intersect：用于测试两个区域之间是否有重叠。
# if函数嵌套 VBA自带函数intersect结合使用，用于判断“B2：B111”这块区域中的值是否发生改变，如若改变，则执行if下面语句块。
    'MsgBox Target.Address
    'MsgBox (Target.Address Like "*B*")

    For s = 1 To UBound(kd)
    # 介绍一下Ubound函数：拿到数组的下标上限值。
        If kd(s, 1) Like "*,*" Then
        # Like函数：正则表达式的应用，此语法表示当该值包含“,”时。
            temp_key = Replace(kd(s, 1), ",", "，")
            # Replace函数：替换函数，将值中的“,”，替换为“，”。
        Else
            temp_key = kd(s, 1)
        End If
        kd_dc(temp_key) = kd(s, 2)
        # 前面定义的字典，这儿用到了，key-val结构储存。
    Next
    
    For Each single_cell In Target
    # 循环函数,此例中用于便利我们选择的所有值，有时候我们会复制多个值过来，所以这边需要将其遍历操作
        now_row = single_cell.row
        'MsgBox now_row
        
        If single_cell.Column = 2 Then
        
            'On Error Resume Next
            bl = single_cell.Value Like "*,*"
            'MsgBox bl
        
            If bl Then
                result = Replace(single_cell.Value, ",", "，")
            Else:
                result = single_cell.Value
            End If
            'MsgBox result
            'MsgBox now_row
            'MsgBox kd_dc(result)
            
            If kd_dc(result) = "" Then
                'MsgBox single_cell.Value
                Worksheets("矿点对应").Range("A" & write_row).Value = result
                write_row = write_row + 1
                Cells(now_row, 3).Value = ""
            Else
                Cells(now_row, 3).Value = kd_dc(result)
            End If
            
        End If
        'On Error GoTo 0
        'If single_cell.Value = "" Then MsgBox Cells(now_row, 3).Value
    Next
    # 这半段函数与上面介绍的大同小异，最终的结果时生成两个字典，并将改变之后的对应值写到相应单元格 
End If

5、我们看看另个操作吧。

If (Not Application.Intersect(Range("C2:C" & maxRow), Target) Is Nothing) And (Target.Address Like "*C*") Then
# 此处用法如上
    For e = 1 To UBound(keys)
        temp = keys(e, 2) + keys(e, 1)
        # 这儿拼接两个单元格中的值作为一个字符串，作为key，对其赋值。
        use(temp) = keys(e, 3)
    Next
    
    
    For Each single_cell In Target
        now_row = single_cell.row
        If single_cell.Column = 3 Then
            If single_cell.Value = "" Then
                Range("S" & now_row).Value = ""
                Range("T" & now_row).Value = ""
            Else
                Range("S" & now_row).Value = use(single_cell.Value & "乌达")
                Range("T" & now_row).Value = use(single_cell.Value & "蒙西")
                # 这儿巧妙的运用组合将两个值结合成一个值，然后用字典索引找到其对应的值写入单元格。
            End If
        End If
    Next
    
End If

进阶操作

其进阶操作有使用EXCEL做简单的人才管理操作界面，以及用命令栏的方式加入自己常使用的VBA代码，使其可以像：开始——>字体，这样操作而已。最主要的，这些东西可以很方便的用其他代码工具来实现，目前偏向于简单的html搭配JavaScript以及mysql就可以简单的搭建一个操作界面了，绑定域名，传输Html文件后就可使用，而且交流性明显强于Excel。这个是我当时学习html时写的备忘录，可下载源代码查看。备忘录（审美有点差，页面设计比较难看，见谅。）

再者，随着工作年限的增加，也许会用到VBA中的框架吧，其也不太难，就是绘画框架后，点击编写语言，感兴趣的可以自己学习。

工作只是副业，生活才是本职，各位切勿舍本逐末奥。

落寞红颜玉玫瑰

关注

24
点赞
踩
25

收藏

觉得还不错? 一键收藏
打赏
0
评论
关于VBA的简单使用

在实际工作或者学习中，Excel已经是我们离开不了的工具之一，其简单易学，提供基本的数据输入和计算功能，也支持复杂的数据分析、图表制作与呈现。本文章只介绍VBA的基本使用，更高级的界面呈现可以做，但没必要，在现在，随着互联网的快速发展，可以用python等配合mysql或者JAVAScript与mysql做出适合于小组作业的界面呈现。再回首，对这句“但见光阴流似箭头，岂知天道曲如弓”深有感触。你我只需：埋头将那光阴赶，莫说后来再发力，趁得闲来看百花，惊言已过五六年。
复制链接

扫一扫