Set Arr = CreateObject("Scripting.Dictionary")
BgnRow = 2
EndRow = .Cells(Rows.Count, "A").End(xlUp).Row
For i = BgnRow To EndRow
If .Cells(i, "D").Value = "V" Then
Arr(i) = .Cells(i, "A").Value
End If
Next i
要如何設置這樣的變數,在其他工作表可以直接引用他的值,比如:Arr.count,或他的Key或Items的值?
=工作表1!$A$1 + 工作表2!$B$1
VBA比照辦理
sum = Worksheets("工作表1").cells(1,1) + Worksheets("工作表2").cells(2,1)
打完收工
sorry delete
sorry sorry
請教若使用Public或Global宣告,以Arr為例,
Public Arr as Variant
Public k as Variant
public t as Variant
For i = BgnRow To EndRow
If .Cells(i, "D").Value = "V" Then
Arr(i) = .Cells(i, "A").Value
End If
Next i
End With
k=Arr.Keys
t=Arr.Items
可否直接宣告一個Public Arr as Variant,其他k 或t值,於sub程式再利用k=Arr.Keys 或t=Arr.Items取得?我的嘗試會錯誤,請教該如何設置?
其中一種做法
Dim temp_arr As Double
Sub test()
Set arr = CreateObject("Scripting.Dictionary")
BgnRow = 2
EndRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
For i = BgnRow To EndRow
If ActiveSheet.Cells(i, "D").Value = "V" Then
arr(i) = ActiveSheet.Cells(i, "A").Value
End If
Next i
temp_arr = arr.Count
End Sub
Sub other() '先行這個
test
MsgBox temp_arr
End Sub
另一種做法
Function temp_arr()
Set arr = CreateObject("Scripting.Dictionary")
BgnRow = 2
EndRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
For i = BgnRow To EndRow
If ActiveSheet.Cells(i, "D").Value = "V" Then
arr(i) = ActiveSheet.Cells(i, "A").Value
End If
Next i
temp_arr = arr.Count
End Function
Sub other()
MsgBox temp_arr
End Sub