PDA

View Full Version : Help with VBA Code



jo15765
11-02-2011, 11:58 AM
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...

Kenneth Hobs
11-02-2011, 12:24 PM
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:
a() = dict.keys

Without the Private declaration:
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

'Set dic = CreateObject("Scripting.Dictionary") 'Late Binding method

For i = 1 To 500000
dic.Add "Key" & i, "Item" & i
Next i

Exit Sub
s = "Key500"
pos = WorksheetFunction.Match(s, WorksheetFunction.Transpose(dic.Keys), 0)
MsgBox s & " = Keys position " & pos
MsgBox "Key = " & dic.Keys(pos - 1) & vbCrLf & "Item = " & dic.Items(pos - 1)
End Sub

jo15765
11-02-2011, 12:31 PM
Kenneth I am not following the code above?

You said use a dictionary key instead of an array --- would that entail re-writing the code that I currently have in place?

Kenneth Hobs
11-02-2011, 12:40 PM
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.

jo15765
11-02-2011, 12:44 PM
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?

Kenneth Hobs
11-02-2011, 01:31 PM
Run Demo or Demo2 and then run Demo2. Note the difference.

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

'Set dic = CreateObject("Scripting.Dictionary") 'Late Binding method

For i = 1 To 12
dic.Add "Key" & i, "Item" & i
Next i

a() = dic.Keys
MsgBox Join(a(), vbLf)
End Sub

jo15765
11-02-2011, 01:38 PM
So in my Monday_report1 module

would I just write the code as:

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)

And continue on for each item that is my array just write that line of "dic.Add" ?

Kenneth Hobs
11-02-2011, 02:07 PM
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.

jo15765
11-02-2011, 02:49 PM
How would I verify that the dic.object is null?

Kenneth Hobs
11-02-2011, 04:12 PM
If dic Is Nothing Then 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.


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

MsgBox dic.Count, vbInformation, "Dictionary Key Count"

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