INVALID USE OF NEW KEYWORD

[JBBennett]

I am getting an "Invalid use of New keyword." error in my code. I am working on a Word 2010 VBA document. The code is:

Dim Rate As clsHourlyRates
Set Rate = New clsHourlyRates

For intCounter = 1 to 2
Selection.GoTo What:=wdGoToBookmark, Name:="Hourly_Rate_" & intCounter
Selection.TypeText Text:=CallByName(Rate, strClientCode & "_Rate", VbGet)
Next

The class in question is not part of my project. It is part of a different project to which I have set a referrence. I am pretty new to class modules so as I was typing this it occured to me that maybe I cannot instantiate a class unless that class is part of my project. I will try that.

Also, I am new to this site and looked for a document on proper posting ettiquet but could not find one. If there is one, or if anyone has any suggestions, please let me know.

Thank you.

Jason Bennett

 

[vbajock]

Are you using an Option Explicit statement at the top of your code module?

 

[strongm]

I would imagine that clsHourlyRates instancing has been set as PublicNotCreatable - which basically means that you cannot directly instantiate it (i.e. you cannot use New).

 

[JBBennett]

No, I am not using Option Explicit. Yes, the class is instancing as PublicNotCreatable. How does that affect instantiating and what can I do differently?

Jason Bennett

 

[vbajock]

Option Explicit declaration should give more detailed error info.

 

[MajP]

Option explicit will provide nothing. As you would expect you cannot create an instance. You must provide a function that returns an instance in the external project. Then dim it as object and set to a return value of the function. See article

http://support.microsoft.com/kb/555159

 

[HughLerwill]

http://support.microsoft.com/kb/555159

looks interesting

 

[MajP]

Example

I have an external project in an Access db called "dbDummyClass". The class is called "DummyClass".

'Class DummyClass in database dbDummyClass
Option Compare Database
Option Explicit

Private mName As String
Private mWeight As Double
Private mShipDate As Date
Const minWeight = 0.25
'custom event
Event NameChange(NewName As String, OldName As String)
Private Sub Class_Initialize()
  'Initialize and terminate are the only events that a class has
   'Unfortunately you cannot pass in parameters.
   'But you could hard code some defaults
   mName = "Not Assigned"
   mWeight = minWeight
   mShipDate = Now
End Sub
Public Sub MyClassInitialize(theName As String, theWeight As Double, theShipDate As Date)
  'So fake it with your own initialize procedure
  mName = theName
  mWeight = theWeight
  mShipDate = theShipDate
End Sub
Public Property Get Name() As String
  Name = mName
End Property
Public Property Let Name(ByVal theName As String)
  'This would be a Set if assigning to an object
  'Here is also the custom event
  RaiseEvent NameChange(theName, mName)
  mName = theName
End Property
Public Sub PrintObjectInfo()
  Dim strInfo As String
  'I can use the property here
  strInfo = "Name: " & Me.Name
  'I did not make a property for weight, I use the public variable
  strInfo = strInfo & vbCrLf & "Weight: " & mWeight
  strInfo = strInfo & vbCrLf & "ShipDate: " & mShipDate
  Debug.Print strInfo
End Sub
Private Sub Class_Terminate()
 'Initialize and terminate are the only events that a class has
 'Do clean up here if necessary
End Sub

In this external project is also have a standard module, in which I have a function that allows me to instantiate a new DummyClass instance and return it.

Public Function NewDummy(Optional theName As String = "No Name", Optional theWeight As Double = 12.5, Optional theShipDate As Variant = Null) As DummyClass
  If Not IsDate(theShipDate) Then theShipDate = Now
  Set NewDummy = New DummyClass
  NewDummy.MyClassInitialize theName, theWeight, CDate(theShipDate)
End Function

To use this class from my current project

Public Sub TestExternal()
  Dim dcDummy As Object
  Set dcDummy = newdummy("Test", 123, Now)
  dcDummy.printobjectinfo
End Sub

The results in the immediate window are

Name: Test
Weight: 123
ShipDate: 7/31/2012 3:30:44 PM

Unfortunately I believe this is how it has to be done if VBA. You lose all intellisense for the object.

 

[JBBennett]

Thank you all for your help. I really appreciated it. The solution at

http://support.microsoft.com/kb/555159

worked but I did not like the fact that I had no intellisense and came up with another solution, similar but that gives me intellisense as I work with my code.

PROJECT WITH CLASS:
I created the class module and populated it with the various client rates. This is an sample:

Public Property Get X_Rate() As String

X_Rate = "175.00"​

End Property

MY FUNCTION
Public Function Get_Hourly_Rate(ByVal strFileNumber As String) As String
' Get the hourly rate for the client.

Dim Rate As New clsHourlyRates
Dim strClientCode As String​

' Assign variables.

strClientCode = modFileInfo.Get_ClientCode(strFileNumber)​

' Get rate.

Get_Hourly_Rate = CallByName(Rate, strClientCode & "_Rate", VbGet)​

End Function

PROJECT CALLING CLASS
Private Sub Hourly_Rate(ByVal strFileNumber As String)

' Declaring variables.

Dim strRate
Dim intCounter As Integer​

' Set rate.
strRate = modFileInfo.Get_Hourly_Rate(strFileNumber)

' Adding hourly rate to form.

For intCounter = 1 To 2

Selection.GoTo What:=wdGoToBookmark, Name:="Hourly_Rate_" & intCounter
Selection.TypeText Text:=strRate​

Next​

End Sub

Jason Bennett

 

[MajP]

That is conceptually very different. This may be perfectly fine and meet your needs. But, they are not really similar.
1) What I demonstrated was an external library (project) exposing an instance of a class to another project. You than could create and work with the instances
2) What you demonstrate is exposing only the properties/and methods of a class to an external project.

In the latter you no longer can create and manage multiple instances of clsHourlyRates. This removes a big advantage of OOP.

 

[JBBennett]

I think I understand but want to be sure. I hope to work much more with classes as I go. My technique exposes the properties and methods, allowing me to return the value of those items but not have multiple instances of the class in my external project. I would in effect have to create a new function in the original project for each thing I wanted the external project to do. In the other technique I would simply instantiate new instances in the external project and work within that project as needed.

Do I understand correctly?

Jason Bennett

 

[MajP]

Exactly. This is a limitation in vba so neither approach is really ideal. It really depends on what you will be doing. VBA allows you to create an external project and easily work with procedures and functions, but it does not make it very easy to use classes from an external project. If you are going to be doing a lot of work with the classes then you are better off just importing them into the current project.

 

[JBBennett]

OK, thank you.

Jason Bennett