Improved Version: TPI, Feeds, X-Feeds: Generalized Lathe TPI Excel file: "TPI_ManyLathesRev1 N430_1600"

[B2]

Re: Improved Version: TPI, Feeds, X-Feeds: Generalized Lathe TPI Excel file: "TPI_ManyLathesRev1 N502_1630"

Edit: I re-attached the files. I found a small limitation in the way the macros were written and I have fixed it. See the posting below this one explaining this.

Hi Folks,

I will start a new thread for a new and improved version the Excel threading work book. I have worked pretty hard on improving this program and I think it is sufficiently better than the old version that it should be posted here. If you down load the files please make a short comment so that I will know that real people are getting them and possibly have used them. Has this task been worth worth my effort? I have spent a long time working on this project and I would like to know it has been helpful or not to others.

If your lathe model is not included I am willing to help you incorporate it into the workbook.

I will link this thread to the the previous thread on this subject. It is here, but the files posted here are much improved:

TPI Feeds X-Feeds: Generalized Excel file for your Lathe

This new workbook incorporates new macros with special features and minor improvements to the individual lathe sheets.
The improvements include:

-Generation of the AllTPI sheet with or without the duplicate rows. Without the duplicates the size of the table is reduced by at least 25% and if there are considerable external gears then much more.
-Improvements in sorting
-Improvements in searching for a value and approximate values. The user can input the desired value in many forgiving formats, Wild cards are still allowed, but now both values above and below the target are found.
-A new macro is added which searches from a list of thread values (Imperial and Metric) which the user inputs. As a default, the list contains the standard threads. So it generates a new table which has all of the lathe settings to get to any standard threads.
-A new macro is added which will automatically hide columns which provide no value to the final table. Of course there is also hide macro which allows the user to manually choose the columns to hide. Likewise there is a macro to unhide the columns.
-A new macro is added to allow the user to input eye guiding lines to the AllTPI table. Of course there is a new one that also removes the lines.
-The ReadMe sheet has been significantly rewritten and hopefully provides more insight to how the lathe sheets function so that if your lathe is not included it is easier to make a sheet for your model lathe.
-There are others which are described in the ReadMe sheet.

Attached are the files:
-The Workbook which includes the macros installed and is ready to be run. (Zipped so that it can be posted)
-The Workbook which has the macros removed. You can still operate the lathe sheets manually. But other than just getting an idea of how things work, this is of limited value.
-A Zipped file which only contains the macros. If you wish you can open these with a simple text editor to convince yourself that there is nothing bad in the text, Then you can import these into the Workbook which has no macros. The top two or three lines of these text files have an Attribute command which assigns the Keystroke short cut description for the importing into Excel. However, if you like you delete these, create a blank macro and then copy/paste the macro text into the version you created.
- A pdf file is include which basically shows the ReadMe sheet in a format that has word wrapping. (Significantly easier to read than on the sheet inside the spread sheet. )

I had some trouble zipping and uploading these files so please let me know if they even work. I had run the macro to generate the AllTPI table for my PM1440GT and the resulting the table had 21840 rows of possible TPI values (after the duplication were left out) and so the file size was too large for HM. So I deleted that data sheet, zipped it again and am trying to post it now. The same was true for the version without the macros.

Dave L.

 

[B2]

Hi Folks,

Sorry to say I found a limitation in the way the sorting etc macros were working in the zipped files of the previous posting. So I edited that posting and replaced the files with ones that should not have this limitation. I had never seen this limitation before as my lathe with its standard gears yeilded ~32000 AllTPI, just under the limitation.

The limitation was that I had declared some of the counting variables a integers, which are only be two bytes in length and so the values cannot be larger than this limitation, 32,767. I discovered this when I included a number of extra gears in the lathe sheet and generated the AllTPI table. The macro to generate this worked fine but a couple of the macros for sorting/searching the table had a problem as there was now close to 100,000 rows of possible threads. So, I edited these macros and these variables are now declared as Long (Integers) of 4 bytes. The 4 bytes allows the value to be up to 3.402823E38 ! So this limitation should be no more! Just for your information, when I generated this larger table it took close to 30 minutes to complete.

Anyway, for those of you who had already downloaded the original file its name ended in N430_1600 which is my date and time stamp. Since the Workbook without the macros did not really change I just reloaded the original versions of those. But, I would recommend that you grab the new versions of the zipped files which are date stamped N502_1630.

Dave L.

 

[B2]

Hi Folks,

@LROYSON , @Aurelius

A couple of folks have the PM1228VF-LB lathe and have ask about the spread sheet for it.

So I have setup a sheet for it. The non-macro version is attached so that you can simply copy the entire tab called uwPM1228VF-LB and put it into the Macro functioning workbook (TPI_ManyLathesRev1 N502_1630.xlsm) posted above. This way I do not have to post zipped files.

Just open the Zipped macro enabled version of the overall workbook (TPI_ManyLathesRev1 N502_1630.xlsm) and insert a blank tab. Then click the upper most left corner of the tab "uwPM1228VF-LB" (PM1228VF-BD N520 For Installing_N520_1415.xlsx) to select the entire sheet and then copy it. Then do the same click on the new blank tab of the macro enabled workbook and paste the entire copied sheet for the 1228 into this new blank sheet of the workbook. Don't forget to change the new tab name from "Sheet..." to "uwPM1228VF-LB" and the workbook will be ready to go. The macros of the overall workbook (TPI_ManyLathesRev1 N502_1630.xlsm) will then operate on this new sheet as well as the previous ones available.

Also for your reference, attached is a pdf version of the STANDARD threads table that I generated from running the spread sheet for the 1228VF-LB. It contains approximate values as well as exact values and so is several pages long. You may find this handy.

