Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Excel 2010 Issue with Creating Dictionary using the New Keyword 2

Status
Not open for further replies.
JasonEnsor

JasonEnsor

Programmer
Sep 14, 2010
193
GB
Hi Guys,

I have been looking back on one of my projects from a few weeks ago and decided to try to update it, using the Dictionary which I was introduced to recently by StrongM. My aim is to reduce the amount of code repeated in my application.

My project consists of a userform, which holds a multipage tab. The multipage has a tab for each day of the week, on each tab a listbox has been placed. My application searches through an excel spreadsheet looking at a date column, for every date, it extracts the day value and places that on the multipage tab corresponding to the day. E.g today is Tue 29 Oct, so the data will appear on the Tuesday tab. Each listbox is named lst & Day name, so Tuesday is lstTuesday.

So my issue arises when I try to reduce the code by using a case statement for each day of the week then by calling a procedure and passing in the listbox and creating the dictionary.

My userform uses code like this
Code: 
Select Case (dayValue)
 Case "Monday"
      Call PopulateDiaryList(frmDiary.lstMonday)
 Case "Tuesday"
      Call PopulateDiaryList(frmDiary.lstTuesday)
  etc...

Then my PopulateDiaryList uses
Code: 
Sub PopulateDiaryList(lst As MSForms.ListBox)

    Dim ListInformation As New Dictionary

    With lst
        .ColumnCount = colCount
        .ColumnWidths = colWidths
        .AddItem
        .List(ListInformation("Monday"), 0) = currentDate ' Date
        .List(ListInformation("Monday"), 1) = ModuleCode ' Module Code
        .List(ListInformation("Monday"), 2) = ModuleTitle ' Module Title
        .List(ListInformation("Monday"), 3) = StartTime ' Start time
        .List(ListInformation("Monday"), 4) = EndTime ' End time
        .List(ListInformation("Monday"), 5) = Site ' Site
        .List(ListInformation("Monday"), 6) = Room ' Room
        .List(ListInformation("Monday"), 7) = SupportWorker ' Support Worker
        .List(ListInformation("Monday"), 8) = InterpElecNote ' Interpeter/Electronic
        .List(ListInformation("Monday"), 9) = ClaimAt & vbTab & TypeOfSupport
    End With
  
    ListInformation("Monday") = ListInformation("Monday") + 1

End Sub

I know it won't work in it's current state as I have hardcoded "Monday" in, however the whole thing dies before I get to that point. it tells me "Invalid Use of New Keyword". What am I doing wrong? as I have copied this from my other project which used the new keyword on the dictionary and that worked.

I could go back to my project being full of redundant code, but this really reduces my code down to a few pages which I would rather for maintainability purposes as it prevents having to alter vast amounts of code for each day of the week.

Any Thoughts?

Many Thanks

J.


Regards

J.
 
Sort by date Sort by votes
Why are you dimming ListInformation in that Sub? You've clearly (?) already populated a variable of that name elsewhere
 
Upvote 0 Downvote
Seems like you missed to add the reference to to Microsoft Scripting Runtime
Another way is to replace this:
Dim ListInformation As New Dictionary
With this:
Dim ListInformation As Object: Set ListInformation = CreateObject("Scripting.Dictionary")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
Upvote 0 Downvote
Hi Guys,

Thanks for the replies, PHV, I had got the reference in however thank you for the tip for late binding as that will come in handy when I look at deploying this out to others. StrongM I was Diming the variable there as it wasn't working anywhere else, however it seems to let me get passed the new issue now.

So my next task is how to populate the list using the dictionary as
Code: 
ListInformation("Monday"), 0) = currentDate ' Date

clearly doesn't work

Thanks Again guys

Regards

J.
 
Upvote 0 Downvote
Hey Guys,

Okay so it is still not working.

I have a globals Module where I am creating my dictionary

Code: 
Dim ListInformation As New Dictionary
Then on my Form I have


