Vba

在传统行业，用Excel做表比通过Python操作更加广泛，Excel也能够通过VBA实现报表自动化：自动更新数据，用公式/代码生成点评，通过Outlook主动群发邮件，嵌入代码的xlsm能够间接发送给其他人应用，无需打包成exe...于是企微机器人传值这种Python几行代码的事件也只能捏着鼻子上那么一百几十行VBA了。 本文代码解决流程中3个次要步骤： 将Excel工作表的指定区域保留为图片获取保留的图片的MD5和Base64将MD5和Base64组成json格局发送企微提供的Webhook（一）保留图片示例代码保留的图片有可能为空白，能够减少文件大小校验。 Public Function RangeToPic(Rng As Range) '应用以后文件所在门路作为输入门路 Pth = ActiveWorkbook.Path '应用【文件名_区域地址】作为输入文件名 Pnm = Left(ActiveWorkbook.Name, InStrRev(ActiveWorkbook.Name, ".") - 1) & "_" & Replace(Rng.Address(0, 0), ":", "_") '把抉择范畴内容转化为截屏图片信息 Rng.CopyPicture Appearance:=xlScreen, Format:=xlBitmap With ActiveSheet.ChartObjects.Add(0, 0, Rng.Width + 1, Rng.Height + 1).Chart .ChartArea.Border.LineStyle = 0 .Parent.Select .Paste .Export Pth & "\" & Pnm & ".png", "PNG" .Parent.Delete End WithEnd Function（二）用VBA获取文件MD5倡议封装为模块，调用MD5File(文件门路) Option ExplicitType MD5_CTX dwNUMa As Long dwNUMb As Long Buffer(15) As Byte cIN(63) As Byte cDig(15) As ByteEnd TypePrivate Declare PtrSafe Sub MD5Init Lib "advapi32" (lpContext As MD5_CTX)Private Declare PtrSafe Sub MD5Final Lib "advapi32" (lpContext As MD5_CTX)Private Declare PtrSafe Sub MD5Update Lib "advapi32" (lpContext As MD5_CTX, ByRef lpBuffer As Any, ByVal BufSize As Long)Private stcContext As MD5_CTX'计算一个字符串（ANSI编码）的MD5码：输出字符串文本，返回MD5码（16字节的Byte数组）Public Function MD5String(strText As String) As Byte() Dim aBuffer() As Byte Call MD5Init(stcContext) If (Len(strText) > 0) Then aBuffer = StrConv(strText, vbFromUnicode) Call MD5Update(stcContext, aBuffer(0), UBound(aBuffer) + 1) Else Call MD5Update(stcContext, 0, 0) End If Call MD5Final(stcContext) MD5String = stcContext.cDigEnd Function'计算一个字节流的MD5码：输出Byte数组和长度（可选，默认计算整个长度），返回MD5码 （16字节的Byte数组）Public Function MD5Bytes(Buffer() As Byte, _ Optional ByVal size As Long = -1) As Byte() Dim U As Long, pBase As Long pBase = LBound(Buffer) U = UBound(Buffer) - pBase If (-1 = size) Then size = U + 1 Call MD5Init(stcContext) If (-1 = U) Then Call MD5Update(stcContext, 0, 0) Else Call MD5Update(stcContext, Buffer(pBase), size) End If Call MD5Final(stcContext) MD5Bytes = stcContext.cDigEnd Function'计算一个文件的MD5码：输出磁盘文件名（残缺门路），返回MD5码 （16字节的Byte数组）Public Function MD5File(ByVal FileName As String) As Byte() Const BUFFERSIZE As Long = 1024& * 512 ' 缓冲区 512KB Dim DataBuff() As Byte Dim lFileSize As Long Dim iFn As Long On Error GoTo E_Handle_MD5 If (Len(Dir$(FileName)) = 0) Then Err.Raise 5 '文件不存在 ReDim DataBuff(BUFFERSIZE - 1) iFn = FreeFile() Open FileName For Binary As #iFn lFileSize = LOF(iFn) Call MD5Init(stcContext) If (lFileSize = 0) Then Call MD5Update(stcContext, 0, 0) Else Do While (lFileSize > 0) Get iFn, , DataBuff If (lFileSize > BUFFERSIZE) Then Call MD5Update(stcContext, DataBuff(0), BUFFERSIZE) Else Call MD5Update(stcContext, DataBuff(0), lFileSize) End If lFileSize = lFileSize - BUFFERSIZE Loop End If Close iFn Call MD5Final(stcContext)E_Handle_MD5: MD5File = stcContext.cDigEnd Function（三）用VBA获取图片Base64调用EncodeFilebase64(文件门路) ...