Also, I found a very minor improvement to the macro for un-hiding columns in the AllTPI sheet. So this new version of the macro is attached and can be installed into the Macro enabled workbook .... if you wish to do so. Everything works fine without it, but this is a minor improvement. I will attach it as a text file, which you can open with notepad or other text editor if you wish to do so. Otherwise, just change it's extension to .bas rather than .txt and then you can use the "import" command to bring it into the workbook as a "Module002_HideUN_N_Col_AllTPI" at the Visual Basic Editor. (You may have to first delete the old module with the same name.) By doing it this way the keystroke commands should still be valid. If you do not know how to do this don't worry about it. Just skip it. Or contact me and I will walk you thought it. The only change to this macro was that the default letters that show up in the Q and A box now contains the entire alphabet rather than just few letters. In the origin version the column R had inadvertently been left out. .

By the way, I ran the AllTIP macro as well as the standard thread generator macro on the PM1228 and they are also in the attachment as excel tables attached.

Lastly a question arose as to which gear upper or lower touches which exchange gear. In the spread sheet for your lathe you will see at column V, cell V21, in the orange you will see the word "Spindle". This means the upper gear and below this cell farther down you will see the possible gear tooth values. Well it is next to column U and at cell U21, it says "A2Spin". This means axle A. The spindle gear touches "2" the gear at Axle "A". Next to this is column T. Which has the words "A-A". Column T and U represent the axle A which in most cases is the exchange gear. The gear at column T is touching the next one to the left and so forth through axles B and C until you arrive at Column O which has the words "Screw2C". This means the Lead screw/gear box is touching this external gear. The ratio of the exchange gear is determined by the gear values of, columns U and T, of axle "A". So suppose both locations select the 86T gear, then this ratio is 1 and this gear has no effect on the final TPI value. It just passes the rotation of the other gears through. On the other hand if the bigger tooth gear of the pari is in contact with the spindle (upper gear) then the TPI value will increase. For example in the 1228 ONE-A gear box setting and 30Upper and 90Lower gears are used and no exchange gear is in play the lathe produces 72TPI. If the exchange gear in contact with the spindle is 91 and the exchange gear that is incontact with the lead screw is 86. then the ratio of these to is 91/86 and this so 72*91/86 = 76.186 is the new TPI value. This also happens to yield 2.99943 t/mm or 0.3333mm/t (metric). Likewise, if the exchange gear is flip over so that the smaller gear is touching the spindle gear the we get 72*86/91= 68.044TPI. So in summary, put the larger exchange gear in contact with the upper gear to get a larger TPI value.

I look forward to any feed back. It is nice to know that someone is getting some value, positive or negative, out of this effort.

Dave L.

 

[LROYSON]

Thanks B2 for the new workbook and all of the work you did to add my lathe and generate a comprehensive set of data to help me do any tpi my lathe can handle.

If anyone wants to know how to use workbook to generate there own tpi data for there lathe reach out to B2 in the chat and he will explain it all. I know because I had plenty of question and he answered them all.

 

[B2]

Hi Folks,
Comments regarding experiences, additions, improvements made etc. about this project/Workbook would be appreciated.

Nov. 6 2023 Excel workbook file name: TPI_ManyLathesRev1 NB06_0054.xlsm

Edit: Corrected lathe sheet G4003G and so changed the file and its name to: TPI_ManyLathesRev1 NB06_2023
I got more info about the gears available in the the G4003G

The fundamental worksheet format and Macros have not changed, but
I added a couple of lathe sheets to the TPI generating Excel workbook. So I thought I would just post the current workbook.
It now has lathe sheets for the following lathes, but many other lathe models are similar or the same...just by different names.

NB06: Currently Available, LatheModel TPI sheets:
PM1440GT
PM1440HD - ( @Larry$ @verbotenwhisky )
PM1340GT_PM1236T
PM1236-1236M
JET-BD1340
Atlas618
MMLB-Norton - ((MetalMax) @Provincial )
MM1340LB-Lever - (MetalMax)
PM1228VF-LB - ( @LROYSON , @Aurelius )
G4003G=?=G4003 - (waiting feedback from @TakeDeadAim)
PM1130V - (TBD: @JPMachine to review. Remove some gears before running the GenAllTPI_14V2 macro)


Dave L

PS. Just download, Unzip, ReadMe tab and run it. To use the macros click on the Developer tab and enable the macros.

PSS. FYI, File Date code: NB06_0054 ==> N=2023, B=Nov., 06=day of month (today), 0054= 54 min. after midnight.

 

[B2]

Hi Folks,

I see that the TPI_ManyLathesRev1 NB06_2023.xlsm workbook as been downloaded a couple hundred times. It is nice to know that it is being looked at and perhaps used? However, I have found that the portion of macro in this workbook which generates the AllTIP table and has the option to eliminate some of the duplicates does not always do the best job. If you use the macro to generate the complete TPI table it appears to work fine.

My approach when writing this duplicate removal portion of of the code was rather ad-hoc and it misses some of the cases. Worst than this, it can cause some valid TPI values to also be lost. So I have spent quite a bit of time and now believe that I have a new macro which eliminates all of the duplicates not just some of them and it does it correctly. It still gives the user the choice of whether to generate the full table or not.

Before I post it I plan to briefly review the other macros and maybe write up a better description of operation. However, if you have used the spread sheet I would appreciate your comments or suggestions. Also, if you added a different lathe model than what I had last included I would like to add these to the next version of the workbook ... for others to also use and share. Please let me know.

Thanks,
Dave L.