效率狂魔!Excel VBA零门槛代码合集
前几天看到一句话: Excel 除了不能生孩子,其他都可以。 毫无疑问, 作为世界使用率最高的软件,或许写文稿你可以不用Word,但面对数据收集和处理时,99%的用户首先想到是Excel。
Excel三大核心功能,函数,数据透视表,VBA。 每一项都能大幅提升你的工作效率。
为什么要用VBA?
入门VBA的前提是你需要对Excel界面和函数有初步的了解。 例如最基本的调节单元格大小,调整页面,公式求和,求平均值,或是利用公式查找和统计数据等。
但最基本的调整和公式计算并不是万能的,尤其是数据超过几千行,或者在几十张不同数据表内时,即便是最基本的数据复制粘贴都会令你崩溃,当你在做这种重复劳动时,你一定会有种“能不能给老子(娘)聪明点”的想法。
而VBA往往10行内的代码就能解决你几百上千次重复工作。 它最大的优势是处理大量数据时避免重复和错误。
如何使用VBA?
建立独立模块大致步骤为: 菜单--开发工具--Visual Basic---在左侧空白处右击---插入---模块
当然VBA还分为独立模块和工作表事件等。独立的模块命运通常需要建立按钮来运行。工作表事件是指当工作表进行点击,激活,双击等动作时命令自动运行。
常规的独立命令 我们通过插入按钮并指定命令(宏)就可以完成。 工作表事件的代码运行会在下面的案例中演示。
而具有VBA代码的Excel文件,在保存方式上也与常规表格不同。 需要注意的是,在Office2007以上版本时,我们需要将它保存为.xlsm格式的文件。
零门槛的入门VBA代码
当然VBA的学习也是需要时间成本的。如果每天忙到爆炸,可想而知你学习VBA的可能性就基本为零了。 而VBA的好处在于你不需要浪费额外时间安装复杂的软件环境,直接在Excel内操作即可,而且有相当多的代码是通用的,不需要针对自己的实际情况编写程序。
有没有现成可用的VBA代码可用? 当然有。
1. 选定区域的单元格的列宽和行高自动调整为最合适的值
Sub 自动调整行列宽()
With ActiveWindow.RangeSelection
.Columns.AutoFit
.Rows.AutoFit
End With
End Sub
2.选区录入当前日期
Sub 区域录入当前日期()
Selection.FormulaR1C1 = Format(Now(), "yyyy-m-d")
End Sub
3.聚光灯效果(工作表事件代码)
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
请注意,这条是工作表事件命令。 需要点击工作表后插入对应代码。 Thisworkbook 表示整个工作表, 如果你只想在Sheet1里运行该代码则进入Shee1插入该代码。 代码中的SheetSelectionChange 就代表当单元格选择改变后就运行该命令。
4. 在当前选区有条件替换数值为文本
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. 自动备份
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.根据单元格自动创建文件夹
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列
Sub 将Sheet1的A列的非空值写到Sheet2的A列()
Sheet1.Columns("A:A").SpecialCells(2, 23).SpecialCells(12).Copy Sheet2.[A1] ‘将Sheet1的A列的非空值写到Sheet2的A列
End Sub
8.将所在列所有图片宽度调整为所在单元大小
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.自建函数,将数值转换成大写人民币
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就显得容易的多。 这两者在语句和结构上颇为相似,上手很快。
乄十四行诗
校验提示文案
it1580
校验提示文案
robinsherlockli
校验提示文案
jencs
校验提示文案
烈日冰峰
校验提示文案
刘律师_靠你了
要我写代码依然一脸懵逼
看网上自动生成询证函模板代码,就是for和if的应用。但是看不太懂
校验提示文案
少年阿滋猫
校验提示文案
少年阿滋猫
校验提示文案
it1580
校验提示文案
乄十四行诗
校验提示文案
刘律师_靠你了
要我写代码依然一脸懵逼
看网上自动生成询证函模板代码,就是for和if的应用。但是看不太懂
校验提示文案
烈日冰峰
校验提示文案
jencs
校验提示文案
robinsherlockli
校验提示文案