Worksheet activation after a first calculation cycle has been completed

[Tempe_VBA]

Hi everybody,
I have completed a macro for processing GPS tracks from skydiving.
It is working but some of the features are a little clumsy.
I would appreciate some help to iron out the macro.
I am using MS Excel 2003.

A little more infos about the context.
The macro is processing Flysight GPS CSV files.
For those interestest by the device here is the link to the device
http://www.flysight.ca/

The macro grabs the gps file and returns metrics and a few charts.
To get there, the program needs exit time, terminal velocity, pull time and end of parachute deployment time.

I am not trying to figure out an algorithm to find those events in the data.
Those events show up nicely on a glide ratio plot where the X axis is just the row number.
I just check with the mouse the row number on the plot and fill a corresponding cell in the spreadsheet.
I have attached a screenshot to clarify the procedure.
The macro then grabs those data without headaches.

So here is how the macro works
Step 1 w/ button "Formatting" : load the CSV file --> data crunching --> ouput TEMP.xls
Step 2 : Manually input row corresponding to events --> Close file
Step 3 w/ button "Calculations" : Resume data crunching (file path in the macro, needs to be updated to work on other computer!)

I am looking now to improve step 3.
How can I resume the calculations without having to close the file at the end of step 2, and reload it to start step 3 ?
I went to the file closing because I do not know how the resume work on the open file.

I am attaching

J249 FS.CSVEvents.jpgTEMP w input.xlsGPS FS VBA v0.0.xls

the macro
GPS FS VBA v0.0

An input file
J249 FS.CSV

FYI, a temp file with event row number
TEMP w input.xls
(Note the program creates and recalls "Temp.xls")

A screnshot for clarification of the event identification procedure
Events.jpg

A final output file from the current version
J249 FS.xls

Thanks for the help,

Pierre

[Tempe_VBA]

J249 FS v1.xlsJ249 FSv2.xlsHi,
I just noticed that the final file is missing because it is exceeding a file size limit.
I split it in two.
Here they are.
Pierre

[Hellis]

Office 2003 is not as good as later versions with VBA.

I'm not sure I understand why you need to close the file to continue the work.
I have just looked through the code quickly.
I have made a macro similar to yours that workes as a add-in, that means the code runs seperatly form the file that is open (csv-file).

As your Excel is too old for it you probably can't run it, but you can see it on the videos. http://www.hellis.me

I will look at the code better when I have time.
But one thing I noticed, you can make the code faster with less loops. Instead of one loop for each calculation from row 3-LastRow you could do all calculations in one loop.

[Tempe_VBA]

This program is just a home project to practice VBA.

Thank, the loop is a very good ideasuggestion

The program starts a first series of calculation, then stops.
I then manually check the row index for the events (exit, pull, deploy).
Then I save and close the file.
Next I want to restart a calculation cycle to complete plots and metrics.
The current version recalls the temporary file.

I wonder how to skip the temp file saving steps.
Can I arrange for the 2nd procedure to call directly the file created by the 1rst one ?

I have attached the improved version, mostly on cosmetics.
Thanks,

PierreGPS FS VBA v2.0.xls

[Hellis]

I will have a closer look at it when I have time.
I have not looked at the latest v2 file, I'm at work and using my phone.

Let me see if I get this.

You have one "master" file that contains the code.
With this masterfile you open the csv and it calculates and outputs.
You now have two files open, the master and the csv?

You then have code to save the csv as temp.
Here code1 stops?

Now you manually input data in the sheet.
And then wants to start code2 that finnishes the job?
But you don't know the location of temp file when you want to start code2.



Does that sum it up?
If yes I think I have a few possible solutions.

[Tempe_VBA]

It sounds correct.

I do not know how to call the open file.

The fix I am living with is that I close the file and recall it.

Thanks for the help.

Pierre

[Hellis]

When I run the code on one of my files it deletes most of the lines and leaves me with the lines where I was at about 1100 hMSL and below.
I think that was the reason why it also chrashed later in "code1" without feedback on why.

Also it opened three workbooks on my computer.
One beeing the codeworkbook, one called "book1" and then one with the gps-data. Then it closed one (I think it was "book1").

I think the reason you can't "call" on the correct workbook is because you use "ActiveWorkbook".
When you push button2, "ActiveWorkbook" becomes the macro-workbook.

Instead I think you should use something like this:

At the very top add public wb as workbook :

Please Login or Register  to view this content.

Somewhere in the button1_click where you know it's running in the correct workbook add :

Please Login or Register  to view this content.

Now wb is the workbook.
So for example msgbox wb.name will output the name of the workbook.

In button2_click you could now use a simple wb.Activate to make sure Excel focuses on the correct workbook.
This means all the ActiveWorkbooks and ActiveSheets should point in the correct direction.

BUT.
It's not a good solution really.
You should try to avoid ActiveWorkbook and ActiveSheet as much as possible.
wb. or ws. is much better.
This means you need to define wb as workbook and ws as worksheet.

However I still believe a much nicer solution is a Add-in file that runs in the background.
A Add-in can be programmed to only respond to CSV-files with the correct content.

[Tempe_VBA]

Thanks for looking into it.
I understand better now what I have to do now.
And it is going to take me a little time.

The deleted line event needs a little clarification.
FS recommendation is to switch on the GPS several minute before exit.
The first datapoints are just garbage with fantastic altitudes.
So the macro cleans it up. The cut off altitude is found in the cell B16 of the master file.

You will also find in B13/14 the coordinate of the target for distance calculations.

Thanks again,

Pierre

[Hellis]

I figured there was something with the cutoff altitude.

I hope you don't mind a suggestion about that?
Instead of using a fixed altitude you could perhaps look at hAcc, vAccor number of gps satelites.
Those combined will probably give you a good way to calculate what row has "ok" data.
And instead of deleting, which is a very permanent solution you could perhaps hide the rows.
This means if your code messed up with the "finding good data" you can just unhide the rows.

Don't hesitate to contact me if you need more help with this.
I have spent years on developing my FlySight macro :-)