![](https://img-blog.csdnimg.cn/20190927151043371.png?x-oss-process=image/resize,m_fixed,h_224,w_224)
VBA代码片
VBA代码片
迪迦 • 奥特曼
C/C++ coder
展开
-
VBA提前退出function
提前退出函数正常情况下,函数使用 Function 和 End Function 语句作为函数的开始和结束。但有时根据实际情况,可能需要提前结束并退出函数。VBA 提供 2 种提前退出过程的方法,Exit Function 和 End 方法。Exit Function 语句在一个函数中,当程序运行到 Exit Function 语句时,立即结束当前函数,提前退出。这里需要注意的是,Exit Function 语句只作用于当前过程,不影响调用它的父过程或函数。End 语句在一个函数,当程序运行到转载 2022-05-21 08:23:47 · 3157 阅读 · 0 评论 -
VBA代码片之获取某列中随机一个数字
Function getRandNum(r As Range) Dim i As Integer Randomize i = Int(Rnd * r.Rows.Count + 1) getRandNum = r.Cells(i, 1).ValueEnd Function原创 2022-04-17 13:08:55 · 448 阅读 · 0 评论 -
vba代码片之获取范围中不同数值个数
Function total(r As Range) Dim i As Integer Dim myd As Object Set myd = CreateObject("Scripting.Dictionary") total = 0 For i = 1 To r.Rows.Count If myd.Exists(r.Cells(i, 1).Value) = False Then myd.Add r.Cells(i, 1).V原创 2022-04-17 13:07:39 · 1517 阅读 · 0 评论 -
VBA代码片之条件格式取重复值(半小时内,同一人出现两次及以上,就对此人进行标红)
Sub demo() Dim i As Long, j As Long, r As Range, name As String Dim d As Date, r1 As Range Set r = Range("B3").End(xlDown) Debug.Print r.Row name = Cells(3, 2).Value d = Cells(3, 4).Value i = 4 j = 4 Do While True原创 2022-04-16 21:58:48 · 1041 阅读 · 0 评论 -
VBA代码片之计算加权平均分
Option ExplicitSub run() Dim i As Integer, k As Integer, maxRow As Integer Dim r As Range k = 2 maxRow = 368 For i = 2 To maxRow + 1 If WorksheetFunction.CountA(Rows(i)) = 0 Then '说明此时为空行 Set r = Range(原创 2022-04-15 19:26:42 · 1059 阅读 · 0 评论 -
VBA代码片之单元格合并并设置、阿拉伯数字转汉字
Option ExplicitSub demo() Dim r As Range, i As Integer, k As Integer Dim t As String, r2 As Range Set r = Range("A2").End(xlDown) Debug.Print r.Row k = 1 For i = 2 To r.Row Step 10 Range("S" & i & ":S" & i + 9原创 2022-04-15 11:46:43 · 871 阅读 · 0 评论 -
VBA代码片之获取行列号
Sub demo() Dim r As Range, i As Integer Set r = Range("A1").End(xlDown) Debug.Print r.Row Set r = Range("A1").End(xlToRight) Debug.Print r.Column End Sub原创 2022-04-14 21:29:53 · 1666 阅读 · 0 评论 -
解除excel中的宏密码
解除excel中的宏密码第一步:将文件另存为2003版本的格式(.xls)第二步:用普通的文本编辑器打开2003版本的文件,文件类型为所有文件,我用的是Notepad++第三步:然后在文件里查找“DPB”修改为“DPx”,注意大小写第四步:保存后重新打开文件,会遇到一些错误,忽略第五步:打开Visual Basic的时候也会遇到一些错误,继续忽略第六步:从VBA编辑器的“工具”菜单,选择VBAProject属性(E),“保护”面板,输入新的密码,保存并关闭excel第七步:重新启动excel打转载 2022-04-11 16:23:55 · 5365 阅读 · 3 评论 -
VBA代码片之SQL数据表查询
Option ExplicitPublic Sub makeConn() '与文件建立数据库连接 Dim fileName, constr, sql As String Dim conn As Object Dim rs As Object Dim wb As Workbook Dim ws As Worksheet Dim arr Dim cat As Object Dim n As Integer Dim转载 2022-04-10 22:26:28 · 2526 阅读 · 0 评论 -
VBA代码片之txt文本每隔12行添加一个空行
Option ExplicitPublic Sub Btn_Click() 'demo "C:\Users\qwq\Desktop\177_1104812209\", "4000.txt" run ThisWorkbook.Path & "\"End SubSub run(folder) Dim fName, subFolders As Collection, t, i As Integer Dim fileName As String, ext As S原创 2022-04-09 10:07:05 · 751 阅读 · 0 评论 -
VBA代码片之隔6行上色
Sub demo() Dim r As Range, i As Integer, j As Integer Dim Flag As Boolean Flag = True For i = 3 To 56 j = j + 1 If j = 7 Then j = 1 '注意,这里是1,如果为0的话,在第二次给颜色的时候,会成为7行 Flag = Not Flag End If I原创 2022-04-08 20:46:01 · 597 阅读 · 0 评论 -
VBA代码片之跨SQL处理工作表数据-插入
Option ExplicitSub runSql() Dim Sql1 As String, Sql2 As String, Sql3 As String, t As String, Sql As String Dim conn As Object, i As Integer Dim fileName As String Set conn = CreateObject("ADODB.Connection") '创建一个连接对象 fileName原创 2022-04-08 18:15:33 · 884 阅读 · 0 评论 -
VBA代码片之跨工作表复制数据
Option ExplicitSub run() Dim r1 As Range, i As Integer, j As Integer Set r1 = Worksheets("混凝土").Range("A3:F490") For i = 1 To r1.Rows.Count Worksheets("Template").Copy After:=Worksheets("Template") Worksheets("Template原创 2022-04-07 22:30:07 · 2955 阅读 · 0 评论 -
VBA代码片之一键去公式
Option ExplicitSub Btn1_Click() Dim app As Object Set app = New Application Application.Calculation = xlCalculationManual Application.DisplayAlerts = False run ThisWorkbook.path & "\", app MsgBox "处理完成" End SubSub run(f原创 2022-04-07 21:11:11 · 706 阅读 · 0 评论 -
VBA代表片之当前活动表中所有公式数据转为普通数据
当前活动表中所有公式数据转为普通数据Sub demo() Dim r As Range, r1 As Range Dim s As String Dim t Application.Calculation = xlCalculationManual For Each r In ActiveSheet.UsedRange 'For Each r In Range("F11:H14") 'Debug.Print r.Address原创 2022-04-07 12:17:01 · 183 阅读 · 0 评论