Application.WorksheetFunction stops working if the Sub is called

[emmalg]

Hi All,

I'm new to VBA and am sure there is a good reason for this that has completely escaped me.

I have the following sub which is one of the earliest I wrote:

Please Login or Register  to view this content.

If I run this as a standalone Macro it works just fine, if instead I call the function as shown below I get a "Run-time Error '1004': Unable to get the Log10 property of the WorksheetFunction class.

Please Login or Register  to view this content.

I wold be extremely grateful if someone could explain why this occurs and how I may fix the problem.

Cheers!

[emmalg]

In fact, I've just realised it doesn't even execute the calls to ReformatDate, ReformatTime, ReformatXName at all either! :-)

[MarvinP]

Hi emmalg,
Is there a chance that your early routines erase or overwrite the Sheet2!(counter,22) cells with non numeric data?

[MarvinP]

Hi,

This looks like a case for Option Explicit to solve. See
http://www.cpearson.com/excel/declaringvariables.aspx

[shg]

Sounds like you have a problem in CreateQT.

Another way to do AddK:

Please Login or Register  to view this content.

[emmalg]

Hi All,

I have Option Explicit in use as recommended by Marvin P in an earlier thread. The code will compile without errors and if do F5 and select each of the sub routines I have called in turn (CreateQT, ReformatDate, ReformatTime, ReformatXName, AddK and Stats) everything works perfectly, I was just getting a bit sick of having to run so many separate marcos when I wanted to check the end to end process.

The eventual aim is that everything in the work book will be completed by the user selecting one or two marcos.

sgh - Thank you, it seems to work (though not with the rest of my code I get the same error)... Looks like you are right there is an earlier error but it isn't in CreateQT.

I think I have found the issue (or at least where it starts) - when I call ReformatDate from PrepSheet, it isn't inserting the data correctly. Run on it's own it creates a new column before F and it first enters "aquisition date" in the top cell of the new column.... Except this is turning up in column U!

BUT! If I put a breakpoint in at the lRow=lastRow() line, run PrepSheet and step through from the breakpoint it works perfectly again

The guilty code:

Please Login or Register  to view this content.

etc...

[shg]

One thing is that you aren't passing a worksheet reference to LastRow, which means it's computing last row for the active sheet.

The "etc." part is difficult to comment on.

[emmalg]

So long as I keep the break point in ReformatDate lRow=LastRow() and step through until the column has been inserted ALL the subsequent functions work perfectly, including ReformatDate which adds a new row in exactly the same way.

Am totally confused!

[emmalg]

Sorry sgh - the "etc" was supposed to be in the code tag, i left it out as the error is related to the insertion of the column.

I accept your criticism of the lack of worksheet being passed to Last Row - I've been activating the worksheet before calling it ans I had ommited to here - didn't make any difference when I did though.

Now off to rework the last col and last row to take a sheet reference as input...

[emmalg]

Hi All,

I have found the problem but I can't figure out how to fix it!

If I watch the spreadsheet while the PrepSheet routine is executing, cell A1 says "ExternalData_43: Getting Data ..." and in cells F1 and column G1 it already says "acquisition_date" and "acquisition_time".

It appears that the CreateQT sub routine does not complete before the other sub routines have started, therefore everything breaks and the run-time 1004 error message pops up.

Now that I understand the issue I have a clearer question:

How can I ensure that my sub routines are run to completion in sequence and not in parallel?

Thanks for your patience!

[shg]

There is a Refreshing property that tells you the status of a background query, and a BackgroundQuery argument for SQL queries that will not rqeturn control until the query is complete.

[emmalg]

Hi sgh,

So you mean At the end of the query you have to do .Refresh? My query is shown below (I have cut out several lines of the sql query in the extract below just because it is so long), I believed this syntax to be correct, though this was the first macro I ever created and I followed examples posted online. If you have any advice I would approciate it.

Please Login or Register  to view this content.

[shg]

I really have none for queries, sorry, but am certain that other people could advise.

[romperstomper]

Use:

Please Login or Register  to view this content.

[emmalg]

Hi sgh,

Actually you are a star! What you just said about the BackgroundQuery property was just what I needed, I thought the default was for it to be False but it must be True, by replacing the oQt.Refresh with oQt.Refresh BackgroundQuery:=False absolutely everything works!

Thank you so much!

[emmalg]

Thanks romperstomper, I just beat you to it but it was still what I needed! :-)