VBA
piao3701
这个作者很懒,什么都没留下…
展开
-
VBA成绩录入
Option ExplicitDim endColumnNum As Integer, firstColumnNum As IntegerPrivate Sub Worksheet_SelectionChange(ByVal Target As Range) Dim temp As Variant temp = Application.InputBox("请输入:", Type:=1) Debug.Print temp If CStr(temp) = CStr(False原创 2022-01-08 18:47:23 · 310 阅读 · 0 评论 -
VBA弹窗打开文件,获取文件名以及文件全路径
Sub t() On Error GoTo myErro '当没有选择打开任何文件时会报错,所以添加异常处理 Dim fileToOpen() As Variant, i As Long, filePaths() As String '设置筛选器,可同时选择多个文件 fileToOpen = Application _ .GetOpenFilename(filefilter:="Files (*.txt;*.xlsx), *.txt;*.xlsx",原创 2022-01-07 21:35:29 · 4076 阅读 · 0 评论 -
VBA打印机设置(列出所有打印机名字、选用合适的打印机并设计端口号)
Sub PrinterDemo() Dim WshNetwork As Object Dim oPrinter As Object Dim i As Long Set WshNetwork = CreateObject("WScript.Network") Set oPrinter = WshNetwork.EnumPrinterConnections For i = 0 To oPrinter.Count - 1 If i Mod 2 = 0原创 2022-01-07 14:20:43 · 4758 阅读 · 0 评论 -
FindFormatAndReplaceFormat
Sub FindFormatAndReplaceFormatDemo() Application.FindFormat.Interior.ColorIndex = 6 Application.ReplaceFormat.Interior.ColorIndex = 14 Cells.Replace what:="", Replacement:="", SearchFormat:=True, ReplaceFormat:=True '最后一句是关键,如果不写则起不到作用End原创 2022-01-07 13:35:17 · 141 阅读 · 0 评论 -
VBA炸弹游戏
Option ExplicitSub RePlay() Dim rng As Range, i As Long, j As Long Set rng = Worksheets(1).Range("B2:J11") rng.ClearContents rng.Interior.Color = xlNone For i = 2 To 11 For j = 2 To 10 If Rnd() > 0.9 Then Cells(i原创 2022-01-05 17:51:45 · 2231 阅读 · 0 评论 -
Excel VBA倒计时
Private myTime As DatePrivate Const endTime As Date = #11/25/2021 2:00:00 PM#Sub StartTimer() Call TimeOnEnd SubSub TimeOn() Dim deltaTime As Variant Dim days As Long, hours As Long, minutes As Long, seconds As Long myTime = Now()原创 2021-11-23 20:49:55 · 1884 阅读 · 1 评论 -
Excel循环换行
'在每一行的前五列单元格中填写数值,当列号为5时,下一个单元格跳到下一行A列,Private Sub Worksheet_SelectionChange(ByVal Target As Range) Cells(Target.Row, Target.Column) = InputBox("请输入:") If Target.Column <> 5 Then If Cells(Target.Row, Target.Column).Value Like "[qQ]"原创 2021-07-16 11:16:52 · 193 阅读 · 0 评论 -
VBA平均格式规则AddAboveAverage
Sub AboveAverage() Range("a1").Value = "Name" Range("b1").Value = "Value" Range("a2").Value = "Channel-1" Range("a2").AutoFill Destination:=Range("a2:a10"), Type:=xlFillDefault '自动填充 Range("b2:b10").FormulaArray = "=int(rand()*100)"原创 2020-11-24 08:54:54 · 437 阅读 · 0 评论 -
Excel VBA 重复数据删除(解决删选功能将第一行数据作为标题的问题)
Excel VBA 重复数据删除Sub DeleteDuplicates() Dim data, data_dict As Variant Dim LastRow, i, column_i As Integer LastRow = Range("A" & Rows.Count).End(xlUp).Row '找出数据列中最后数据所在行 data = Range("A1:A" & LastRow).Value '将数据赋值给变量 '利原创 2020-11-15 22:05:44 · 1022 阅读 · 0 评论