VBA 常用參數方法

  • 1431
  • 0
  • VBA
  • 2019-10-08

變數.常數常用基本方法

變數

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表單"

 

更改作業表名稱

Worksheets(1).Name = "新的工作表"

 

計算目前作業表總數

MsgBox Worksheets.Count

指定某個檔案、某個表單,向下至最底行,取得此行數

Windows("A1233777.xlsm").Activate
Sheets("XX123表").Select
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欄的總和
    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

 

儲存

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("aaa_Result.xlsx").SaveAs Path & "結果.xlsx"
    Workbooks("結果").Close ' 關閉xls
    Application.DisplayAlerts = True ' 開啟警告訊息

 

匯入合併資料

   '----------------------------------------------------
    '匯入合併資料
    '----------------------------------------------------
    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
資料來源都會特別註明,有興趣都可查詢原出處,本站皆經過整理才分享,如有轉載請顯示出處及作者,感謝。