Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Compile Error

  Asked By: Jason    Date: Oct 07    Category: MS Office    Views: 2433
  

I am an ExcelVBA novice, so apologies if this question
appears
too infantile.

I have VBA code that has worked before on another PC, but now I get
the following message (in a dialog box) when run on my PC at work.

Compile Error
Invalid use of new Keyword.

The code opens Excel and writes records (from MS Project). I have
verified that Excel object library is selected under References.
Excel Help (pasted below) does not make sense (to me).

Share: 

 

10 Answers Found

 
Answer #1    Answered By: Iqbal Bashara     Answered On: Oct 07

We need to see the code. Please post the code  containing "New" and at least
the lines around it.

 
Answer #2    Answered By: Bryson Smith     Answered On: Oct 07

Ok... without seeing the code, here's what I'd do:

Look for the word "New" in your code.

You probably have it in a statement that defines something
as a "new" object, but the object  was previously defined.

Some programmers like to define objects with the same name as "new"
as a way to re-initialize them, rather than writing code  to
initialize them manually.

Clearly, the compiler has a problem with the use of the "New" keyword.
what is unclear (without seeing the code) is whether the "New" keyword
is necessary in the first place.

Have a look and see if it compiles if you remove the "New" keywords.

 
Answer #3    Answered By: Minnie Romero     Answered On: Oct 07

See the code  below. Note, the code is run  from MS Project.

----------------------------------------------------------------
Sub HeatMapExtractProjectToExcel()

Dim xlApp As Excel.Application

Dim xlBook As Excel.Workbook

Dim xlSheet As Excel.Worksheet

Dim xlRow As Excel.Range

Dim xlCol As Excel.Range

Dim Proj As Project

Dim t As Task

Dim ColumnCount, Columns, Tcount As Integer

Tcount = 0
ColumnCount = 0

Set xlApp = New Excel.Application '<<<<<this is where the compile
error occurs

xlApp.Visible = True

AppActivate "Microsoft Excel"

xlApp.Cursor = xlWait

Set xlBook = xlApp.Workbooks.Add

Set xlSheet = xlBook.Worksheets.Add

xlSheet.Name = ActiveProject.Name

' set range to write to first cell

Set xlRow = xlApp.ActiveCell
xlRow = "Filename: " & ActiveProject.Name
xlRow = xlRow.Offset(1, 0)
xlRow = Date
Set xlRow = xlRow.Offset(2, 0)

Set xlCol = xlRow.Offset(0, 0)
xlCol = "ENV ID" ' Env ID

Set xlCol = xlRow.Offset(0, 1)
xlCol = "ENV TYPE" ' Env Type

Set xlCol = xlRow.Offset(0, 2)
xlCol = "WORK TYPE" ' Work Type

Set xlCol = xlRow.Offset(0, 3)
xlCol = "REL START WK" ' Rel Start Week

Set xlCol = xlRow.Offset(0, 4)
xlCol = "DURATIONWKS" ' Duration in weeks

Set xlRow = xlRow.Offset(2, 0)

'Write each task and indent to match outline level

For Each t In ActiveProject.Tasks
If Not t Is Nothing Then
If Not t.Summary Then
Set xlRow = xlRow.Offset(1, 0)

Set xlCol = xlRow.Offset(0, 0)
xlCol = t.Text23 ' Env ID

Set xlCol = xlRow.Offset(0, 1)
xlCol = t.Text24 ' Env Type

Set xlCol = xlRow.Offset(0, 2)
xlCol = t.Text27 ' Work Type

Set xlCol = xlRow.Offset(0, 3)
xlCol = t.Text25 ' Rel Start Week

Set xlCol = xlRow.Offset(0, 4)
xlCol = (t.Duration1) / (60 * 8 * 5) ' Convert Duration
from minutes to weeks

Tcount = Tcount + 1
End If

End If
Next t

'switch back to project  and display completion message

AppActivate "Microsoft Project"
MsgBox ("Macro Complete with " & Tcount & " Non-Summary Tasks
Written")
AppActivate "Microsoft Excel"
xlApp.Cursor = xlDefault

End Sub

 
Answer #4    Answered By: Madison Campbell     Answered On: Oct 07

The New works fine on my Project 2002 / Excel 2003 mix. However, an answer
on the Experts Exchange suggests you try replacing the New line with

Set xlApp = CreateObject("Excel.Application")

and that appeared to fix the person's problem.

The person later had further portability problems and the following code
fragment was offered as an alternative:

Sub CopyTableToExcel()
Dim tbl As Word.Table
Dim wdRow As Word.row
Dim wdCell As Word.Cell
Dim xlApp As Object ' Excel.Application
Dim xlWbk As Object ' Excel.Workbook
Dim xlWks As Object ' Excel.Worksheet
Dim r As Integer
Dim c As Integer
Const FirstRow = 1
Const FirstColumn = 1

Set xlApp = CreateObject("Excel.Application") ' New Excel.Application

This was for Excel driven from Word, but should apply to Project as well.
The changes from the original were the three "Object" lines and the
CreateObject statement. The thinking was that this worked better when there
were different versions of Excel involved.

 
Answer #5    Answered By: Duane Walker     Answered On: Oct 07

The suggested changes works great with excel  2003, but not 2002. I
can live with it for now. This is progress! Thank you.

While I have your attention....

Any suggestions why I get a compile  error (Variable not found) for

xlApp.Cursor = xlDefault

I have commented it out for now. Also, excel cursor has an hour
glass - any ideas how to eliminate it?

 
Answer #6    Answered By: Quinn Johnson     Answered On: Oct 07

The hour glass is from

xlApp.Cursor = xlWait

The way to eliminate it is with the

xlApp.Cursor = xlDefault

I can't think why you would be getting a compile  error on this - especially
as the xlWait one clearly works OK.

Is it xlApp or xlDefault it thinks isn't defined? (It will highlight the
offender.)

I'm wondering if your 2002 problem is to do with your references. Go to
Tools/References in the VB environment. The ticked ones will all be at the
top. In my case, I have five ticked:

Visual Basic For Applications
Microsoft Project 10.0 Object Library
OLE Automation
Microsoft Office 11.0 Object Library
Microsoft Excel 11.0 Object Library

I'm using Project 2002 which is 10.0, but the rest of my Office is 2003
(11.0).

If you're using 2002 throughout, then you'll presumably need to be seeing
the 10.0 libraries ticked.

If you see "missing" against any of them, you know you've got a problem.
Untick those and find and tick the appropriate ones further down your list.
(Much further :-) it's an alphabetical list apart from the ticked ones..)

 
Answer #7    Answered By: Kim Coleman     Answered On: Oct 07

I have the same libs ticked as below, except I have

Microsoft Excel 5.0 Object Library instead of 11.0

The compile  error is at xlDefault

I am running Office 2003 on my laptop right now.

 
Answer #8    Answered By: Burke Martin     Answered On: Oct 07

Try ticking the object  library matching your version of excel  (11 for 2003, 10
for XP, Not sure about the earlier ones but version 5 is very old, I think.)

 
Answer #9    Answered By: Rolando Reed     Answered On: Oct 07

Excel 5 is very old. I would hazard Office 97 from memory. Un-tick it and
tick the version 11 ones, seeing you're using Office 2003.

 
Answer #10    Answered By: Luete Fischer     Answered On: Oct 07

I don't see Version 11 in the list, let me find out why.

 
Didn't find what you were looking for? Find more on Compile Error Or get search suggestion and latest updates.




Tagged: