VBA: Create an array of class module


I'm trying to create an array of my custom class, but it gives me this error:


Run-time error '91':

Object variable or With block variable not set


Here is my code so far:

Sub DBM_Format() Dim coreWS As Worksheet Dim WS As Worksheet Dim LastRow As Long Dim RowRange As Long Dim dataList() As clsDBM Dim tmpdate As Date Set coreWS = Sheets(ActiveSheet.Name) 'Set WS = Sheets.Add LastRow = coreWS.Columns("A").Cells.Find("*", SearchOrder:=xlByRows, LookIn:=xlValues, SearchDirection:=xlPrevious).row RowRange = LastRow - 1 Dim row As Integer ReDim Preserve dataList(RowRange) Dim i As Integer Dim tmpData As clsDBM For i = 0 To (RowRange - 1) row = i + 2 tmpData.setDate = Format(Cells(row, 2), "MM/dd/yyyy hh:mm:ss") tmpData.setBloodGlucose = Cells(row, 3) tmpData.setCH = Cells(row, 4) tmpData.setInzulinF = Cells(row, 5) tmpData.setInzulinL = Cells(row, 6) tmpData.setCategory = Cells(row, 8) tmpData.setDayOfWeek = Weekday(dataList(i).pDate, vbMonday) 'dataList(i).setDate = Format(Cells(row, 2), "MM/dd/yyyy hh:mm:ss") 'dataList(i).setBloodGlucose = Cells(row, 3) 'dataList(i).setCH = Cells(row, 4) 'dataList(i).setInzulinF = Cells(row, 5) 'dataList(i).setInzulinL = Cells(row, 6) 'dataList(i).setCategory = Cells(row, 8) 'dataList(i).setDayOfWeek = Weekday(dataList(i).pDate, vbMonday) Set dataList(i) = tmpData Next i End Sub

And the class module:

Option Explicit Public pDayOfWeek As Integer Public pDate As Date Public pBloodGlucose As Double Public pCH As Double Public pInzulinF As Double Public pInzulinL As Double Public pCategory As String Public Property Let setDayOfWeek(Value As Integer) pDayOfWeek = Value End Property Public Property Let setDate(Value As Date) pDate = Value End Property Public Property Let setBloodGlucose(Value As Double) pBloodGlucose = Value End Property Public Property Let setCH(Value As String) If IsNumeric(Value) Then setCH = CDbl(Value) Else setCH = 0 End If End Property Public Property Let setInzulinF(Value As String) If IsNumeric(Value) Then pInzulinF = CDbl(Value) Else pInzulinF = 0 End If End Property Public Property Let setInzulinL(Value As String) If IsNumeric(Value) Then pInzulinL = CDbl(Value) Else pInzulinL = 0 End If End Property Public Property Let setCategory(Value As String) If Value = "Something" Then If Hour(pDate) < 9 Then pCategory = "Something" ElseIf Hour(pDate) < 11 Then pCategory = "Something" ElseIf Hour(pDate) < 14 Then pCategory = "Something" ElseIf Hour(pDate) < 16 Then pCategory = "Something" ElseIf Hour(pDate) < 19 Then pCategory = "Something" ElseIf Hour(pDate) < 21 Then pCategory = "Something" End If Else pCategory = Value End If pCategory = Value End Property

So my class name is "clsDBM" and I'm trying to fill this array with corresponding data from a whorksheet. The table is well-formatted, there is no empty lines, so that is not the problem, but I can't figure out what is...

Is there a way to fix it and make this happen (or should I use a completely different approach :D)

Thanks in advance!


use the <strong><em>new</em></strong> operator

Dim tmpData As New clsDBM

Because this statement that you're using: Dim tmpData As clsDBM simply defines a variable container or placeholder, of <em>type</em> clsDBM with a default value of Nothing (likewise: Dim i as Integer creates an <em>empty</em> integer with a default value of 0). To create an actual <em>instance</em> of that class object, you need to New it.


To expand on Zsmaster, here's a full example filling up an Array of 5 items with your custom class:

Private myCls(0 To 4) As myClass Private Sub Test() Dim i As Integer For i = 0 To 4 Set myCls(i) = New myClass Next i End Sub

In your case, you'd have to start the loop with:

For i = 0 To (RowRange - 1) row = i + 2 Set tmpData = New clsDBM tmpData.setDate = Format(Cells(row, 2), "MM/dd/yyyy hh:mm:ss") '... do stuff... Set dataList(i) = tmpData Next i

Or, alternatively forget about the tmpData object and do it like this:

For i = 0 To (RowRange - 1) Set dataList(i) = New clsDBM row = i + 2 dataList(i).setDate = Format(Cells(row, 2), "MM/dd/yyyy hh:mm:ss") dataList(i). '...Do more stuff... Next i


  • How to avoid casting arguments in Spock
  • Invocation of methods with default parameters in scala higher-order function
  • Is TryGetValue thread safe with itself
  • Visual Studio 2017 Nuget.config at solution folder not recognized
  • Does Azure Cosmos DB not support MongoDB/Mongoose unique index?
  • Increase GUI size in Netbeans 7
  • Different CUDA versions shown by nvcc and NVIDIA-smi
  • How to install to the Public directory in Windows 7 from the VS 2010 deployment Setup Project
  • Winform中实现ZedGraph中曲线右键显示为中文
  • 'matrix too large' exception using colt java lib
  • @RequestBody gives empty JsonObject when making a POST Request
  • Vulkan command execution order
  • Webpack: TypeError: Cannot read property 'properties' of undefined
  • QtDesigner how to change background
  • Django on Apache web server 'dict' object has no attribute 'render_context'
  • Xamarin Forms Switch Toggled event doesn't bind with viewmodel
  • /dev/kvm is not found on Mac OS
  • javascript - find subset that gives maximum sum under a certain limit (subset sum )
  • Remapping 'wq' to save and close buffer instead of save and quit
  • NSIS Special Build - Logging and Long String (8192 byte)
  • Core Data information in application
  • Starting a Thin cluster on Windows
  • Rails Nested attributes with check_box loop
  • Number of Hours Between Two Dates Not Working in Excel
  • spring / hibernate - filter by current user id
  • How can I turn a bunch of rows into aggregated columns WITHOUT using pivot in SQL Server 2005?
  • Adobe Phonegap Push Notifications with Firebase Cloud on Android
  • Outlook PropertyFrom MAPI Schema Property ID
  • Deadlock in range over channel in pipeline with multiple waitgroups
  • Sql Alchemy cannot run inside a transaction block
  • Is DateTimeToString in Delphi XE5 doesn't work?
  • Compress bitmap to a specific byte size in Android
  • Using libgdx with Android Studio
  • Old data appears in SQLite database when reinstalling app
  • Spring Cloud Config - Multiple Composite Repositories?
  • C++ Is “const void” As Return Value More Const-Correct Than “void”? [duplicate]
  • ASP.NET MVC razor view, post to a different model from the original model binding?
  • How can i move Clearcase dyamic/snapshot views to another host (Linux)
  • Grails - How to implement a foreign key relationship not using an id column?
  • convert json to excel in java