Code: 
Select Case (dayValue)
                                Case "Monday"
                                    Call PopulateDiaryList(dayValue, frmDiary.lstMonday)
                                Case "Tuesday"
                                    Call PopulateDiaryList(dayValue, frmDiary.lstTuesday)
                                Case "Wednesday"
                                    Call PopulateDiaryList(dayValue, frmDiary.lstWednesday)
                                Case "Thursday"
                                    Call PopulateDiaryList(dayValue, frmDiary.lstThursday)
                                Case "Friday"
                                    Call PopulateDiaryList(dayValue, frmDiary.lstFriday)
                                Case "Saturday"
                                    Call PopulateDiaryList(dayValue, frmDiary.lstSaturday)
                                Case "Sunday"
                                    Call PopulateDiaryList(dayValue, frmDiary.lstSunday)
                            End Select
Then the PopulateDiaryList



Code: 
Sub PopulateDiaryList(dayValue As String, lst As MSForms.ListBox)
   
        listVal = ListInformation(Chr(34) & dayValue & Chr(34))
    With lst
        .ColumnCount = colCount
        .ColumnWidths = colWidths
        .AddItem
        .List(listVal, 0) = currentDate ' Date
        .List(listVal, 1) = ModuleCode ' Module Code
        .List(listVal, 2) = ModuleTitle ' Module Title
        .List(listVal, 3) = StartTime ' Start time
        .List(listVal, 4) = EndTime ' End time
        .List(listVal, 5) = Site ' Site
        .List(listVal, 6) = Room ' Room
        .List(listVal, 7) = SupportWorker ' Support Worker
        .List(listVal, 8) = InterpElecNote ' Interpeter/Electronic
        .List(listVal, 9) = ClaimAt & vbTab & TypeOfSupport
    End With
    
    ListInformation(dayValue) = ListInformation(dayValue) + 1
  
  ' dayList(lstName) = dayList(lstName) + 1
End Sub
I tried doing it this way as I was wanting to create a Key in the dictionary name "Monday" with a value of 1, then that would increase giving me the same effect that I had previously. Where I had the following for each day of the week.



Code: 
With lstMonday
        .ColumnCount = colCount
        .ColumnWidths = colWidths
        .AddItem
        .List(MonCount, 0) = currentDate ' Date
        .List(MonCount, 1) = ModuleCode ' Module Code
        .List(MonCount, 2) = ModuleTitle ' Module Title
        .List(MonCount, 3) = StartTime ' Start time
        .List(MonCount, 4) = EndTime ' End time
        .List(MonCount, 5) = Site ' Site
        .List(MonCount, 6) = Room ' Room
        .List(MonCount, 7) = SupportWorker ' Support Worker
        .List(MonCount, 8) = InterpElecNote ' Interpeter/Electronic
        .List(MonCount, 9) = ClaimAt & vbTab & TypeOfSupport
    End With
MonCount = MonCount + 1
Am I attempting the impossible or am I just to blind to see an easy solution to this?

Regards

J.
 
Upvote 0 Downvote
>I was Diming the variable there as it wasn't working anywhere else

In which case it takes precedence, and replaces the populated ListInformation you have already declared with an nice new, empty one for the scope of this sub.
 
Upvote 0 Downvote
Just to be picky...

All this code:

Code: 
Select Case (dayValue)
    Case "Monday"
        Call PopulateDiaryList(dayValue, frmDiary.lstMonday)
    Case "Tuesday"
        Call PopulateDiaryList(dayValue, frmDiary.lstTuesday)
    Case "Wednesday"
        Call PopulateDiaryList(dayValue, frmDiary.lstWednesday)
    Case "Thursday"
        Call PopulateDiaryList(dayValue, frmDiary.lstThursday)
    Case "Friday"
        Call PopulateDiaryList(dayValue, frmDiary.lstFriday)
    Case "Saturday"
        Call PopulateDiaryList(dayValue, frmDiary.lstSaturday)
    Case "Sunday"
        Call PopulateDiaryList(dayValue, frmDiary.lstSunday)
End Select

Could be just this:

Code: 
 ...
Call PopulateDiaryList(dayValue, frmDiary.Controls("lst" & dayValue))
 ...

Or even this:
Code: 
Call PopulateDiaryList(dayValue)

if you would change this:

Code: 
Sub PopulateDiaryList(dayValue As String)
   
        listVal = ListInformation(Chr(34) & dayValue & Chr(34))
    With [blue]frmDiary.Controls("lst" & dayValue)[/blue]
        .ColumnCount = colCount
        ...

