IT 免費/無料/Kostenlos(gratis) 記事本分享: EXCEL VBA常用參考資訊

EXCEL VBA運用框架關鍵資訊

應用程式(Application)，Microsoft Excel 是一個應用程式，它可以打開並編輯工作簿，每個工作簿可以包含多個工作表。

工作簿（Workbook）：Excel 文件的容器，可以包含多個工作表。一個工作簿可以包含多個工作表，並且可以進行保存、打開和編輯。

工作表（Worksheet）：Excel 中的一個分頁，用於方便歸納分類和存儲數據用途頁籤。每個工作簿可以包含多個工作表。工作表包含由列和欄組成的單元格(小方格)。

儲存格（Cell）：工作表中的一個方格，用於存儲數據。每個儲存格由列和行交叉形成，可以包含文本、數字、公式等。

列（Column）：工作表中的垂直方向的一系列儲存格。每一列由字母表示，例如 A、B、C 等。

行（Row）：工作表中的水平方向的一系列儲存格。每一行由數字表示，例如 1、2、3 等。

範圍（Range）：工作表中的一個連續儲存格的集合。範圍可以是單個儲存格，也可以是多個相鄰儲存格的組合，它亦是指定套用公式之範圍的設定小幫手。

參考範例 (將多個工作簿儲存後，並將它關閉工作簿儲存後，並將它關閉)
For Each wbs In Application.Workbooks
wbs.Save
Next wbs
Application.Quit

Windows("活頁簿1").Activate 'EXCEL預設開啟，即為工作簿(或活頁簿)
ActiveWindow.Close

參考範例
ActiveWorkbook.SaveAs "C:\Test.xls"

With Application.FileDialog(msoFileDialogFilePicker)
.InitialFileName = ThisWorkbook.Path & "\"
.Title = "開啟檔案對話窗格"

參考範例
Set ws1 = ThisWorkbook.Worksheets("工作表1")
lastRow1 = ws1.Cells(ws1.Rows.Count, 2).End(xlUp).Row ' 左邊為列的計數；右邊數字2,代表B欄位,取得最後列的資料筆數，回傳給lastRow1變數

參考範例
Workbooks("Test.xls").Worksheets("工作表1").Range("A1").Value = 88

Range("A1").Select
ActiveCell.Value = 88 '目前儲存格A1，被選取的值，指定為88

執行外部程式(如:可執行預先寫好的Batch批次檔(OOO.bat)或讓EXCEL透過SHELL,直接執行PYTHON程式,如下面範例)

Shell "python c:\DoSomething.py", vbNormalFocus 'EXCEL去執行外部PYTHON應用程式

Application.DisplayAlerts = False
'控制 Excel 應用程式是否顯示警告訊息。
ActiveWindow.SelectedSheets.Delete
'設定刪除選定頁籤時，不會有警告。

Application.ScreenUpdating = False
'控制 Excel 應用程式是否更新螢幕顯示。

Application.EnableEvents = True
'控制 Excel 應用程式，將會觸發各種事件，例如工作表的更改事件、工作簿的打開和關閉事件等。亦即，當您在 VBA 程式中設置了一個工作表的更改事件，該事件將會被觸發，並且相應的程式碼將被執行。

Application.CutCopyMode = False
'清除剪貼簿中的內容

Application.StatusBar = "正在更新資料..."
' 進行資料更新的程式碼,於下方狀態列,告知使用者EXCEL仍在更新資料

On Error Resume Next
'當程式碼執行遇到錯誤時，會忽略該錯誤並繼續執行下一行程式碼。

On Error GoTo Label01
'當程式碼執行遇到錯誤時，會將控制權轉移到指定的錯誤處理程式碼 (Label01:)。

公式(Formula)，自動貼在整A1到A100儲存格，貼上公式，省去拖拉之處理時間

Sub DuplicateFormulaFlag()
    Dim ws As Worksheet
    Dim rng As Range
    Dim cell As Range

    ' 設定要操作的工作表
    Set ws = ThisWorkbook.Worksheets("Sheet1") ' 修改為你的工作表名稱

    ' 設定要操作的範圍
    Set rng = ws.Range("A1:A100")

    ' 使用 For Each 迴圈逐一處理每個儲存格
    For Each cell In rng
        ' 將公式貼上儲存格
        cell.Formula = "=IF(COUNTIF(" & rng.Address & "," & cell.Address & ")>1,""重複"",""沒重複"")"
    Next cell
