VBA Custom Collection Error - (Object Variable or With Block Variable Not set)

Question

Ive set up a custom collection on VBA, in order to store several objects from the worksheet & loop over the items to create a ppt presentation.

Unfortunately ive hit a snag in creating the custom collection: ive set it up as follows and get an Object variable or with block variable error

Sub Funds()
Dim Funds As Collection
Dim V As Fund
Set V = New Fund

V.FundID = "V1"
V.Title = "Profile_FactSheet_Title_En"
V.Fund_MER = "V1_Mer_En"
V.Fund_Yield = "V1_Yield_End"
V.Asset_Alloc = "V1_assetAlloc_En_SourceData"
V.Asset_Alloc2 = "AAV1EN"
V.Asset_Alloc3 = "FIV1EN"
V.Asset_Alloc4 = "FIMAV1EN"
V.Title_2 = "Profile_FactSheet_Title_En"
V.Trailing = "RetV1TrailingEN"
V.Calendar = "RetV1CalendarEN"

Funds.Add V, V.FundID

End Sub

As i look over this i think everything is set properly but i still get that error.

In addition i have a class module for the objects in the collection as such:

Option Explicit

Public FundID As String
Public Title As String
Public Fund_MER As String
Public Fund_Yield As String
Public Asset_Alloc As String
Public Asset_Alloc2 As String
Public Asset_Alloc3 As String
Public Asset_Alloc4 As String
Public Title_2 As String
Public Trailing As String
Public Calendar As String

Any help on this would be greatly appreciated!!

You need to New your Funds collection.

Brian M Stafford
– Brian M Stafford

2018-11-13 15:43:45 +00:00
Commented Nov 13, 2018 at 15:43 — Brian M Stafford
– Brian M Stafford, Commented Nov 13, 2018 at 15:43

Nick · Accepted Answer · 2018-11-13 16:06:39Z

4

Dont give your collection and sub the same name (Funds). And you either need to do:

Dim Funds As Collection
Set Funds = New Collection

OR

Dim Funds As New Collection

answered Nov 13, 2018 at 16:06

Nick

3,8586 gold badges36 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

ETB Over a year ago

Great that solves it!! When i call an item within the collection such as Set shP = V.Title --> i get an error stating "Object Required" .. If i store V.title as Object i still get the same error.

Nick Over a year ago

To get an item from the collection, you will need to use it's index. So for the first item, Funds.item(1)

Collectives™ on Stack Overflow

VBA Custom Collection Error - (Object Variable or With Block Variable Not set)

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related