Right...? :)

Have fun.

---- Andy
 
Upvote 0 Downvote
Hi Guys,

I seem to have hit a brick wall with this one still. Andy cheers for pointing out where I could shorten aspects of my code, I have taken this on board and now have

Code: 
Select Case (dayValue)
                                Case "Monday"
                                    Call PopulateDiaryList(MonCount, dayValue)
                                    MonCount = MonCount + 1

                                Case "Tuesday"
                                    Call PopulateDiaryList(TueCount, dayValue)
                                    TueCount = TueCount + 1
                                    
                                Case "Wednesday"
                                    Call PopulateDiaryList(WedCount, dayValue)
                                    WedCount = WedCount + 1
                                
                                Case "Thursday"
                                    Call PopulateDiaryList(ThursCount, dayValue)
                                    ThursCount = ThursCount + 1
                                    
                                Case "Friday"
                                    Call PopulateDiaryList(FriCount, dayValue)
                                    FriCount = FriCount + 1
                                    
                                Case "Saturday"
                                    Call PopulateDiaryList(SatCount, dayValue)
                                    SatCount = SatCount + 1
                                    
                                Case "Sunday"
                                    Call PopulateDiaryList(SunCount, dayValue)
                                    SunCount = SunCount + 1
                                    
                            End Select

and then in my PopulateDiaryList sub I use

Code: 
Sub PopulateDiaryList(count As Integer, dayValue As String)
                                With frmDiary.Controls("lst" & dayValue)
                                        .ColumnCount = colCount
                                        .ColumnWidths = colWidths
                                        .AddItem
                                        .List(count, 0) = currentDate ' Date
                                        .List(count, 1) = ModuleCode ' Module Code
                                        .List(count, 2) = ModuleTitle ' Module Title
                                        .List(count, 3) = StartTime ' Start time
                                        .List(count, 4) = EndTime ' End time
                                        .List(count, 5) = Site ' Site
                                        .List(count, 6) = Room ' Room
                                        .List(count, 7) = SupportWorker ' Support Worker
                                        .List(count, 8) = InterpElecNote ' Interpeter/Electronic
                                        .List(count, 9) = ClaimAt & vbTab & TypeOfSupport
                                    End With
                            End Sub

This has reduced some of the repetitive code. I did try to remove the case statement however I was unable to find a good way of being able to populate the .List value for each of the individual listboxes and increase the count value by one each time an item was added.

I think I will give up on the Dictionary approach I tried on this as I just can't seem to get my head around the whole thing and fathom out why it wasn't working the way I wanted.

Thanks again guys for the help.

Regards

J.
 
Upvote 0 Downvote
After
Code: 
Dim ListInformation As New Dictionary
the ListInformation is visible only in the module with this declaration. You need [tt]Public[/tt] instead:
Code: 
[COLOR=red]Public[/color] ListInformation As New Dictionary

combo
 
Upvote 0 Downvote
Hi Guys,

So after looking at Combo's suggestion, I created a dummy workbook and added a userform with a listbox. I created a procedure to populate the ListBox

Code: 
Public ListInformation As New Dictionary

Sub Populate(dayValue As String)

    listVal = ListInformation(Chr(34) & dayValue & Chr(34))
    
    With UserForm2.Controls("lst" & dayValue)
      '  .ColumnCount = colCount
      '  .ColumnWidths = colWidths
        .AddItem
        .List(listVal, 0) = "Hello" & listVal ' Date
'        .List(listVal, 0) = currentDate ' Date
'        .List(listVal, 1) = ModuleCode ' Module Code
'        .List(listVal, 2) = ModuleTitle ' Module Title
'        .List(listVal, 3) = StartTime ' Start time
'        .List(listVal, 4) = EndTime ' End time
'        .List(listVal, 5) = Site ' Site
'        .List(listVal, 6) = Room ' Room
'        .List(listVal, 7) = SupportWorker ' Support Worker
'        .List(listVal, 8) = InterpElecNote ' Interpeter/Electronic
'        .List(listVal, 9) = ClaimAt & vbTab & TypeOfSupport
    End With

    ListInformation(Chr(34) & dayValue & Chr(34)) = ListInformation(Chr(34) & dayValue & Chr(34)) + 1
    
