效率狂魔!Excel VBA零门槛代码合集

2020-02-17 11:02:26 25点赞 229收藏 6评论

前几天看到一句话: Excel 除了不能生孩子,其他都可以。 毫无疑问, 作为世界使用率最高的软件,或许写文稿你可以不用Word,但面对数据收集和处理时,99%的用户首先想到是Excel。

Excel三大核心功能,函数,数据透视表,VBA。 每一项都能大幅提升你的工作效率。  

为什么要用VBA?

效率狂魔!Excel VBA零门槛代码合集

入门VBA的前提是你需要对Excel界面和函数有初步的了解。 例如最基本的调节单元格大小,调整页面,公式求和,求平均值,或是利用公式查找和统计数据等。

但最基本的调整和公式计算并不是万能的,尤其是数据超过几千行,或者在几十张不同数据表内时,即便是最基本的数据复制粘贴都会令你崩溃,当你在做这种重复劳动时,你一定会有种“能不能给老子(娘)聪明点”的想法。

而VBA往往10行内的代码就能解决你几百上千次重复工作。 它最大的优势是处理大量数据时避免重复和错误。

如何使用VBA?

效率狂魔!Excel VBA零门槛代码合集

建立独立模块大致步骤为: 菜单--开发工具--Visual Basic---在左侧空白处右击---插入---模块

当然VBA还分为独立模块和工作表事件等。独立的模块命运通常需要建立按钮来运行。工作表事件是指当工作表进行点击,激活,双击等动作时命令自动运行。

效率狂魔!Excel VBA零门槛代码合集

常规的独立命令 我们通过插入按钮并指定命令(宏)就可以完成。   工作表事件的代码运行会在下面的案例中演示。

效率狂魔!Excel VBA零门槛代码合集

 而具有VBA代码的Excel文件,在保存方式上也与常规表格不同。 需要注意的是,在Office2007以上版本时,我们需要将它保存为.xlsm格式的文件。

零门槛的入门VBA代码

当然VBA的学习也是需要时间成本的。如果每天忙到爆炸,可想而知你学习VBA的可能性就基本为零了。 而VBA的好处在于你不需要浪费额外时间安装复杂的软件环境,直接在Excel内操作即可,而且有相当多的代码是通用的,不需要针对自己的实际情况编写程序。

有没有现成可用的VBA代码可用? 当然有。

1. 选定区域的单元格的列宽和行高自动调整为最合适的值

效率狂魔!Excel VBA零门槛代码合集

Sub 自动调整行列宽()

With ActiveWindow.RangeSelection     

.Columns.AutoFit     

.Rows.AutoFit   

End With   

End Sub

 

2.选区录入当前日期  

效率狂魔!Excel VBA零门槛代码合集

Sub 区域录入当前日期()

Selection.FormulaR1C1 = Format(Now(), "yyyy-m-d")

End Sub

 

3.聚光灯效果(工作表事件代码)

效率狂魔!Excel VBA零门槛代码合集

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)

Application.ScreenUpdating = False

Cells.Interior.ColorIndex = -4142

'取消单元格原有填充色,但不包含条件格式产生的颜色。

Rows(Target.Row).Interior.ColorIndex = 36

'活动单元格整行填充颜色

Columns(Target.Column).Interior.ColorIndex = 36

'活动单元格整列填充颜色(如不需要整列高亮,可删除该行命令)

Application.ScreenUpdating = False

End Sub

效率狂魔!Excel VBA零门槛代码合集

请注意,这条是工作表事件命令。 需要点击工作表后插入对应代码。 Thisworkbook 表示整个工作表, 如果你只想在Sheet1里运行该代码则进入Shee1插入该代码。   代码中的SheetSelectionChange 就代表当单元格选择改变后就运行该命令。

4. 在当前选区有条件替换数值为文本

效率狂魔!Excel VBA零门槛代码合集

Sub 在当前选区有条件替换数值为文本()

For Each r In Selection