End Sub

檢查File是否存在函數
      Private Function FileExists(fname) As Boolean '判斷Excel檔是否存在
'   Returns TRUE if the file exists
        Dim x As String
        x = Dir(fname)
        If x <> "" Then FileExists = True _
        Else FileExists = False
End Function

常用函數

VLOOKUP函數，是一個簡單的垂直查找所要查「特定字串值」，並可回傳查找值在第一列中的匹配，對應相對位置之陣列值
(如:查找姓名，依其對應陣列中同列欄位位置資訊，如:身份證號；或可透過查此函數，與其它工作表之相對資訊，比對查看「特定字串值」，是否為在職人員等)。
INDEX與MATCH函數提供更靈活的查找和檢索功能，可以根據多個條件進行查找並返回更多的結果。

Excel表,第1列為標題列,請在工作表1 ,請協助用VBA程式,將所有工作表1 中之空白列全數刪除,並以B欄做為判斷資料是否為空值

Sub DeleteActiveEmptyRows()   '刪除作動中之工作表,所有空白列
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim sheetName As String
    sheetName = ActiveSheet.Name

    ' 取得作動中之工作表
    Set ws = ThisWorkbook.Worksheets(sheetName)

    ' 取得最後一列的行數 (2代表,以B欄位做為研判資料筆數基準)
    lastRow = ws.Cells(ws.Rows.Count, 2).End(xlUp).Row

    ' 從最後一列往上檢查每一列的B欄是否為空值，如果是則刪除該列
    For i = lastRow To 2 Step -1
        If ws.Cells(i, 2).Value = "" Then
            ws.Rows(i).Delete
        End If
    Next i
End Sub

進階資訊:

EXCEL 可自行撰寫類別元件，檔案類型為.cls,經由模組(Module)中之巨集程式,將其呼叫執行
例如下面為AI,撰寫出64位元上網VBA CLASS元件,所以EXCEL執行如RS-232 數據通訊CLASS元件應該也不是難事
Private Declare PtrSafe Function InternetOpen Lib "wininet.dll" Alias "InternetOpenA" (ByVal Agent As String, ByVal AccessType As LongPtr, ByVal ProxyName As String, ByVal ProxyBypass As String, ByVal Flags As LongPtr) As LongPtr

Private Declare PtrSafe Function InternetConnect Lib "wininet.dll" Alias "InternetConnectA" (ByVal hInternetSession As LongPtr, ByVal ServerName As String, ByVal ServerPort As Integer, ByVal UserName As String, ByVal Password As String, ByVal Service As LongPtr, ByVal Flags As LongPtr, ByVal Context As LongPtr) As LongPtr

Private Declare PtrSafe Function InternetCloseHandle Lib "wininet.dll" (ByVal hInet As LongPtr) As Boolean

Private Declare PtrSafe Function InternetReadFile Lib "wininet.dll" (ByVal hConnect As LongPtr, ByVal Buffer As String, ByVal NumberOfBytesToRead As LongPtr, NumberOfBytesRead As LongPtr) As Boolean

Private Declare PtrSafe Function HttpOpenRequest Lib "wininet.dll" Alias "HttpOpenRequestA" (ByVal hHttpSession As LongPtr, ByVal Verb As String, ByVal ObjectName As String, ByVal Version As String, ByVal Referer As String, ByVal AcceptTypes As LongPtr, ByVal Flags As LongPtr, Context As LongPtr) As LongPtr

串列通訊傳輸元件

Private Declare PtrSafe Function BuildCommDCB Lib "kernel32" Alias "BuildCommDCBA" _
(ByVal lpDef As String, lpDCB As DCB) As Long

PROMPT提示訊息下法:

請寫VBA程式,運用上面元件(BuildCommDCBA),執行RS-232傳輸測試

2023年12月16日 星期六

EXCEL VBA常用參考資訊