I was experimenting with the ontime property as part of a development and found that i couldn't always disable it
This was the rescheduling section :-
Cnext = Now() + TimeValue("00:01:00")
Application.OnTime Cnext, "UpdateClock"
This was the disable
Application.OnTime EarliestTime:=TimeValue(Cnext), Procedure:="UpdateClock", Schedule:=False
Sometimes it works, sometimes it doesn't ... Any ideas?
Errrr, 'calculate 12 months' isn't very descriptive and i'm not up on excel enough to be able to read the code and decipher it in my head...
A good tip about nesting if statements and formulas tho :-
Find some space on the worksheet and write each individual formula into it's own cell and confirm they all work. Once you've done that you can construct the main formula in it's correct cell location by copying and pasting in the individual cell formula's.
eg
=if(dummy1>dummy2,dummy3,dummy4)
substitute for the real thing
Not sure if you'll get my drift.. Somewhere on this website, it explains the technique much better only i forget where exactly
You can use the macro recorder to set up all of that, the only qualifier being that the recorder stores absolutes within it's coding..ie - it will always open exactly the same file every time..
It's easy enough to modify the code afterwards by substituting the absolute filename with range(blah blah)..
It worked for me anyway and therefore, by definition, must be relatively easy..
I don't know of any shareware that will convert the file formats yet but Microsoft activesync will. It would need to be installed on the receivers pc obviously and i think the license agreement allows for up to 3 installations.
Alternatively you could convert the file to xls on your own pc before sending it. Open the file via activesync then save as filetype=xls
Now you mention it, i think it does carry a lot of text formulas...
Thanks for the advice..
Notch another success story to Ozgrid
Uploading isn't practical i'm afraid. The excel file is 14Mb and as i mentioned earlier i'm not 14Mb patient...
I don't think this is a macro problem because it also does it when i manually filter although not always... I suspect it's a windows problem but i was just wondering if anyone had seen similar behaviour in excel..
Here's a minor issue with excel that i've noticed but so far haven't yet felt the need to resolve.
I run a macro at work that opens a text file and pastes it into column a (all 65K+) rows. It then converts text to table and uses autofilter to group the data before copying and pasting into other worksheets.
What i've noticed is that the time taken to generate a filtered list or revert back to all data is highly variable and it doesn't appear to be related to the amount of data.. For instance it may generate a filtered list in 5 to 10 seconds but reverting back to all data can take a minute or more.
As i said, this isn't a major problem at the moment but it looks like i'm going to be doing a lot more of this kind of work and i'm not a very patient person.
Speaking as a complete Biffo on Excel i relish the experience.
:bouncy:
'out of memory' at 1.6Mb(mem in use) and 20 hours hard labour is not really where i wanna be but somebody's gotta go see what's out there i suppose....
I may be some time
Seems to me the easiest thing to do in excel is hit the litlle cross in the top right hand corner and head for the pub.
Or is that too obvious ?:cheers:
I'm working on a fairly new generation of production machine for Intel. These machines are entirely software driven and consequently all of the machine calibration is done through the software.
Problem is, there are at least 2000 different calibration scaling factors scattered throught a range of text files in a variety of directories.
My project is to get excel to collate all the current data, append it to the excel history file, flag suspect parameters and if neccessary plot the so the technician can assess whether there is a problem or not... I've applied spc control limits to automatically flag suspect parameters but at the end of the day, it's the technicians call and i would prefer to plot the charts automatically (and quickly) because some of the guys are not that well versed in excel.
This should be quick and easy for the experts.
I'm about to set up 600 or so dynamic named ranges in my workbook project. I can't find any reference in the help file to say there is a limit on this but bitter experience has taught me that i will get to 590 and excel will conk out...
Anybody know if there is a limit and if so, what it is?
Don't take this as a definitive answer but when i was searching the web yesterday for a solution to my VBA headache I noticed there is an add in or application that will take any plot (even in bmp format) and derive the equation....
Don't remember exactly where it was but if you search for excel engineering you'll find it..
Lads, great job thanks for the advice.. My wall is full of dents and my forehead severely bruised but i know a bit more about VBA and i'm back on the road with 'the big project'.
Dunno about the formula's v's macro's... I'm still wearing my vba L plates and it's a bit like skiing one legged down a black slope a la Jean pierre lunee - not pretty but i got to the bottom in one piece...
Didn't realise this forum was so busy - proves there is a market for someone who can drive a speadsheet anyway
:cheers:
Doug,
thanks - sounds like an excellent idea.. I'll get cracking on it right away.I'm sure it will stretch my macro abilities to the limit but that's no bad thing either.
Appreciate the help
I got halfway through a fairly gigantic Excel project (which included 50 or so charts) when the old 'out of memory' bug stopped me dead in my tracks....
I've since found through the internet that this is related to a bug in the way excel accounts for charts etc but I've not really found the definitive answer..
Any ideas?..
Furthermore, i've put a lot of work so far into this project and it looks like there is no way around the problem (from my perspective anyway)... I expect the finished project would need to display 100-200 simple line charts... Any ideas for an alternative method?
:cool:
