I am using Excel 2003 and I have been using the VBA code below on my computer
for a long time and find it extremely useful and which has never result in an
error.

A colleague asked if he could also have in on his computer and so I copied
it over. The strange thing is it doesn't seem to work on his computer and I
not sure why. I stops on the line 'Dim Result As New DataObject' that I have
higlighted with the *********. and a dialogue box appears saying: Compile
Error: User defined type not defined.

Does anyone know the answer why it works on my PC and not on his? I think it
might have something to do with declaring the DataObject

Sub AddSelection()

'This routine adds the values of selected cells and
'places the result in the variable 'SumOfRange'
'and also holds the value in the clipboard

Dim SumOfRange As Double
Dim Result As New DataObject '*******

SumOfRange = 0
SumOfRange = Application.WorksheetFunction.Sum(Selection) 'Add the Range

Result.SetText SumOfRange
Result.PutInClipboard

End Sub

Not sure what the variable is used for however, in your VBA editor, Select
Tools - References and make a list of the references that are checked. Then
have a look at the same on the other computer and make them match. (Note you
must actually check the boxes; not just select the lines.)
--
Regards,

OssieMac


"Simka" wrote:

I am using Excel 2003 and I have been using the VBA code below on my computer
for a long time and find it extremely useful and which has never result in an
error.

A colleague asked if he could also have in on his computer and so I copied
it over. The strange thing is it doesn't seem to work on his computer and I
not sure why. I stops on the line 'Dim Result As New DataObject' that I have
higlighted with the *********. and a dialogue box appears saying: Compile
Error: User defined type not defined.

Does anyone know the answer why it works on my PC and not on his? I think it
might have something to do with declaring the DataObject

Sub AddSelection()

'This routine adds the values of selected cells and
'places the result in the variable 'SumOfRange'
'and also holds the value in the clipboard

Dim SumOfRange As Double
Dim Result As New DataObject '*******

SumOfRange = 0
SumOfRange = Application.WorksheetFunction.Sum(Selection) 'Add the Range

Result.SetText SumOfRange
Result.PutInClipboard

End Sub

There are a few things to check


1) Do you have any code on Module sheets or forms?
2) Compare the "checked" Reference setting in the VBA menu

Tools - References

If any are missing the find them in the list and check them, then press
OK.

3) Check the Break on Error options

Tools - OPtions - General - Error Trapping


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=178426

Microsoft Office Help

The dataobject variable is associated with the Microsoft Forms 2.00 Object
Library

--
Ken
"Using Dbase dialects since 82"
"Started with Visicalc in the same year"


"OssieMac" wrote:

Not sure what the variable is used for however, in your VBA editor, Select
Tools - References and make a list of the references that are checked. Then
have a look at the same on the other computer and make them match. (Note you
must actually check the boxes; not just select the lines.)
--
Regards,

OssieMac


"Simka" wrote:

I am using Excel 2003 and I have been using the VBA code below on my computer
for a long time and find it extremely useful and which has never result in an
error.

A colleague asked if he could also have in on his computer and so I copied
it over. The strange thing is it doesn't seem to work on his computer and I
not sure why. I stops on the line 'Dim Result As New DataObject' that I have
higlighted with the *********. and a dialogue box appears saying: Compile
Error: User defined type not defined.

Does anyone know the answer why it works on my PC and not on his? I think it
might have something to do with declaring the DataObject

Sub AddSelection()

'This routine adds the values of selected cells and
'places the result in the variable 'SumOfRange'
'and also holds the value in the clipboard

Dim SumOfRange As Double
Dim Result As New DataObject '*******

SumOfRange = 0
SumOfRange = Application.WorksheetFunction.Sum(Selection) 'Add the Range

Result.SetText SumOfRange
Result.PutInClipboard

End Sub

You need a reference to the MSForms 2.0 library. There are (at least)
two ways to set this. The simplest is to just add a userform to your
project. That will set the reference. You can then delete the user
form, and the reference will remain. Or, in VBA, go to the Tools menu,
choose References, and scroll down to "Microsoft Forms 2.0 Object
Library". Check that item. In the interest of science, you can also
add the reference with code:

Sub AddFormsRef()
On Error Resume Next
ActiveWorkbook.VBProject.References.AddFromGuid _
GUID:="{0D452EE1-E08F-101A-852E-02608C4D0BB4}", _
major:=2, minor:=0
End Sub

Another reason that you might get the error you did even if you have
the Forms library referenced is that some other reference cannot be
found. In VBA go to the Tools menu, choose References, and see if any
are marked MISSING. The presence of a missing library can cause the
compiler to fail on objects in other referenced libraries. If you find
a missing reference, you can uncheck it if you do not need it, or you
can browse for the file to add it (if you know the file name) or you
can ask the vendor for a new copy. It the missing reference is a base
library need by VBA or Excel, close Excel, go to the Windows Start
menu, choose Run and enter

"C:\Program Files\Microsoft Office\Office11\Excel.exe" /regserver

You may have to change the path to point to your particular
installation of Excel. The /regserver switch causes Excel to reset
itself back to "factory defaults" and can cure any number of ills.


Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]




On Thu, 11 Feb 2010 02:11:01 -0800, Simka
wrote:

I am using Excel 2003 and I have been using the VBA code below on my computer
for a long time and find it extremely useful and which has never result in an
error.

A colleague asked if he could also have in on his computer and so I copied
it over. The strange thing is it doesn't seem to work on his computer and I
not sure why. I stops on the line 'Dim Result As New DataObject' that I have
higlighted with the *********. and a dialogue box appears saying: Compile
Error: User defined type not defined.

Does anyone know the answer why it works on my PC and not on his? I think it
might have something to do with declaring the DataObject

Sub AddSelection()

'This routine adds the values of selected cells and
'places the result in the variable 'SumOfRange'
'and also holds the value in the clipboard

Dim SumOfRange As Double
Dim Result As New DataObject '*******

SumOfRange = 0
SumOfRange = Application.WorksheetFunction.Sum(Selection) 'Add the Range

Result.SetText SumOfRange
Result.PutInClipboard

End Sub