Prev: Filedialog err 438 Object doesn't support this property or method
Next: DoCmd.RunCommand acCmdFind
From: Klatuu on 13 Nov 2006 09:15
Further research:

Here is a copy/paste from VBA Help:

Another easy way to loop through a range is to use a For Each...Next loop
with the collection of cells specified in the Range property. Visual Basic
automatically sets an object variable for the next cell each time the loop
runs. The following procedure loops through the range A1:D10, setting to 0
(zero) any number whose absolute value is less than 0.01.

Sub RoundToZero2()
For Each c In Worksheets("Sheet1").Range("A1:D10").Cells
If Abs(c.Value) < 0.01 Then c.Value = 0
Next
End Sub

And to tie it all up, I spoke before I looked. Option Explicit was not set.
Why, I don't know, because I always (well I thought I did) instist on two
Option statments
Option Explicit
Option Base 0
I know it is the default, but it is self documenting.

So, I added Option Explicit, and dimmed cell as an object. and it works.

I am very glad you and BruceS took the time to review this. At least we
know why it works now and I have corrected my serious sin.

Thanks.

"RoyVidar" wrote:

> "Klatuu" <Klatuu(a)discussions.microsoft.com> wrote in message
> <5A43E0A8-B6AC-46AF-A348-792CECDE805B(a)microsoft.com>:
> > No, it works as is. I have seen other posts stating the xl constants
> > are only available in late binding, but I have found that not to be
> > true. In fact, I can go into the immediate window without an
> > instance of Excel running and query an xl constant and it returns
> > the correct value. Maybe it could be because I have the Excel 11.0
> > object library in my references.
>
> Yes!
>
> The xlConstants are only available when referencing the automated
> application, which is what one would do when using early binding.
>
> Check out Tony Toews article on late binding, with further links
> http://www.granite.ab.ca/access/latebinding.htm
>
> > I use late binding because when this particular code was written, we
> > had some users on Office 2000 and some on 2003. Early binding in
> > that case causes one or the other not to work because the object
> > libraries are different.
>
> To me - the reason to go late bound, is to be able to remove the
> reference to the automated application. The reference, is the usual
> cause for the hassle when moving an app between versions ;-)
>
> > As to the cells, I don't know what you mean. Cells is a property of
> > both the Worksheet and Range objects.
>
> I see you've commented it elsethreads - you are using a For Each
> Cell... construct a couple of times - is it a possibility that
> you've missed Option Explicit in this module? There's a Word object
> called Cell, but that would/should probably give some mismatch or
> method or member not found error, shouldn't it (and start with
> capital C)?
>
> I have a couple of times in the 2003 version, probably with some
> beginning corruption, experienced that code has compiled successfully
> even with Option Explicit and undeclared variables.
>
> > "RoyVidar" wrote:
> >
> >> "Klatuu" <Klatuu(a)discussions.microsoft.com> wrote in message
> >> <1042F6C8-8ECD-4446-8143-79D128205904(a)microsoft.com>:
> >>
> >> Very nice code, Klatuu, I'm sure you have a declaration section
> >> where you declare all the xlConstants, don't you? (probably also
> >> contains declaration of "cell", too?)
> >>
> >> Else there'd probably be some challenges going late bound.
> >>
> >> --
> >> Roy-Vidar
> >>
> >>
> >>
>
> --
> Roy-Vidar
>
>
>
From: Douglas J. Steele on 13 Nov 2006 10:49
"Klatuu" <Klatuu(a)discussions.microsoft.com> wrote in message
news:DC98C903-E6AE-4C5B-828E-A94AA7F00D48(a)microsoft.com...
> What I read in Tony's article sound different to me than what you are
> saying.
> In addition, what Tony states, I find not to be correct. First, I always
> use Option Explicit. I do have a reference to Office 11.0.

Then realistically you're using Early Binding, even though you're using the
typical Late Binding function to instantiate the objects.

> The following are the declaration of my objects:
>
> Dim xlApp As Object 'Application Object
> Dim xlBook As Object 'Workbook Object
> Dim xlSheet As Object 'Worksheet Object
>
> The objects are instanciated with this:
>
> Set xlApp = GetObject(, "Excel.Application")
> If Err.Number <> 0 Then
> blnExcelWasNotRunning = True
> Set xlApp = CreateObject("excel.application")
> Else
> DetectExcel
> End If
>
> xlApp.DisplayAlerts = False
> xlApp.Interactive = False
> xlApp.ScreenUpdating = False
> Set xlBook = xlApp.Workbooks.Add
>
> Me.txtStatus = "Building Workbook"
> Me.Repaint
>
> 'Remove excess worksheets
> Do While xlBook.Worksheets.Count > 1
> xlApp.Worksheets(xlApp.Worksheets.Count).Delete
> Loop
> Set xlSheet = xlBook.ActiveSheet
>
> Regardless of how many reasons you can come up with, it compiles, it works
> it this and at least two other modules.

There's nothing in that code that I can see that shouldn't compile, whether
using Late Binding or Early Binding.

If your code was using any of the intrinsic Excel constants (xlBottom,
xlLeft, etc., etc.), then your code would not compile if you were using Late
Binding, unless you explicitly declare each of the Excel constants you're
using.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)



From: Andreas on 13 Nov 2006 11:06
Hello,

first of all thanks to everybody who contributed to this discussion.
I've take your ideas/ codes and entered them into my vba environment.
The problem, vba tells me it doesn't recognize

Dim xlApp As Excel.Application
Dim Wb As Excel.Workbook

saying "user-defined type not defined." What do I need to do to correct
this error as I have seen it before.

Thanks,

Andreas

From: Douglas J. Steele on 13 Nov 2006 11:22
You're attempting to use Early Binding, which means that you need to go into
Tools | References while you're in the VB Editor, scroll through the list of
available references until you find the one for Microsoft Excel n.0 Object
Library (n will be 11 for Excel 2003, 10 for Excel 2002, 9 for Excel 2000, 8
for Excel 97 and so on) and select it.


--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Andreas" <andreas.strzodka(a)ny.frb.org> wrote in message
news:1163434001.738373.175410(a)h48g2000cwc.googlegroups.com...
> Hello,
>
> first of all thanks to everybody who contributed to this discussion.
> I've take your ideas/ codes and entered them into my vba environment.
> The problem, vba tells me it doesn't recognize
>
> Dim xlApp As Excel.Application
> Dim Wb As Excel.Workbook
>
> saying "user-defined type not defined." What do I need to do to correct
> this error as I have seen it before.
>
> Thanks,
>
> Andreas
>


From: Van T. Dinh on 13 Nov 2006 16:36
Yes, I myself wasn't entirely convinced in my last post as indicated ...

The reason is that the For Each statement works with a collection and
therefore, somehow the Range has to be interprested as a collection. I
found that even though the Default Property of the Range object is Item but
it can be intepreted as Cells on how Item is used.

I see that you also found the Help details about the For Each ... with the
Range object.

--
Van T. Dinh
MVP (Access)



"Klatuu" <Klatuu(a)discussions.microsoft.com> wrote in message
news:A3D1ED18-BF91-4C4D-B1EF-A8AFEE13651A(a)microsoft.com...
> Not true. I always use Option Explicit.
> It is not executed only once. The result is as expected. Each cell in
> that
> range is affected.
>
> Sorry, you are totally incorrect on all points.
>
> I found that I also have the same technique in two other modules.
>


First  |  Prev  |  Next  |  Last
Pages: 1 2 3 4 5
Prev: Filedialog err 438 Object doesn't support this property or method
Next: DoCmd.RunCommand acCmdFind