End Sub

Then on my userform I used the following code

Code: 
Private Sub UserForm_Initialize()

    For i = 1 To 5
    Call Populate("Monday")
    Next
    
    For i = 1 To 5
    Call Populate("Tuesday")
    Next
End Sub

And that worked....So I moved back to my original workbook and did the following

Code: 
Public ListInfo As New Dictionary

Public Const saveLocation = "U:\"
Public Const fileName = "SavedDocuments"
Public Const fileExtension = ".xlsm"

Sub PopulateDiaryList(dayValue As String)

    listVal = ListInfo(Chr(34) & dayValue & Chr(34))
    
    With frmDiary.Controls("lst" & dayValue)
        .ColumnCount = colCount
        .ColumnWidths = colWidths
        .AddItem
        .List(listVal, 0) = currentDate ' Date
        .List(listVal, 1) = ModuleCode ' Module Code
        .List(listVal, 2) = ModuleTitle ' Module Title
        .List(listVal, 3) = StartTime ' Start time
        .List(listVal, 4) = EndTime ' End time
        .List(listVal, 5) = Site ' Site
        .List(listVal, 6) = Room ' Room
        .List(listVal, 7) = SupportWorker ' Support Worker
        .List(listVal, 8) = InterpElecNote ' Interpeter/Electronic
        .List(listVal, 9) = ClaimAt & vbTab & TypeOfSupport
    End With
    
   ListInfo(Chr(34) & dayValue & Chr(34)) = ListInfo(Chr(34) & dayValue & Chr(34)) + 1

End Sub

Then in my code I call it by using
Code: 
Call PopulateDiaryList(dayValue)

This once again gives me the invalid use of the New Keyword error. Why does this mock me so much, I thought I had cracked it when I got the test working....obviously not


Regards

J.
 
Upvote 0 Downvote
You don't have any other object named ListInfo elsewhere ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
Upvote 0 Downvote
>I have taken this on board and now have

Um ... not sure that you have. You seem to have made your Select Case even more complex than it was before - rather than eliminating it completely, as per Andrzejek's suggestion.

And then modified PopulateDiaryList inappropriately ...

What you want - to mostly repeat Andrzejek - is to replace your entire

[tt]Select Case DayValue
...
End Select[/tt]

with

[tt]PopulateDiaryList DayValue[/tt]

(I might also comment that given that the tab you are populating defines the day you could even eliminate passing the DayValue parameter if you felt so inclined)

and then your PopulateDiaryList sub becomes:

Code: 
[blue]Sub PopulateDiaryList(strDay As String)

    With Me.Controls("lst" & strDay)
        .ColumnCount = colCount
        .ColumnWidths = colWidths
        .AddItem
        .List(Listinformation(strDay), 0) = currentDate ' Date
        .List(Listinformation(strDay), 1) = ModuleCode ' Module Code
        .List(Listinformation(strDay), 2) = ModuleTitle ' Module Title
        .List(Listinformation(strDay), 3) = StartTime ' Start time
        .List(Listinformation(strDay), 4) = EndTime ' End time
        .List(Listinformation(strDay), 5) = Site ' Site
        .List(Listinformation(strDay), 6) = Room ' Room
        .List(Listinformation(strDay), 7) = SupportWorker ' Support Worker
        .List(Listinformation(strDay), 8) = InterpElecNote ' Interpeter/Electronic
        .List(Listinformation(strDay), 9) = ClaimAt & vbTab & TypeOfSupport
    End With
  
    Listinformation(strDay) = Listinformation(strDay) + 1
End Sub[/blue]

(my code makes some assumptions - that it is in the UserForm module, and that Listinformation is in scope)

Having said that, there are several changes I'd probably make. Firstly I wouldn't declare ListInformation as New. I'd rather explicitly instantiate it when I need it rather than letting VB guess ... Secondly you seem to be using the Multipage tab to host tabs that all have the same controls on, whereas it is better used to host tabs that have different controls. I'd probably use a tabstrip for what you are doing.
 
Upvote 0 Downvote
Hi Guys,

PHV, I have not used ListInfo anywhere else.

