Hello everyone! I am a geotechnical engineer and have been tasked with editing hundreds of excel files all in the same way so I decided to go back to my college intro to C days and make VBA do all of my dirty work!

Anyhow, *I* think I am having problems with my library. Someone else who was apparently a programming wiz had the incarnation of this computer before me, and I have a feeling that she MAY have done something with the library......

So to my problem!

The error I am getting is
"Run-time error '91': Object variable or With Block variable not set"

Here is a simple example of what gives me the error (not my code, but just an example :

Sub ThisIsMyError()
     
            Dim mychart As Object                 
                For Each mychart In Worksheets(1).ChartObjects
                 mychart.Delete
            Next mychart
End Sub

When I looked at my object library, I was able to browse the <globals> and saw that "chartobjects" was not there. Strangely enough "charts" was there..... go figure?

Now, a line like

Worksheets(1).ChartObjects.delete

Works just fine! However not being able to assign certain common objects to variables in excel severely limits me and requires that I bash my head against the wall till I find a work around, heh. I also have the same problem with assigning certain other objects as variables.

Oh, and I am using Excel 2003 SP3/VBA 6.5, if it makes a difference.

OK, this is really weird.... it's working now.... could I have somehow accidentally triggered the reference on or perhaps on a different script?

Usually when something is in a plural form, it is a collection of objects and there should be a singular form that can reference a specific object from the collection. However, to enumerate through that collection you would do something like...

Dim MyCharts As Object, AChart As Object
Set MyCharts = Worksheet.ChartObjects
For Each AChart In MyCharts
  '...

Now that is normally and the above code is just off the top of my head as I don't do much VBA programming so you would have to check on those objects.

As for why it is all of a suddenly working, well maybe it forgot its own reference and you reminded it??? :)


Good Luck

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.