Gah! VBA Weirdness sucking all my time up!

[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,

[Alan Kirk]

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.)

[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,