StrongM, The Select Case became more complex as I was unable to get the Dictionary to work as I wanted. So by using part of Andy's suggestion I was able to reduce some of the code repetition. The thought behind the Multipage Tab is that all the data is populated on to the relevant day tab allowing the user to quickly switch between tabs and see a students timetable information. I assume that by using a tabstrip I would be having to update listbox data each time the user switches to view another day of the week.

Using your suggestion I do seem to have the listbox population working with the dictionary, however I am not 100% sure why it didn't work before.

To Call the procedure I am using:
Code: 
Call PopulateDiaryList(dayValue)

Code: 
Sub PopulateDiaryList(dayValue As String)

    With frmDiary.Controls("lst" & dayValue)
            .ColumnCount = colCount
            .ColumnWidths = colWidths
            .AddItem
            .List(ListInformation(dayValue), 0) = currentDate ' Date
            .List(ListInformation(dayValue), 1) = ModuleCode ' Module Code
            .List(ListInformation(dayValue), 2) = ModuleTitle ' Module Title
            .List(ListInformation(dayValue), 3) = StartTime ' Start time
            .List(ListInformation(dayValue), 4) = EndTime ' End time
            .List(ListInformation(dayValue), 5) = Site ' Site
            .List(ListInformation(dayValue), 6) = Room ' Room
            .List(ListInformation(dayValue), 7) = SupportWorker ' Support Worker
            .List(ListInformation(dayValue), 8) = InterpElecNote ' Interpeter/Electronic
            .List(ListInformation(dayValue), 9) = ClaimAt & vbTab & TypeOfSupport
        End With
        
        ListInformation(dayValue) = ListInformation(dayValue) + 1
End Sub

Each time I search for a new student I am also removing all items from the dictionary, as Excel crashed and was unable to proceed if I didn't

Many thanks for everyone's help

Regards

J.
 
Upvote 0 Downvote
>by using a tabstrip I would be having to update listbox data each time the user switches to view another day of the week

Well, yes - but I don't see that as an issue (unless your search through Excel is slow, in which case you might want to review that code ...)

And, on reflection, I'm not quite sure why you are using a Dictionary here at all, when

Code: 
[blue]Sub PopulateDiaryList(dayValue As String)

    With frmDiary.Controls("lst" & dayValue)
        .ColumnCount = colCount
        .ColumnWidths = colWidths
        .AddItem
        .List(.ListCount - 1, 0) = currentDate ' Date
        .List(.ListCount - 1, 1) = ModuleCode ' Module Code
        .List(.ListCount - 1, 2) = ModuleTitle ' Module Title
        .List(.ListCount - 1, 3) = StartTime ' Start time
        .List(.ListCount - 1, 4) = EndTime ' End time
        .List(.ListCount - 1, 5) = Site ' Site
        .List(.ListCount - 1, 6) = Room ' Room
        .List(.ListCount - 1, 7) = SupportWorker ' Support Worker
        .List(.ListCount - 1, 8) = InterpElecNote ' Interpeter/Electronic
        .List(.ListCount - 1, 9) = ClaimAt & vbTab & TypeOfSuppor
    End With
        
End Sub[/blue]

achieves much the same thing
 
Upvote 0 Downvote
Hi StrongM,

I was using a dictionary as that was the only thing I could think of that I could increment easily. Which actually turned out to be more difficult than I realised for me. I never thought about using .ListCount, you are correct though it does do exactly the same. I will look in to the tabstrip approach and see how that works out, as you say it shouldn't make much of a difference unless excel is slow to search the data

Thanks Again

Regards

J.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

aldi07
  • Locked
  • Question
update a front-end databases with a new release using vba
Replies
2
Views
252
aldi07
aldi07
remeng
  • Locked
  • Solved
Run-time error '429': ActiveX component can't create object From Excel to Word
Replies
9
Views
1K
remeng
remeng
huiettcm
  • Locked
  • Question
Excel UserForm Window 'Focus' Event Listener 1
Replies
14
Views
579
huiettcm
huiettcm
remeng
  • Locked
  • Question
Using Err.Raise question
Replies
2
Views
739
mintjulep
M
remeng
  • Locked
  • Question
Run PowerShell Commands from Excel VBA
Replies
14
Views
3K
dhookom
dhookom

Part and Inventory Search

Sponsor

Back
Top