1.进入Excel，点击选项 2.点击自定义功能区 3.勾选开发工具 4.点击确定 5.在页面上点击开发工具 6.点击visual basic 7.页面就进去了 想要编程的话就在菜单栏上抉择插入，点击模块就能够了

昨天收到一个需求，根据要求生成每个号码的等级（图片）下面是最后做出的效果下面是VBA代码’作者：梁茂业 2019年3月7日Sub replacePhone1() ‘定义起始行 START_ROW = 2 ‘定义等级 Dim Rng2 Dim level Dim level_1 Dim level_2 Dim level_3 level_1 = Array(1, 2, 1, 2, 3, 3, 4, 5, 4, 5, 7) level_2 = Array(0, 1, 1, 2, 3, 1, 2, 3, 3, 4, 5) level_3 = Array(0, 0, 1, 2, 3, 1, 2, 3, 3, 4, 5) Set regx = CreateObject(“vbscript.regexp”) regx.Global = True Set Rng = Range(“a3:a” & Cells(Rows.Count, 1).End(xlUp).Row) Rng2 = Sheet2.Range(“a2:g” & Sheet2.Cells(Rows.Count, 1).End(xlUp).Row) For Each rn In Rng n = n + 1 ‘基础匹配 是否是手机号 regx.Pattern = “^1\d{10}$” If regx.Test(rn.Value) Then ‘判断号码级别 level = level_3 regx.Pattern = “^(1380772|1380782|1390772|1390782|1980772|1987720|1987722|1987723|1987724|1987725|1987726|1987727|1987728|1987729)” ‘第一级 If regx.Test(rn.Value) Then level = level_1 End If regx.Pattern = “^(1350772|1360772|1387720|1387721|1387722|1387723|1387724|1387725|1387726|1387727|1387728|1387729|1387820|1387821|1387822|1387823|1387824|1387825|1387826|1387827|1387828|1387829|1397720|1397721|1397722|1397723|1397724|1397725|1397726|1397727|1397728|1397729|1397820|1397821|1397822|1397823|1397824|1397825|1397826|1397827|1397828|1397829|)” ‘第二级 If regx.Test(rn.Value) Then level = level_2 End If ‘判断局向 n2 = 0 For i = 1 To UBound(Rng2, 1) n2 = n2 + 1 n3 = Rng2(n2, 3) n4 = Rng2(n2, 4) If rn.Value >= Rng2(n2, 3) And rn.Value <= Rng2(n2, 4) Then Cells(n + START_ROW, 3) = Rng2(n2, 7) GoTo area End If Next area: ‘尾数顺位9位 regx.Pattern = “(?:0(?=1)|1(?=2)|2(?=3)|3(?=4)|4(?=5)|5(?=6)|6(?=7)|7(?=8)|8(?=9)){8}\d” If regx.Test(Right(rn.Value, 9)) Then Cells(n + START_ROW, 4) = “尾数顺位9位” Cells(n + START_ROW, 2) = “99” GoTo break End If ‘尾数顺位8位 regx.Pattern = “(?:0(?=1)|1(?=2)|2(?=3)|3(?=4)|4(?=5)|5(?=6)|6(?=7)|7(?=8)|8(?=9)){7}\d” If regx.Test(Right(rn.Value, 8)) Then Cells(n + START_ROW, 4) = “尾数顺位8位” Cells(n + START_ROW, 2) = “99” GoTo break End If ‘尾数顺位7位 regx.Pattern = “(?:0(?=1)|1(?=2)|2(?=3)|3(?=4)|4(?=5)|5(?=6)|6(?=7)|7(?=8)|8(?=9)){6}\d” If regx.Test(Right(rn.Value, 7)) Then Cells(n + START_ROW, 4) = “尾数顺位7位” Cells(n + START_ROW, 2) = “99” GoTo break End If ‘尾数连号9位 regx.Pattern = “([\d])\1{8,}” If regx.Test(Right(rn.Value, 9)) Then Cells(n + START_ROW, 4) = “尾数连号9位” Cells(n + START_ROW, 2) = “99” GoTo break End If ‘尾数连号8位 regx.Pattern = “([\d])\1{7,}” If regx.Test(Right(rn.Value, 8)) Then Cells(n + START_ROW, 4) = “尾数连号8位” Cells(n + START_ROW, 2) = “99” GoTo break End If ‘尾数连号7位 regx.Pattern = “([\d])\1{6,}” If regx.Test(Right(rn.Value, 7)) Then Cells(n + START_ROW, 4) = “尾数连号7位” Cells(n + START_ROW, 2) = “99” GoTo break End If ‘尾数连号6位 尾号6、8、9 regx.Pattern = “([6|8|9])\1{5}” If regx.Test(Right(rn.Value, 6)) Then Cells(n + START_ROW, 4) = “尾数连号6位 尾号6、8、9” Cells(n + START_ROW, 2) = “89” GoTo break End If ‘尾数连号6位 尾号非6、8、9 regx.Pattern = “([\d])\1{5,}” If regx.Test(Right(rn.Value, 6)) Then Cells(n + START_ROW, 4) = “尾数连号6位 尾号非6、8、9” Cells(n + START_ROW, 2) = “79” GoTo break End If ‘尾数顺位6位 regx.Pattern = “(?:0(?=1)|1(?=2)|2(?=3)|3(?=4)|4(?=5)|5(?=6)|6(?=7)|7(?=8)|8(?=9)){5}\d” If regx.Test(Right(rn.Value, 6)) Then Cells(n + START_ROW, 4) = “尾数顺位6位” Cells(n + START_ROW, 2) = “79” GoTo break End If ‘尾数连号5位 尾号6、8、9 regx.Pattern = “([6|8|9])\1{4}” If regx.Test(Right(rn.Value, 5)) Then Cells(n + START_ROW, 4) = “尾数连号5位 尾号6、8、9” Cells(n + START_ROW, 2) = “69” GoTo break End If ‘尾数连号6位 尾号非6、8、9 regx.Pattern = “([\d])\1{4,}” If regx.Test(Right(rn.Value, 5)) Then Cells(n + START_ROW, 4) = “尾数连号5位 尾号非6、8、9” Cells(n + START_ROW, 2) = “59” GoTo break End If ‘尾数顺位5位 regx.Pattern = “(?:0(?=1)|1(?=2)|2(?=3)|3(?=4)|4(?=5)|5(?=6)|6(?=7)|7(?=8)|8(?=9)){4}\d” If regx.Test(Right(rn.Value, 5)) Then Cells(n + START_ROW, 4) = “尾数顺位5位” Cells(n + START_ROW, 2) = “59” GoTo break End If ‘尾数连号4位 尾号6、8、9 regx.Pattern = “([6|8|9])\1{3}” If regx.Test(Right(rn.Value, 4)) Then Cells(n + START_ROW, 4) = “尾数连号4位 尾号6、8、9” Cells(n + START_ROW, 2) = “49” GoTo break End If ‘尾数连号4位 尾号非6、8、9 regx.Pattern = “([\d])\1{3,}” If regx.Test(Right(rn.Value, 4)) Then Cells(n + START_ROW, 4) = “尾数连号4位 尾号非6、8、9” Cells(n + START_ROW, 2) = “39” GoTo break End If ‘尾数顺位4位 regx.Pattern = “(?:0(?=1)|1(?=2)|2(?=3)|3(?=4)|4(?=5)|5(?=6)|6(?=7)|7(?=8)|8(?=9)){3}\d” If regx.Test(Right(rn.Value, 4)) Then Cells(n + START_ROW, 4) = “尾数顺位4位” Cells(n + START_ROW, 2) = “39” GoTo break End If ‘尾数连号3位 尾号6、8、9 regx.Pattern = “([6|8|9])\1{2}” If regx.Test(Right(rn.Value, 3)) Then Cells(n + START_ROW, 4) = “尾数连号3位 尾号6、8、9” Cells(n + START_ROW, 2) = “29” GoTo break End If ‘尾数连号3位 尾号非6、8、9 regx.Pattern = “([\d])\1{2,}” If regx.Test(Right(rn.Value, 3)) Then Cells(n + START_ROW, 4) = “尾数连号3位 尾号非6、8、9” Cells(n + START_ROW, 2) = “19” GoTo break End If ‘AABBCCDD AABBAABB regx.Pattern = “(.)\1{1}(.)\2{1}(.)\3{1}(.)\4{1}” If regx.Test(Right(rn.Value, 8)) Then Cells(n + START_ROW, 4) = “AABBCCDD AABBAABB” Cells(n + START_ROW, 2) = “7” GoTo break End If ‘中段5连号以上，且号码无4 regx.Pattern = “[0-35-9]+([0-35-9])\1{4}[0-35-9]*” If regx.Test(rn.Value) Then Cells(n + START_ROW, 4) = “中段5连号以上，且号码无4” Cells(n + START_ROW, 2) = “7” GoTo break End If ‘未4位和前4位一样 regx.Pattern = “([\d]{4})\1” If regx.Test(Right(rn.Value, 8)) Then Cells(n + START_ROW, 4) = “未4位和前4位一样” Cells(n + START_ROW, 2) = “6” GoTo break End If ‘尾号AABBCC C!=4 regx.Pattern = “([\d])\1{1}([\d])\2{1}([0-35-9])\3{1}” If regx.Test(Right(rn.Value, 6)) Then Cells(n + START_ROW, 4) = “尾号AABBCC C!=4” Cells(n + START_ROW, 2) = “6” GoTo break End If ‘尾数4位顺降 DCBA A!=4 regx.Pattern = “(?:9(?=8)|8(?=7)|7(?=6)|6(?=5)|5(?=4)|4(?=3)|3(?=2)|2(?=1)|1(?=0)){3}\d” If Right(rn.Value, 1) <> 4 Then If regx.Test(Right(rn.Value, 4)) Then Cells(n + START_ROW, 4) = “尾数4位顺降 DCBA A!=4” Cells(n + START_ROW, 2) = “6” GoTo break End If End If ‘============================================================== ‘尾数ABAB A或B等于4 regx.Pattern = “4” If regx.Test(Right(rn.Value, 4)) Then regx.Pattern = “(\d{2})\1” If regx.Test(Right(rn.Value, 4)) Then Cells(n + START_ROW, 4) = “尾数ABAB A或B等于4” Cells(n + START_ROW, 2) = level(8) GoTo break End If End If ‘尾数AABB A或B等于4 regx.Pattern = “4” If regx.Test(Right(rn.Value, 4)) Then regx.Pattern = “(\d)\1{1}(\d)\2{1}” If regx.Test(Right(rn.Value, 4)) Then Cells(n + START_ROW, 4) = “尾数AABB A或B等于4” Cells(n + START_ROW, 2) = level(8) GoTo break End If End If ‘尾数AAAAB A或B等于4 regx.Pattern = “4” If regx.Test(Right(rn.Value, 5)) Then regx.Pattern = “(\d)\1{3}\d+” If regx.Test(Right(rn.Value, 5)) Then Cells(n + START_ROW, 4) = “尾数AAAAB A或B等于4” Cells(n + START_ROW, 2) = level(8) GoTo break End If End If ‘尾数AAAB A或B等于4 regx.Pattern = “4” If regx.Test(Right(rn.Value, 4)) Then regx.Pattern = “(\d)\1{2}\d+” If regx.Test(Right(rn.Value, 4)) Then Cells(n + START_ROW, 4) = “尾数AAAB A或B等于4” Cells(n + START_ROW, 2) = level(8) GoTo break End If End If ‘============================================================== ‘尾数ABAB A或B=6 8 9 regx.Pattern = “6|8|9” If regx.Test(Right(rn.Value, 4)) Then regx.Pattern = “(\d{2})\1” If regx.Test(Right(rn.Value, 4)) Then Cells(n + START_ROW, 4) = “尾数ABAB A或B=6 8 9” Cells(n + START_ROW, 2) = level(10) GoTo break End If End If ‘尾数AABB A或B=6 8 9 regx.Pattern = “6|8|9” If regx.Test(Right(rn.Value, 4)) Then regx.Pattern = “(\d)\1{1}(\d)\2{1}” If regx.Test(Right(rn.Value, 4)) Then Cells(n + START_ROW, 4) = “尾数AABB A或B=6 8 9” Cells(n + START_ROW, 2) = level(10) GoTo break End If End If ‘尾数AAAAB A或B=6 8 9 regx.Pattern = “6|8|9” If regx.Test(Right(rn.Value, 5)) Then regx.Pattern = “(\d)\1{3}\d+” If regx.Test(Right(rn.Value, 5)) Then Cells(n + START_ROW, 4) = “尾数AAAAB A或B=6 8 9” Cells(n + START_ROW, 2) = level(10) GoTo break End If End If ‘尾数AAAB A或B=6 8 9 regx.Pattern = “6|8|9” If regx.Test(Right(rn.Value, 4)) Then regx.Pattern = “(\d)\1{2}\d+” If regx.Test(Right(rn.Value, 4)) Then Cells(n + START_ROW, 4) = “尾数AAAB A或B=6 8 9” Cells(n + START_ROW, 2) = level(0) GoTo break End If End If ‘============================================================ ‘尾数ABAB A或B不等于4 6 8 9 regx.Pattern = “\d” If regx.Test(Right(rn.Value, 4)) Then regx.Pattern = “(\d{2})\1” If regx.Test(Right(rn.Value, 4)) Then Cells(n + START_ROW, 4) = “尾数ABAB A或B不等于4 6 8 9 " Cells(n + START_ROW, 2) = level(9) GoTo break End If End If ‘尾数AABB A或B不等于4 6 8 9 regx.Pattern = “\d” If regx.Test(Right(rn.Value, 4)) Then regx.Pattern = “(\d)\1{1}(\d)\2{1}” If regx.Test(Right(rn.Value, 4)) Then Cells(n + START_ROW, 4) = “尾数AABB A或B不等于4 6 8 9 " Cells(n + START_ROW, 2) = level(9) GoTo break End If End If ‘尾数AAAAB A或B不等于4 6 8 9 regx.Pattern = “\d” If regx.Test(Right(rn.Value, 5)) Then regx.Pattern = “(\d)\1{3}\d+” If regx.Test(Right(rn.Value, 5)) Then Cells(n + START_ROW, 4) = “尾数AAAAB A或B不等于4 6 8 9 " Cells(n + START_ROW, 2) = level(9) GoTo break End If End If ‘尾数AAAB A或B不等于4 6 8 9 regx.Pattern = “\d” If regx.Test(Right(rn.Value, 4)) Then regx.Pattern = “(\d)\1{2}\d+” If regx.Test(Right(rn.Value, 4)) Then Cells(n + START_ROW, 4) = “尾数AAAB A或B不等于4 6 8 9 " Cells(n + START_ROW, 2) = level(9) GoTo break End If End If ‘尾号AA A= 4 regx.Pattern = “(4)\1” If regx.Test(Right(rn.Value, 2)) Then Cells(n + START_ROW, 4) = “尾号AA A=4” Cells(n + START_ROW, 2) = level(5) GoTo break End If ‘尾号AA A= 6 8 9 regx.Pattern = “([6|8|9])\1” If regx.Test(Right(rn.Value, 2)) Then Cells(n + START_ROW, 4) = “尾号AA A= 6 8 9” Cells(n + START_ROW, 2) = level(7) GoTo break End If ‘尾号AA A不等于 4 6 8 9 regx.Pattern = “(\d)\1” If regx.Test(Right(rn.Value, 2)) Then Cells(n + START_ROW, 4) = “尾号AA A不等于 4 6 8 9” Cells(n + START_ROW, 2) = level(6) GoTo break End If ‘尾数3位正顺号 ABC C不等于4 regx.Pattern = “(?:0(?=1)|1(?=2)|2(?=3)|3(?=4)|4(?=5)|5(?=6)|6(?=7)|7(?=8)|8(?=9)){2}\d” If Right(rn.Value, 1) <> 4 Then If regx.Test(Right(rn.Value, 3)) Then Cells(n + START_ROW, 4) = “尾数3位正顺号 ABC C不等于4” Cells(n + START_ROW, 2) = level(4) GoTo break End If End If ‘尾号末两位 18 58 68 98 regx.Pattern = “18|58|68|98” If regx.Test(Right(rn.Value, 2)) Then Cells(n + START_ROW, 4) = “尾号末两位 18 58 68 98” Cells(n + START_ROW, 2) = level(3) GoTo break End If ‘尾号一个8 regx.Pattern = “8” If regx.Test(Right(rn.Value, 1)) Then Cells(n + START_ROW, 4) = “尾号一个8” Cells(n + START_ROW, 2) = level(2) GoTo break End If ‘后四位带4 regx.Pattern = “4” If regx.Test(Right(rn.Value, 4)) Then Cells(n + START_ROW, 4) = “后四位带4” Cells(n + START_ROW, 2) = “100分” Cells(n + START_ROW, 2) = level(0) GoTo break End If ‘后四位不带4 regx.Pattern = “[0-35-9]” If regx.Test(Right(rn.Value, 4)) Then Cells(n + START_ROW, 4) = “后四位不带4” Cells(n + START_ROW, 2) = level(1) GoTo break End If Else Cells(n + START_ROW, 4) = “手机号格式不正确” Cells(n + START_ROW, 2) = “错误！！！” GoTo break End If break: NextEnd Sub ...