I have the an 3 separate arrays, for 3 separate reports that are run daily. For example on Monday, I have report 1 (which has its own array) report 2 (which has its own array) and report 3 (which also has its own array) and the same for Tues, Wed... etc up to Friday. I want to build a form, that I can enter for an example Report_1 and enter a new workbook name "Michael.xls" and push a button and it will add "Michael.xls" to the array. Also, I want to be able to enter a workbook name (well use the same book name) "Michael.xls" and push a different button and it will remove "Michel.xls" from the array.
I attached a sample form, with a little code in there that will hopefully help...
I would use one concept to solve the problem and not use arrays per se.
1. Private Dictionary Object.
Dictionary objects are easy to add and remove unique keys. I will work up a more specific short example if needed. e.g. If the dictionary object name was Dict, I would put the elements into an array:
[vba]a() = dict.keys[/vba]
Without the Private declaration:
[vba]Sub Demo()
'Early Binding method requires Reference: MicroSoft Scripting Runtime, scrrun.dll
Dim a As Variant
Dim i As Long
Dim pos As Long
Dim s As String
'Dim dic As Object 'Late Binding method
Dim dic As Dictionary 'Early Binding method
Set dic = New Dictionary 'Early Binding Method
Yes. You are not using a Private or Public declared variable to store your array now. If you want to do that kind of thing, this approach would be best. Here's the why with regard to the other problem using an array. e.g. Even if the array was Private or Public, you remove the 2nd element. How is this done? Once done, you then have to reorder the array to fix the hole that you just put in it or change your code to account for the hole. Life is much easier with dictionary objects. Once you learn, you will like it.
My demo code will introduce you to the world of the dictionary object. Of course you should first click the menu Tools > References... and select the object as I noted in a comment. This lets you use early binding when you use it in the Dim line. Early binding lets intellisense work for you.
I added the Microsoft Scripting Runtime as a Reference, can you elaborate a little more on how to set define what is in my array's to set as the dict.keys?
Run Demo or Demo2 and then run Demo2. Note the difference.
[VBA]Option Explicit
Dim dic As Dictionary
Sub Demo()
'Early Binding method requires Reference: MicroSoft Scripting Runtime, scrrun.dll
Dim a() As Variant
Dim i As Long
Dim pos As Long
Dim s As String
'Dim dic As Object 'Late Binding method
'Dim dic As Dictionary 'Early Binding method
If dic Is Nothing Then Set dic = New Dictionary 'Early Binding Method
'Set dic = CreateObject("Scripting.Dictionary") 'Late Binding method
dic.CompareMode = BinaryCompare
For i = 1 To 12
dic.Add "Key" & i, "Item" & i
Next i
a() = dic.Keys
MsgBox Join(a(), vbLf)
End Sub
Sub Demo2()
'Early Binding method requires Reference: MicroSoft Scripting Runtime, scrrun.dll
Dim a() As Variant
Dim i As Long
Dim pos As Long
Dim s As String
'Dim dic As Object 'Late Binding method
'Dim dic As Dictionary 'Early Binding method
If dic Is Nothing Then
Set dic = New Dictionary 'Early Binding Method
dic.CompareMode = BinaryCompare
Else
If Not dic.Exists("Key" & 1) Then dic.Add "Key" & 1, Nothing
dic.Add "Key" & 13, Nothing
a() = dic.Keys
MsgBox Join(a(), vbLf)
Set dic = Nothing
Exit Sub
End If
would I just write the code as:
[vba]
Dim a() As Variant
Dim i As Long
Dim pos As Long
Dim s As String
'Dim dic As Object 'Late Binding method
'Dim dic As Dictionary 'Early Binding method
If dic Is Nothing Then Set dic = New Dictionary 'Early Binding Method
'Set dic = CreateObject("Scripting.Dictionary") 'Late Binding method
dic.CompareMode = BinaryCompare
For i = 1 To 12
dic.Add "Test.xls" & i
dic.Add "Run23.xls" & i
Next i
a() = dic.Keys
MsgBox Join(a(), vbLf)
[/vba]
And continue on for each item that is my array just write that line of "dic.Add" ?
Last edited by Aussiebear; 11-03-2011 at 02:36 AM.
Reason: adjusted the code tags for the correct usage (...Again)
Sort of. If the dic object is nothing, then you can safely add the keys. If not, be sure to check first with the dictionary object property Exists before using the Add Method. With a few tries, you will have it down pat in a short time.
If it is not nothing, you can get the count of entries by dic.count. You should check for the Object before using a property or method.
[VBA]
Dim dic As Dictionary
Sub Demo()
'Early Binding method requires Reference: MicroSoft Scripting Runtime, scrrun.dll
Dim a() As Variant
Dim i As Long
Dim s As String
'Dim dic As Object 'Late Binding method
'Dim dic As Dictionary 'Early Binding method
If dic Is Nothing Then Set dic = New Dictionary 'Early Binding Method
'Set dic = CreateObject("Scripting.Dictionary") 'Late Binding method
Reply With Quote