If r.Value > 18 And r.Value < 30 Then r.Value = "Y"     '如果数值大于18,小于30,则把数值替换为Y.

Next

End Sub

 

5. 自动备份

效率狂魔!Excel VBA零门槛代码合集

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

On Error Resume Next

Dim mypath As String, fname As String

fname = Format(Now, "yymmddhhmmss") & ThisWorkbook.Name  ' 以当前事件加本文件名格式保存备份文件

mypath = ThisWorkbook.Path & "/备份/"        '保存目录为当前文件目录的备份文件夹

ThisWorkbook.SaveCopyAs mypath & fname

End Sub

 请注意:这是工作表命令,需要插入到Thisworkbook工作表中。  

6.根据单元格自动创建文件夹

效率狂魔!Excel VBA零门槛代码合集

Sub 创建文件夹()

Dim d As Object

Set d = CreateObject("wscript.shell")

d.Run ("cmd.exe /k md e:" & [a1] & ""), vbHide    '根据a1单元格在E盘目录下创建文件夹

End Sub

7.将Sheet1的A列的非空值写到Sheet2的A列

效率狂魔!Excel VBA零门槛代码合集

Sub 将Sheet1的A列的非空值写到Sheet2的A列()

Sheet1.Columns("A:A").SpecialCells(2, 23).SpecialCells(12).Copy Sheet2.[A1]    ‘将Sheet1的A列的非空值写到Sheet2的A列

End Sub

8.将所在列所有图片宽度调整为所在单元大小

效率狂魔!Excel VBA零门槛代码合集

Sub 将A列最后数据行以上的所有B列图片大小调整为所在单元大小()

Dim Pic As Picture, i&

i = [A65536].End(xlUp).Row

For Each Pic In Sheet1.Pictures

If Not Application.Intersect(Pic.TopLeftCell, Range("B1:B" & i)) Is Nothing Then

Pic.Top = Pic.TopLeftCell.Top

Pic.Left = Pic.TopLeftCell.Left

Pic.Height = Pic.TopLeftCell.Height

Pic.Width = Pic.TopLeftCell.Width

End If

Next

End Sub

 

9.自建函数,将数值转换成大写人民币

效率狂魔!Excel VBA零门槛代码合集

Public Function N2RMB(Number As Double) As String

If IsNull(Number) = True Then

    N2RMB = "0"

    Exit Function

End If

Dim j, k, l, last As Integer

Dim n As Double

Dim C1, C2, X As String

C1 = "零壹贰叁肆伍陆柒捌玖"

C2 = "分角元拾佰仟万拾佰仟亿拾佰"

last = 1

n = Round(Abs(Number), 2) * 100

l = Len(CStr(n))

last = 1

For j = 1 To Len(CStr(n))

    'k为右边算起的第j位的数字

    k = Mid(n, Len(CStr(n)) + 1 - j, 1)

    If k > 0 Then

      X = Mid(C1, k + 1, 1) & Mid(C2, j, 1) & X

      last = 1

    Else

      Select Case j

         Case 1

         Case 3

           X = "元" & X

         Case 7

           If Len(CStr(n)) < 11 Then

             X = "万" & X

           Else

             If Mid(CStr(n), Len(CStr(n)) - 9, 4) <> "0000" Then

                X = "万" & X

             End If

           End If

         Case 11

           X = "亿" & X

         Case Else

           If last = 1 Then

           X = "零" & X

           End If

      End Select

    last = 0

    End If

    If j = 2 And Right(n, 2) = 0 Then

       X = X & "整"

    End If

Next j

N2RMB = X

End Function

 这是条自建函数代码。后续可以在表格中输入函数名称就可以执行,如本条的函数名称为N2RMB。

VBA难学吗?

VBA作为VB语言的一个子集,算是一门古老的语言了。由于VBA只用于Office的各应用程序中,如Word、 Excel、 Access等,它的代码是精简过的,这就降低了用户的学习难度。 简单来说,当你学会了条件语句(IF),  循环语句(For)单元格(Range), 数组(Arr),字典 (Dictionary)这5个命令你就足够应付工作中大多数场合了。

