Excel 使用(公式法與 VBA)

Function myFun(x As Integer, y As Integer) As Integer
    myFun = x + y 'VBA 中的 Funciton 可以直接在 Excel 儲存格中以公式的方式調用
End Function

Sub TestFunc()

    Range("A1").Value = "" '清除儲存格中的資料
    Range("A1:A4").Value = 5 '更改多個連續的儲存格內容
    Range("A1:A2,B3:C4").Value = 10 '如果範圍不是連續的，也可以一次使用多個範圍來指定
    Cells(1, 1).Value = 23 'Cells用法
    Cells(1, "A").Value = 45
    
    Range(Cells(1, 1), Cells(4, 2)).Select '呼叫 Select 可以選取這些儲存格
    Rows(3).Select '選擇整個列
    Columns(2).Select '選擇整個行
    
    '當這些儲存格被選取之後，在 VBA 中就可以透過 Selection 物件來取得這些被選取的儲存格
    Selection.Copy '將選取的儲存格複製起來
    Range("C3").Select '選擇要貼上的位置
    ActiveSheet.Paste '呼叫 ActiveSheet 的 Paste 將資料貼上去

End Sub

Sub 計算剩餘庫存()

    Dim i, j, Num, k As Integer
    Dim Index(100), sellIndex(100), stock(100) As Integer
    Dim Str As Variant
    Dim FindStr As String
    Dim FindCell As Range
    
    FindStr = Range("A2").Value
    Set FindCell = Cells.Find(What:=FindStr, AFTER:=Range("C9"), LookIn:=xlFormulas, _
    LookAt:=1, SearchOrder:=2, SearchDirection:=xlNext, _
    MatchCase:=False, MatchByte:=False, SearchFormat:=True)
    If FindCell Is Nothing Then
        Cells(8, "K").Value = "更新失敗" '以 Cells 賦值
    End If
    
    'MsgBox FindCell.Row '使用 MsgBox 回傳搜尋之字串(A2儲存格)在指定範圍(C9之後)之<列(.Row)>
    
    '=====取得原始庫存儲存格資訊=====
    
    '建立原始庫存 array ( for 迴圈用法)
    For i = 0 To 99
        Index(i) = 0
        sellIndex(i) = 0
    Next i
    
    Data = Cells(4, "G") '以 Cells 取值

    ' 使用 Split 分割欄位
    fieldArray = Split(Data, ".") '以 Split 取值
    Asize = (UBound(fieldArray) - LBound(fieldArray)) '計算原始庫存陣列大小

    For j = 0 To (Asize - 1)
        Num = fieldArray(j)
        Index(Num) = Index(Num) + 1 '更新原始庫存 array
    Next j
    
    '以字串印出原始庫存 array ( if 用法)
    For k = 0 To 99
        If Index(k) > 0 Then
            Str = Str & "尺寸 (" & k & ") = " & Index(k) & Chr(10) '以 & 將字串連接起來;Chr(10)用來換行
        End If
    Next k
    'Range("A2").Value = "原始庫存" '以 range 賦值
    'Range("A3").Value = Str
    
    '=====取得欲更新的售出資訊儲存格=====
    sellData = Cells(8, "H")

    ' 使用 Split 分割欄位
    sellfieldArray = Split(sellData, ".")
    sellAsize = (UBound(sellfieldArray) - LBound(sellfieldArray)) '售出陣列大小
    
    For j = 0 To (sellAsize - 1)
        sellNum = sellfieldArray(j)
        sellIndex(sellNum) = sellIndex(sellNum) + 1 '更新售出 array
    Next j
    
    For k = 0 To 99
        If sellIndex(k) > 0 Then
            sellStr = sellStr & "尺寸 (" & k & ") = " & sellIndex(k) & Chr(10)
        End If
    Next k
    'Range("B2").Value = "售出"
    'Range("B3").Value = sellStr
    Cells(FindCell.Row, "H").Value = sellData
    
    '=====取得剩餘庫存資訊儲存格=====
    For i = 0 To 99
        stock(i) = Index(i) - sellIndex(i)
    Next i
    For k = 0 To 99
        If stock(k) > 0 Then
            stockStr = stockStr & "尺寸 (" & k & ") = " & stock(k) & Chr(10)
        End If
    Next k
    'Range("C2").Value = "剩餘庫存"
    Cells(FindCell.Row, "I").Value = stockStr
    
End Sub

Function 與 Sub
VBA 的陣列
「VBA」陣列(array)使用小整理

公式法練習(EXCEL檔)