How to define a user-defined type

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am starting a new thread which was based on a sub part of a previous topic
'Open an Excel File from Access' as the focus has changed.

Essentially want to run a macro on some data in an excel file. When the
macro is in excel it works. From Access it tells me that User-Defined type is
not defined.

Any help appreciated.

Bruce

Sub AddApostrophes()

Dim C As Excel.Range
Application.ScreenUpdating = False
myRange = "C2:C" & endrow
With ActiveWorkbook.ActiveSheet
For Each C In Range(myRange)
C.Formula = "'" & C.Formula
Next
End With
Application.ScreenUpdating = True
End Sub

Function endrow()
endrow = (Range("A1").End(xlDown).Row)
End Function
 
Most likely you need to add a reference to Excel. To do this, open the VB
Edit window and click Tools - References and find and check your Excel
reference. Also, you didn't Dim the myRange variable; it may be dimmed
somewhere else, but I'd think this would be a Procedure local variable
(unless it's used somewhere else).
 
Bruce said:
I am starting a new thread which was based on a sub part of a
previous topic 'Open an Excel File from Access' as the focus has
changed.

Essentially want to run a macro on some data in an excel file. When
the macro is in excel it works. From Access it tells me that
User-Defined type is not defined.

Any help appreciated.

Bruce

Sub AddApostrophes()

Dim C As Excel.Range
Application.ScreenUpdating = False
myRange = "C2:C" & endrow
With ActiveWorkbook.ActiveSheet
For Each C In Range(myRange)
C.Formula = "'" & C.Formula
Next
End With
Application.ScreenUpdating = True
End Sub

Function endrow()
endrow = (Range("A1").End(xlDown).Row)
End Function
Click to expand...

You don't say what line of code raises this error, but if you don't have
a reference to the Microsoft Excel Object Library, the type Excel.Range
isn't going to be recognized. Are you intending to use early binding or
late binding?

Even more important, if you are running this code in Access, you need to
qualify almost everything in those procedures with a reference to an
Excel Application object that you have previously created. Looking back
at your earlier thread, I see that you are creating such an object in a
different procedure. You need to either declare that object at the
module level, so that it is accessible by all procedures in the module,
or pass it as an argument to the various procedures.

Let's suppose that you have declared "obj" as an Object at module level,
and have previously set it to an instance of Microsoft Excel. Then your
code might be rewritten (using early binding) as:

'----- start of revised code -----
'----- WARNING: AIR CODE -----
Sub AddApostrophes()

Dim C As Excel.Range
Dim myRange As String

With obj
.Application.ScreenUpdating = False
myRange = "C2:C" & endrow
With .ActiveWorkbook.ActiveSheet
For Each C In .Range(myRange)
C.Formula = "'" & C.Formula
Next
End With
.Application.ScreenUpdating = True
End Sub

Function endrow()
endrow = (obj.Range("A1").End(xlDown).Row)
End Function

'----- end of revised code -----

I *think* something like that is what you need, though I'm not an expert
on Excel and haven't spent much time automating it. But the most
important thing is that you qualify all calls to Excel methods and
objects with the object reference to the instance of Excel that you are
automating.
 
Access will not understand any of these instructions. ALL occurances need to
be prefaced with "Excel." in some manner. Otherwise, Access thinks you are
trying to define a new type.

Application.ScreenUpdating
ActiveWorkbook.ActiveSheet
Range(myRange)
 
Back
Top