Gah! VBA Weirdness sucking all my time up!

Not related to a specific OLAP tool. (Includes forum policies and rules).
Post Reply
User avatar
Steve Rowe
Site Admin
Posts: 2410
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: TM1 v6,v7,v8,v9,v10,v11+PAW
Excel Version: Nearly all of them

Gah! VBA Weirdness sucking all my time up!

Post by Steve Rowe »

I've a piece of code that I'm developing and on Friday when I finished working on it all was OK..
Now it isn't...
Spent an age tinkering with it trying to figure what is happening.

Part of the script is to remove TM1 formula from the workbook and the macro is basically the TM1 Tools macro from elsewhere on the forum.

When it gets to the
c.Formula=c.Value

the script throws an Object required error.
I don't understand why this error is being generated and I think it's false positive some how. If I run the TM1Tools macro which is the same script it does not error.

The other strange thing is that when I step through the code it seems to execute the line twice, only generating the error on the second execution of the single line.

Any clues as to what it is going on, I'm pretty competent at VBA so this has got to be something pretty unusual or broken...

Tried restarting etc, but this is the same macro that my virus scanner was interfering with and I’m wondering if the compiled version of the script has got corrupted. Anyone know how I can force a full recompile?

Cheers,
Technical Director
www.infocat.co.uk
User avatar
Alan Kirk
Site Admin
Posts: 6606
Joined: Sun May 11, 2008 2:30 am
OLAP Product: TM1
Version: PA2.0.9.18 Classic NO PAW!
Excel Version: 2013 and Office 365
Location: Sydney, Australia
Contact:

Re: Gah! VBA Weirdness sucking all my time up!

Post by Alan Kirk »

Steve Rowe wrote:I've a piece of code that I'm developing and on Friday when I finished working on it all was OK..
Now it isn't...
Spent an age tinkering with it trying to figure what is happening.

Part of the script is to remove TM1 formula from the workbook and the macro is basically the TM1 Tools macro from elsewhere on the forum.

When it gets to the
c.Formula=c.Value

the script throws an Object required error.
I don't understand why this error is being generated and I think it's false positive some how. If I run the TM1Tools macro which is the same script it does not error.

The other strange thing is that when I step through the code it seems to execute the line twice, only generating the error on the second execution of the single line.

Any clues as to what it is going on, I'm pretty competent at VBA so this has got to be something pretty unusual or broken...

Tried restarting etc, but this is the same macro that my virus scanner was interfering with and I’m wondering if the compiled version of the script has got corrupted. Anyone know how I can force a full recompile?
To force a recompile you need only add and delete a couple of lines of code, then use the Compile VBA Project command from the Debug menu.

As to the actual error... I'm wondering whether it's getting tripped up by the way the TM1 add-in "takes over" certain events such as the Worksheet_Change event. I'm not familiar with the code in the TM1Tools and won't have time to look at it until I get to work but it may be worth checking what the state of the Application.EnableEvents property is when it executes the equivalent line of code to yours. (The fact that the line is executing more than once makes me suspect this.)
"To them, equipment failure is terrifying. To me, it’s 'Tuesday.' "
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.
User avatar
Steve Rowe
Site Admin
Posts: 2410
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: TM1 v6,v7,v8,v9,v10,v11+PAW
Excel Version: Nearly all of them

Re: Gah! VBA Weirdness sucking all my time up!

Post by Steve Rowe »

I got to the bottom of this it was the fact that events were enabled that was killing my code. I've not for found where it gets turned back on again though.

My suspicion is that
Application.Run "TM1Recalc"

is doing it but I've not proved it.

Cheers,
Technical Director
www.infocat.co.uk
Post Reply