当然它的难度不在于对于命令的理解,而是如何将代码组合到实际案例中。  

插句题外话. 近2年Python的教学被炒的火热,什么办公自动化,Python Excel自动化... 但当你搜索具体案例时,却发现能用于实践的少之又少。 去年我就是被一脸懵逼的骗进去,然后一脸懵逼的出来的。 (不接受反驳)

另外如果你学了VBA,那么再学习Python就显得容易的多。 这两者在语句和结构上颇为相似,上手很快。

展开 收起

Office 365 家庭版

Office 365 家庭版

259元起

微软 (Microsoft) Office 家庭学生版 2016 激活密钥 Mac专用 正版办公软件 一次购买 永久使用 非商业使用

微软 (Microsoft) Office 家庭学生版 2016 激活密钥 Mac专用 正版办公软件 一次购买 永久使用 非商业使用

5元起

微软 Office 家庭和学生版 2019 电子下载版 即买即用 一次性购买 正版授权 适用于Windows 10 PC/Mac 非商用

微软 Office 家庭和学生版 2019 电子下载版 即买即用 一次性购买 正版授权 适用于Windows 10 PC/Mac 非商用

209元起

微软 Office 365 个人版 1年订阅 电子下载版 即买即用 1账号登录5台设备 Windows PC/Mac/平板/手机均适用

微软 Office 365 个人版 1年订阅 电子下载版 即买即用 1账号登录5台设备 Windows PC/Mac/平板/手机均适用

249元起

微软 Office 365 家庭版 1年订阅 电子下载版 即买即用 6账号共享30台设备 Windows PC/Mac/平板/手机均适用

微软 Office 365 家庭版 1年订阅 电子下载版 即买即用 6账号共享30台设备 Windows PC/Mac/平板/手机均适用

279元起

《Money Pro》 记账理财软件

《Money Pro》 记账理财软件

6元起

《1Password》 ios密码管理软件

《1Password》 ios密码管理软件

暂无报价

《Agenda》Mac数字版软件

《Agenda》Mac数字版软件

暂无报价

《vegas pro 14 edit》视频编辑软件

《vegas pro 14 edit》视频编辑软件

132元起

用友软件 好会计财务软件专业版追加会计角色1用户/年 畅捷通用友T3网页版 记账宝易代账云会计ERP软件

用友软件 好会计财务软件专业版追加会计角色1用户/年 畅捷通用友T3网页版 记账宝易代账云会计ERP软件

301元起

用友财务软件 好会计云财务软件 畅捷通用友T3网页版 专业版试用30天

用友财务软件 好会计云财务软件 畅捷通用友T3网页版 专业版试用30天

1元起

ps cc2019 零基础自学 视频教程

ps cc2019 零基础自学 视频教程

19.9元起

远平条码 YPLABEL免驱不干胶标签条码打印软件

远平条码 YPLABEL免驱不干胶标签条码打印软件

400元起

用友财务软件 好会计普及版 畅捷通T3在线版会计记账软件 专业版30天试用

用友财务软件 好会计普及版 畅捷通T3在线版会计记账软件 专业版30天试用

498元起

用友财务软件 好会计专业版追加账套/年 畅捷通用友T3网页版 专业版试用30天

用友财务软件 好会计专业版追加账套/年 畅捷通用友T3网页版 专业版试用30天

50元起

用友财务软件 好会计专业版追加1个会计角色/年 畅捷通用友T3网页版 专业版试用30天

用友财务软件 好会计专业版追加1个会计角色/年 畅捷通用友T3网页版 专业版试用30天

300元起
6评论

发表评论请 登录
  • 最新
  • 最热
评论举报

请选择举报理由

相关文章推荐

更多精彩文章
更多精彩文章
最新文章 热门文章
229
扫一下,分享更方便,购买更轻松