Vba

设置睡眠/等待时间

1. 开头加入此行代码

Private Declare Sub Sleep Lib Kernel32 (ByVal dwMilliseconds As Long)

1. 调用等待时间代码

Sleep 5000

获取当前单元格的地址

• .Row和.Column分别表示当前单元格的所在的第几行和第几列,为数字
• Application表示当前应用
• Selection表示所选区域
• ActiveCell表示激活的某一个单元格,和Selection有所区别

Function CELL_ADDRESS()
    'CELL_ADDRESS = Application.ThisCell.Address(True, False)
    CELL_ADDRESS = "[" & Application.ThisCell.Row & "," & Application.ThisCell.Column & "]"
End Function

获取已选区域的地址

• RowAbsolute和ColumnAbsolute表示是否使用绝对引用

Function SELECT_CELL_ADDRESS()
    SELECT_CELL_ADDRESS = Selection.Address(RowAbsolute:=False, ColumnAbsolute:=False)
End Function

正则表达式

https://blog.csdn.net/ycc9957/article/details/90054766

1. 创建对象的方式

Function REGEX_STR(ByVal text)
    Dim regex
    Set regex = CreateObject("vbscript.regexp")
    regex.Pattern = "(\d+)"
    regex.Global = True
    Set result = regex.Execute(text)
    REGEX_STR = result(0) & "|" & result(1)
End Function

1. 导入包的方式

Function REGEX_STR2(ByVal text)
    Dim myregexp As New RegExp
    Dim result As Object
    With myregexp
        .IgnoreCase = True
        .Global = True
        .Pattern = "(-?[\d\.]+)-(-?[\d\.]+)"
        Set result = .Execute(text)
    End With
    REGEX_STR2 = result(0).SubMatches(0) & "|" & result(0).SubMatches(1)
End Function

http请求

Sub getHtmlText()
    Dim oHtml As Object
    Set oHtml = VBA.CreateObject("WinHttp.WinHttpRequest.5.1")
    Dim resText As String
    Dim sUrl As String
    sUrl = "http://www.ooofeng.cn/ctp/ctp.html"
    With oHtml
        .Open "GET", sUrl, False
        .Send
        MsgBox .ResponseText
    End With
    Set oHtml = Nothing
End Sub

高亮显示所选单元格(聚光灯)

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Cells.Interior.ColorIndex = 0
    ActiveCell.EntireColumn.Interior.ColorIndex = 40
    ActiveCell.EntireRow.Interior.ColorIndex = 36
End Sub

WPS与VBA的区别

http://club.excelhome.net/thread-1551631-1-1.html