最近接触了一下VBA编程,才知道Excel还能编程,而且还如此强大,真的是惊呆了,话不多说,先了解一下VBA常用的语法吧。 1、基础语法 //高级for循环 Function deleteArrayByIndex(list) As String Dim item For Each item In list MsgBox item Next item End Function //获取当前sheet的名称和位置 sheetIndex = ActiveSheet.index sheetName = ActiveSheet.Name //获取最后一行 Dim sheetIndex As Integer lastRow = Sheets(sheetName).UsedRange.Rows.Count //删除多行 Rows(“7:12”).Delete Range(“A12:Q12”).Clear Range(“A12:Q12”).ClearContents //插入一行 Sheets(sheetName).Rows(10).Insert //为某单元格赋值 Range(“B” & 1).Select Selection.formula = “D” //contains,包含 If InStr(str, “zhangsan”) <> 0 Then Else End If //for循环 + Replace For i = 0 To UBound(arr) arr(i) = Replace(Trim(arr(i)), “,”, “”) arr(i) = Replace(valueArr(i), “;”, “”) Next i //Split list = “1,2,3,4” arr = VBA.Split(list, “,”) //获取某单元格公式 Range(“A12”).formula //return exit function(函数) exit sub(事件过程) //截取字符串 Mid(a, 2, 3) '提取第二个字符开始之后的3个字符 //获取字符串的首字符 Left(month, 1) //获取其它sheet单元格数据(第6行第21列) Sheet1.Cells(6, 21).value //获取单元格背景色 Range(“A1”).Interior.ColorIndex //设置单元格背景颜色 For Each item In Range(Range(“A1”), Range(“H10”)) If item.value = 0 Then item.Interior.ColorIndex = 10 ElseIf item.value = 1 Then item.Interior.ColorIndex = 11 End If Next //赋值 Dim a As range Set a = Sheets(sheetName).range(“A1:C6”) a.Borders.LineStyle = 1//表格线宽度 a.Font.ColorIndex = 1//颜色 a.Font.Name = “Arial”//字体 a.HorizontalAlignment = xlCenter//居中 //计算方法耗时 Dim t As Single t = Timer Timer - t ———————————————— 版权声明:本文为CSDN博主「GooReey」的原创文章,遵循CC 4.0 BY-SA版权协议,转载请附上原文出处链接及本声明。 原文链接https://blog.csdn.net/guorui_java/article/details/112632442客 发布于2021-04-12 著作权归作者所有