變數.常數常用基本方法
變數
Dim APPLE As Integer
*除了宣告成基本型態外(String/Integer/Boolean/Float)
宣告為 Variant 的變數可以儲存任何類型的資料。
未經宣告的變數預設會是 Variant 萬用類型
Option Explicit ' 強迫變數宣告
可以在程式碼的開頭加上
避免沒宣告的情況下使用變數
Public MyVar As Integer
如將變數宣告成Public，則可以跨模組Module調用此變數
把變數放入儲存格中
Range("A1").Value = APPLE
視窗提示視窗
MsgBox "value is " & x
串接字串要用 " & "
常數
Const SampleInt As Integer = 10001
執行過程中都是固定值, 不會變動
儲存格: Cell
Cells(1, 1).Value = 23
Cells 將第一行第一列的儲存格
Cells配合Range
Range(Cells(1, 1), Cells(4, 2)).Value = 13
列(直)
Columns(2).Select
行(橫)
Rows(3).Select
複製貼上
Selection.Copy ActiveSheet.Paste
清除儲存格
Range("A1:A2").ClearContents
作業簿間切換
Worksheets("工作表1").Range("A1").Value = "工作表1的A1" Worksheets("工作表2").Range("A1").Value = "工作表2的A1"
新增一個新的工作表
Worksheets.Add 或Worksheets.Add(Before:=Worksheets(1)).Name = "aaaa表單"
判斷某頁簽是否存在
'先建立一個檢查頁簽的函式, 方便調用
Function checkSheetName(sheetname)
'檢查此頁簽是否存在
isfind = False
For Each st In Sheets
If st.Name = sheetname Then
isfind = True
Exit For
End If
Next
checkSheetName = isfind
End Function
'調用
sheetname = "第一季"
If checkSheetName(sheetname) = False Then
MsgBox "工作表:" & sheetname & "不存在"
'Exit Sub '跳出
End If更改作業表名稱
Worksheets(1).Name = "新的工作表"
計算目前作業表總數
MsgBox Worksheets.Count
指定某個檔案、某個表單，向下至最底行，取得此行數
Windows("A1233777.xlsm").Activate
Sheets("XX123表").Select
或
Workbooks("A1233777.xlsm").Sheets("XX123表").Activate
Range("A3").Select
RowNum = Selection.End(xlDown).Row多個活頁簿間切換
Workbooks("活頁簿1").Worksheets(1).Range("A1").Value = "Hello"
開啟活頁簿
Workbooks.Open "C:\VBA\demo.xlsx"
加總 Application.WorksheetFunction.SUM公式
Application：表示當前的EXCEL執行的程式。
WorksheetFunction：表示呼叫當前Excel程式裡的公式。後面出現的SUM就是EXCEL裡的預設公式。
Dim Row
Dim Sum
Range("A1").Select
Row = Selection.End(xlDown).Row
取得總行數
Sum = Application.WorksheetFunction.Sum( Range("D2:D" & Row ) )
或是
Worksheets("Sheet1").Activate
Dim cellfrom
Dim cellfinish
cellfrom= 1
cellfinish= 5
ActiveSheet.Cells(A , 14) = Application.Sum( Range("A" & cellfrom& ":C" & cellfinish) )*補充*
Cells(列,欄)
例如：Cells(1,2) 對應"B1"
亦可寫成Cells(1,”B”) 或 Cells(“1”,”B”)，但請注意! 沒有Cells("B1")這種寫法!
*Rows(列)
例如：Rows(“1:3”) 代表第一到第三列
*Columns(欄)，"欄"或稱為"行"
例如：Columns(4) 代表第四欄，等同於Columns(“D”)
但如要選取多個欄
在雙引號裡面要用英文字!
要寫成：Columns(“A:D”) 代表A欄到D欄
*Range( ) 萬能~~
單格：
Range(“B1”)
多格：
Range(“A1,B2,C3,D4”)
單欄：
Range(“A:A”)
多欄：
Range("B:B,E:E")
多列：把英文字改成數字
Range("2:5,6:7")
選一個範圍：
Range("A1","B2") 表示 A1~B2 一整區
等同於 Range( "A1:B2" )
等同於 Range( Cells(“A1”) , Cells(“B2”) )
Cell帶入變數
Cells、Rows跟Columns的優點是可以代入變數
例如：迴圈1~5
For i=1 to 5
cells(i,2)=i*2
Next結果則是會回傳 Cell(1,2)、Cell(2,2)、Cell(3,2)、Cell(4,2)、Cell(5,2)
帶入公式計算Q欄的總和
'計算Q欄的總和
DataRow = 120 #資料總行數
Dim SUM_Q
Range("Q" & DataRow + 1).Select
ActiveCell.FormulaR1C1 = "=SUBTOTAL(9,R[-1]C:R[-" & DataRow - 1 & "]C)"
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With
SUM_Q = Range("Q" & DataRow + 1).Value篩選+刪除
DataRow = 120 #資料總行數
'篩選刪除
ActiveSheet.Range("$A$1:$O$" & DataRow).AutoFilter Field:=5, Criteria1:="=BANANA", Operator:=xlOr, Criteria2:="=APPLE"
With Range("2:" & DataRow) #從第2行開始~第幾行
.SpecialCells(xlCellTypeVisible).Delete #針對被篩出來的進行刪除
.EntireRow.Hidden = False
End With篩選後確認篩選出的數量
'篩選出 U欄 Quantity >= 0 排除負值 - 20210114 add
ActiveSheet.Range("$A$1:$AP$" & DataRow_Detail).AutoFilter Field:=21, Criteria1:="<0", Operator:=xlAnd
'檢查是否篩選出值
Dim myRange As Range
On Error Resume Next
Set myRange = Range("A2:A" & DataRow_Detail).SpecialCells(xlVisible)
On Error GoTo 0
If myRange Is Nothing Then
MsgBox "no cells"
Else
With Range("2:" & DataRow_Detail)
.SpecialCells(xlCellTypeVisible).Delete
.EntireRow.Hidden = False
End With
End If儲存
Workbooks("demo").Save
Excel移動位置並選取
移動到B4，往上，選取到底
Range("B4").End(xlUp).Select
移動到B4，往右，選取到底
Range("B4").End(xlToRight).Select
指定Sheet1工作頁簽，選取 B4 至 B4往右選取到底
Worksheets("Sheet1").Activate
Range("B4", Range("B4").End(xlToRight)).Select另存新檔
'巨集 另存新檔
'
'取得路徑
Dim Path As String
Windows("aaa.xlsm").Activate
Sheets("mapping table").Select
Path = Range("D1").Value
Application.DisplayAlerts = False ' 關閉警告訊息
'另存結果表
Workbooks("A.xlsx").SaveAs Path & "B.xlsx"
'這個前提是,巨集檔必須還開著
Workbooks("B").Close ' 關閉xls
Application.DisplayAlerts = True ' 開啟警告訊息
'第二種寫法
Path_Result = Path & "B.xlsx"
Application.DisplayAlerts = False ' 關閉警告訊息
'另存結果表
ActiveWorkbook.SaveAs Filename:=Path_Result, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
Application.DisplayAlerts = True ' 開啟警告訊息
'檢查工作表是否存在,不存在則建立工作表
Function checkSheetName(sheetname)
'檢查活頁是否存在
isfind = False
For Each st In Sheets
If st.Name = sheetname Then
isfind = True
Exit For
End If
Next
checkSheetName = isfind
End Function
If checkSheetName(sheetname) = True Then
Application.DisplayAlerts = False ' 關閉警告訊息
Sheets(sheetname).Select
ActiveWindow.SelectedSheets.Delete
Worksheets.Add(After:=Worksheets(1)).Name = sheetname
Application.DisplayAlerts = True ' 開啟警告訊息
Else
Worksheets.Add(After:=Worksheets(1)).Name = sheetname
End If匯入合併資料
'----------------------------------------------------
'匯入合併資料
'----------------------------------------------------
Windows("AAAA_Format_Macro.xlsm").Activate
Sheets("Sheet1").Select
Range("A1").Select
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=AAAA_MergeExcel;Extended Properties=""""" _
, Destination:=Range("$A$1")).QueryTable
.CommandType = xlCmdSql
.CommandText = Array("SELECT * FROM [AAAA_MergeExcel]")
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = False
.ListObject.DisplayName = "AAAA_MergeExcel"
.Refresh BackgroundQuery:=False
End With匯入樞紐表
'樞紐分析表
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"AAAA_MergeExcel", Version:=6).CreatePivotTable TableDestination:= _
"AAAA_樞紐表!R1C1", TableName:="AAAA_0004", DefaultVersion:=6
Cells(1, 1).Select
ActiveSheet.Shapes.AddChart2(201, xlColumnClustered).Select
ActiveChart.SetSourceData Source:=Range("AAAA_樞紐表!$A$1:$C$18")
'x軸
With ActiveChart.PivotLayout.PivotTable.PivotFields("Txn Type Name")
.Orientation = xlRowField
.Position = 1
End With
'篩選
With ActiveChart.PivotLayout.PivotTable.PivotFields("Trx Cost")
.Orientation = xlPageField
.Position = 1
End With
'y軸
ActiveChart.PivotLayout.PivotTable.AddDataField ActiveChart.PivotLayout. _
PivotTable.PivotFields("Transaction Qty"), "加總 - Transaction Qty", xlSum
ActiveChart.PivotLayout.PivotTable.AddDataField ActiveChart.PivotLayout. _
PivotTable.PivotFields("Trx Value"), "加總 - Trx Value", xlSum
'刪除樞紐圖
ActiveChart.Parent.Delete
參考資料來源：https://blog.gtwang.org/programming/excel-vba-programming-workbook-worksheet-cell/5/
人生美好~別浪費腦容量記程式碼 :- )
作者：CYL
出處：http://dotblogs.com.tw/cylcode
資料來源都會特別註明，有興趣都可查詢原出處，本站皆經過整理才分享，如有轉載請顯示出處及作者，感謝。