INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!

Talk With Other Members
Be Notified Of Responses
To Your Posts
Keyword Search
One-Click Access To Your
Favorite Forums
Automated Signatures
On Your Posts
Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

LINK TO THIS FORUM!

Tek-Tips LinkedIn^® Group

Join our
LinkedIn^® Group!

Hook up with past and present colleagues and classmates quickly.

Partner With Us!

"Best Of Breed" Forums Add Stickiness To Your Site

(Download This Button Today!)

Feedback

"...It's fun to see others going through the same stuff I did and be able to help. It's also a way for me to stay sharp and not lose the stuff I've learned..."

More...

Geography

Where in the world do Tek-Tips members come from?

Click Here To Find Out!

Tek-Tips Shirts!

Get your
Tek-Tips Forums
SWAG here!

Home > Forums > Programmers > Languages > VBA Visual Basic for Applications (Microsoft) Forum

INVALID USE OF NEW KEYWORD

Read More Threads Like This One

JBBennett (TechnicalUser)

31 Jul 12 13:02

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 (Programmer)

31 Jul 12 13:31

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

strongm (MIS)

31 Jul 12 14:22

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 (TechnicalUser)

31 Jul 12 14:25

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 (Programmer)

31 Jul 12 14:54

Option Explicit declaration should give more detailed error info.

MajP (TechnicalUser)

31 Jul 12 15:02

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 (Programmer)

31 Jul 12 15:09

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

MajP (TechnicalUser)

31 Jul 12 15:39

Example

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

CODE

'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.

CODE

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

CODE

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 (TechnicalUser)

31 Jul 12 17:30

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

End Sub

Jason Bennett

MajP (TechnicalUser)

31 Jul 12 18:31

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 (TechnicalUser)

1 Aug 12 10:03

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 (TechnicalUser)

1 Aug 12 11:48

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 (TechnicalUser)

1 Aug 12 11:54

OK, thank you.

Jason Bennett

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Back To Forum

Back To VBA Visual Basic for Applications (Microsoft)

Member Login

Come Join Us!

LINK TO THIS FORUM!

Tek-Tips LinkedIn® Group

Partner With Us!

Feedback

Geography

Tek-Tips Shirts!

INVALID USE OF NEW KEYWORD

CODE

CODE

CODE

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Back To Forum

Join Tek-Tips® Today!

Tek-Tips LinkedIn^® Group

Join Tek-Tips^® Today!