piao3701的博客

VBA将所有公式改为绝对引用

piao3701 — Wed, 19 Nov 2025 17:01:03 +0800

【代码】VBA将所有公式改为绝对引用。

Excel函数

piao3701 — Mon, 23 Dec 2024 09:20:34 +0800

【代码】Excel函数。

VBA设置自动行高和自动换行，解决文字贴近上下框问题

piao3701 — Thu, 12 Dec 2024 09:57:47 +0800

【代码】【无标题】

Python日志模块logging

piao3701 — Mon, 27 Jun 2022 16:22:56 +0800

Python logging

VBA成绩录入

piao3701 — Sat, 08 Jan 2022 18:47:23 +0800

Option Explicit Dim endColumnNum As Integer, firstColumnNum As Integer Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim temp As Variant temp = Application.InputBox("请输入：", Type:=1) Debug.Print temp If CStr(temp) = CStr(False

VBA弹窗打开文件，获取文件名以及文件全路径

piao3701 — Fri, 07 Jan 2022 21:35:29 +0800

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",

VBA打印机设置（列出所有打印机名字、选用合适的打印机并设计端口号）

piao3701 — Fri, 07 Jan 2022 14:20:43 +0800

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

FindFormatAndReplaceFormat

piao3701 — Fri, 07 Jan 2022 13:35:17 +0800

Sub FindFormatAndReplaceFormatDemo() Application.FindFormat.Interior.ColorIndex = 6 Application.ReplaceFormat.Interior.ColorIndex = 14 Cells.Replace what:="", Replacement:="", SearchFormat:=True, ReplaceFormat:=True '最后一句是关键，如果不写则起不到作用 End

VBA炸弹游戏

piao3701 — Wed, 05 Jan 2022 17:51:45 +0800

Option Explicit Sub 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

Excel VBA倒计时

piao3701 — Tue, 23 Nov 2021 20:49:55 +0800

Private myTime As Date Private Const endTime As Date = #11/25/2021 2:00:00 PM# Sub StartTimer() Call TimeOn End Sub Sub TimeOn() Dim deltaTime As Variant Dim days As Long, hours As Long, minutes As Long, seconds As Long myTime = Now()

Excel循环换行

piao3701 — Fri, 16 Jul 2021 11:16:52 +0800

'在每一行的前五列单元格中填写数值，当列号为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]"

Excel公式技巧

piao3701 — Mon, 11 Jan 2021 15:06:13 +0800

计算某一列有数据的最后一行的行号 MAX(NOT(ISBLAND(A:A))*ROW(A:A)) '该公式为数组公式，次案例计算A列最后一个数据所在行号

VBA平均格式规则AddAboveAverage

piao3701 — Tue, 24 Nov 2020 08:54:54 +0800

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)"

Excel VBA 重复数据删除（解决删选功能将第一行数据作为标题的问题）

piao3701 — Sun, 15 Nov 2020 22:05:44 +0800

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 '将数据赋值给变量 '利