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

[Larry$]

" I am also writing code to automatically scan the AllTPI sheet and find and extract ALL the " industry standard" UNF & UNC TPI values. I assume(?) these might be useful to folks as it is to me."
That would be the best for my use. Thanks

 

[B2]

Edit: I tried to generate the AllTPI for the ForLarry workbook for the PM1440HD last night and found that it generated way to many rows, 121,600! I had left the Zzz flag off of one of the 120:127 columns so the program stepped through 38 rows (mostly blank) instead of just 2. The flag tells the program that this set of gears has reached the end of its list. So I have deleted the file I uploaded last night and have now uploaded one with the Zzz flag. Maybe this will work better.



Hi @Larry$

" I am also writing code to automatically scan the AllTPI sheet and find and extract ALL the " industry standard" UNF & UNC TPI values. I assume(?) these might be useful to folks as it is to me."
That would be the best for my use. Thanks

Yes, very useful! I have completed a similar Macro code, but just have not posted it yet. Doing the taxes have gotten in the way! You maybe able to write a better code than I. I am still learning VBa. I have not tried to clean it up yet.

I set up an extra page where the standard Imperial and Metric threads (or any other the user want to put in) is placed in a single column. Then a macro extracts these one at a time as it works its way down a column and performs a search using a search subroutine. It also writes back to the list sheet displaying how many of each TPI values was found. Hence, one has a list which also shows which ones are missing. It seems to work well and I was planning to post it when I was finished along with some other new macros... one of which removes rows which are duplicated from AllTPI. That way the duplicates would not be in the table of the Standard threads either.

I put the words "IMPERIAL" and "METRIC" at the head of each list so that the search program knows how to turn on the 120:127 exchange gear. The TPI values needs to be in TEXT format for the search sub to work correctly. (By the way, I moved the 120:127 values to columns "T" and "U" in the uwPM1440HD sheet of your lathe. This is where they really belong. In the original version they were in columns "P" and "U" (technically incorrect) which worked fine because the other transfer axis values were set to a gear tooth value of 1. Columns OP, Q:R, and S:T are the three potential transfer/exchange gear axiales. The search sub sorts the the order of the columns and uses this info so putting the exchange gears at T and U is needed to switch to METRIC.

For most folks lathe there will be some standard thread values for which they do not have gears to make them. Hence, the search will show zero values. So the AllTIP is still needed so that one can see if the missing ones can be approximated. The Search subroutine allows for searches for approximations, but I envision that one would first run the list of standards and then where there are missing ones insert into the list the wild cards structure to find the approximations. That way there almost always be something in the final list of standards.

The Standard TPI setting values are simply written into the top of the AllTPI table as though you were doing several independent searches. This is not clean looking, but I had not settled in on what I thought the final list should look like yet and where I should put it. On the the other hand, the DELETE FLAG is still embedded and so it easy to get rid of the top rows with the Delete search Macro. Do you have a suggestions for a format for how to lay this searched information out in a separate sheet.? I suppose we could put it right back on the search list sheet leaving out extra title lines, but that seems kind of messy looking?

I think when I prepared spread sheet for your lathe I just looked at the picture of the charts on the PM1440HD and then guessed and assumed you had two-40T, 25T, 50T, 32T, and 30T . Those along with all of the gear box positions, including the feed conditions, would yield about 6400 rows in the AllTPI. You really do need the macro to remove duplicate cases.

Attached is a zipped version of the workbook but... just your lathe (truncated gear set to cut the number of AllTPI rows down), but it has the Standard thread sheet (uwSrchList) and the macro for extracting Standard threads. You will need to choose your gears in the 1440HD sheet and then run the TPI generator macro first. (remember the 120:127 column positions have been moved so it is not like the original workbook. I also made minor changes to some of the other macros.) Then you can run the "SrchListTPI" macro. The macro works on the A column of the SrchList sheet. You will see that one of the TPI entries is "999" This is a flag to tell the Macro that it is the end of the list.

Best Regards,
Dave L.

 

[B2]

@Larry$

So if you run the program that I just posted. You should find you can make all of the "Standard" IMPERIAL threads and many METRIC threads. The ones that you gear set will not make are: 0.2, 0.35, 0.7, 1.75, 3.5, 5.5

So I ran these missing ones using wild cards in the uwSrchList sheet to try to approximate them. It did a pretty good job, see below. In fact some of them like the 0.70004 would indicate that the spread sheet calculation may just be off in that last decimal places!!! The 127/120 should be dead nuts on so it is a bit of a mystery. I will have to take a look at this sometime. Anyway, it looks like we should not pay close attention to that last place or two so I may need to check the search macro or more likely the lathe sheet its self. The program finds the TIP value for the gears and then for the mm/T the TIP is divided so maybe some last decimal places is being cut off or something.

I would be interesting if you could test to see if these are as good as they say. I think in some or all cases they were using the feed setting and I had only estimated the feed rate from the inconsistent feed table plate.... so maybe by scratching some threads where the D (or F in some cases) positions were being used to see if the feed rate I estimated is accurate enough. Example, the 0.35097. However, this will be really hard to count the close spaced threads over along scratch (or count the spindle turns) and measure the length..... as your measurements would have to be extremely accurate ... requiring a very long scratch..... Unless my estimated feed rates are way off. Counting turns and using a long dial gauge might be a better approach. I recently check the calibration on a 1" throw digital dial gauge by ClockWise and it was good to within a few microns over 1 inch. (this was as good as my standards for doing the measurement).

More recently I purchased a slightly more expensive version of one of these from Clockwise. Amazon: "Clockwise Tools DITR-0105 Electronic Digital Dial Indicator Gage Gauge Inch/Metric Conversion 0-1 Inch/25.4 mm 0.00005 Inch/0.001mm Resolution with Back Lug Auto Off" There is also one with a probe tip set for just a bit more. https://www.amazon.com/Clockwise-Tools-DITR-0105-Indicator-Resolution/dp/B08H4943GZ
or their store https://www.amazon.com/stores/page/...b663-04d6-40a7-a4e3-9272a8cb582a&ref_=ast_bln

I paid about $63 (no probe tip set) because they promised to send an error curve, which they did. It is much better than the first as It is more robust

Dave L.

 

[B2]

@Larry$

I have pretty much completed the modifications to the workbook to generate a version of AllTIP which does not contain the obvious duplication of TPI values. However, I would like your comments to make sure that I have not missed something. Is there anything else that can be eliminated from the AllTPI table.

I think that the only duplication of gear settings that really need to be removed are of the following structures. Have, I missed anything common?:

1) The exchange gears of 120/127 are currently entered so that the 120 can be either on the front or the back. I.e. the 120/127 can be reversed. This means that there are 4 possible combinations when both the 120 and the 127 are entered in both of the columns of the lathe sheet. The generate the following combinations 120/120, 127/120, 120/127 and 127/127. Each of these are realizable as the spindle or gear box gear can be moved in and out to accommodate. However, while 127/120 and 120/127 produce unique results, the 120/120 yields the same TPI as 127/127. Hence, only 3 of the 4 combinations yield unique results and should be kept. This means that the total number of TPI values in the table can be cut by 1/4.

In the case of your lathe PM1440HD, with the possible external gears, the total number of combinations was 6400. This is then reduced to 6400*(3/4) = 4800.

2) The second set of duplication of results is for the spindle and the gear box gears are equal in value. For example, that means the when the spindle connection has a 25T gear and and the gear box connection has a 25T gear the same results will be obtains for any other values that are identical. I.e. 30:30 40:40 etc. as their effect is cancelled out. This means that the number of total TIP values can be reduced by simply allowing only one of these and then knowing that the others yield the same results. In this case, I am thinking I will replace the two identical values with sysmbols so that we know that any pair of identical gears yields the same results. I am thinking something like "# = #" rather than say 25/25 will be more obvious in the table. ("# = #" means "number" of teeth at the gear box "equals" the "number" of teeth at the spindle.) Is that obvious enough?

So suppose the spindle has "n" gears in its list and the gear box has "m" gears in its list and that of these there are j gears which are common between the two. Then after removing all the duplicates except for one to represent the case where the two gears are identical, then the total number of combinations is given by (n*m -j+1). In the case of your lathe where we list 25,30,32,40,50 in both of these positions the total number of combinations these generate are (5*5-5+1)= 21. Since the exchange gear 120/127 axial produces 3 combinations and the gear box produces (8*4*2)=64. It means that the total number of TIP values in the table would be (8*4*2)(5*5-5+1)(3)= 4032 total.

Hence, by just these two sets of duplicates the total has been reduced from 6400 to 4032. Still a large number.

These elimination of duplicates also applies to the 2 axes where other exchange gears might be used. These usually only occur on the smaller lathes where there is no extensive gear box.

3) For your particular set of external gears there is another set of duplicates for values on the Gearbox/Spindle gears: 32/40 is the same ratio as 40/50 =.8 and so these two combinations yield the same result. However it does not seem to be worth trying to eliminate these. Besides having them would make selection of settings more straight forward. Besides others may not have duplications of ratios!

4) There are some other duplicate TPI values, but these have to do with the gear box itself when working against the external gear combinations. However, these are dependent up on the particular lathe model and would not be easy to removed nor does one want to. For example, if you already have a certain set of external gears install, by simply changing the gear box settings you might be able to get the TPI value you want without having to change external gears.

New Macros:

So I still have some clean up work to do on the macro programing, and I have had to make some other changes in hour your lathe sheet is laid out, but I think I have a macro that implements the above AllTIP table reductions.

Also, as you suggested, I have also created a macro that searches from a user generated list of TPI or metric threads, say the Standard Thread TPI and Metric mm/Thread values or anything else you want to put into it, and searches of and generates a list of all of the TPI values requested that are available. This program also allows the user to use some wild cards to get the close approximations of the desired TPI value. For example, suppose you wanted to cut 32 TPI but did not have the gears to exactly match this. You could place 32.0* in the list and it would then give you all of the available entries, which are above ("Greater Than") 32.00000 up to 32.09999 and would also give you all of the available entries below ("Less Than") 32.0 such as 31.90000 to 31.99999. If nothing is found then one could simply change this list entry to 32.* and get a bigger set of approximations. There are other combinations of wild cards, but I primarily focused on using the * rather than the ? symbols. It turns out to be rather tricky about how to do all of this, so I spent quite a bit of time getting the "Less Than" approximations. But in some cases they are the best values.

I have not figured out how to present this new set of "Standard" TPI as a table yet, but would welcome ANY suggestions or discussion. Right now the results just appear above the AllTPI table as though one had done a number of individual searches, but without deleting any of them. Of course the old search macro did not do the "Less Than" approximations. I guess I should fix that too. Anyway, I am thinking just a table with a title row followed by the search results with only a line between each value. If I can add it, to make the table narrower and more printable, I will also hide/delete the columns where there is no data.

So I ran the Standard TPI list against the set up we have been using for your lathe.
I think the UNF & UNC TPI are: 80, 72, 64, 56, 48, 44, 40, 36, 32, 28, 24, 20, 18, 16, 14, 13, 12, 11, 10, 9, 8, 7, 6, 5, 4.5, 4
I think the standard course and fine metric pitches are: 0.2, 0.25, 0.35, 0.4, 0.45, 0.5, 0.6, 0.7, 0.75, 0.8, 1, 1.25, 1.5, 1.75, 2, 2.5, 3, 3.5, 4, 4.5, 5, 5.5, 6
Have I missed any?

Of these and with the 5 gears listed above for the PM1440HD either at the top (spindle position) or the bottom (gear box position) I found that you could make all of the Imperial threads. Of the Metric threads you were missing the exact values for: 0.2, 0.35, 0.7,1.75, 3.50, and 5.5. However multiple approximations were available when using: 0.200* (9 values) , 0.350* (4 value), 0.700* (5 values), 1.75* (6 values), 3.50* (6 values), and 5.5* (11 values).

I am hoping to finally get this posted in the next couple of weeks.

Dave L.

 

[Larry$]

I haven't tried using all the information you have worked hard on. I'm running behind partly because my computer skills don't come close to yours. I'm going back to the first iteration to see why I've struggled with some of this. I may need a step by step procedure to get me there. will get back to you. Being able to get close approximations like you have outlined seems ideal.

 

[B2]

@Larry$

No Problem. Seems I am always behind. Anyway, I struggle with the Excel Visual Basic Code, trying to learn a bit more about it is part of the reason I started working on this workbook, and why it keeps evolving (improving I hope). That, and the hope that other HM will find it useful.

Anyway, I am glad to assist you in anyway that I can. Step by step or other wise. I find that in the Excel VBA there is always someone, on the web, who has already tried to do what I want to make happen. Just Google search a the phrase "Excel VBA topic of interest" where the topic is something you are interested in. For example: "Excel VBA end of column" and you will get a bunch of hits on how to find the row number to the last entry in a column. Likewise, "excel vba find in a column" you will find hits on how to find a value in a column. A lot of times people even provide short pieces of code or entire subroutines on how to do this. Anyway, that is how I have been teaching myself.

I think I have the macro written to search the AllTPI table from a list of desired TPI values to generate a list of gear settings to use. I still need to compile this list into a useful table. Then we will have all of the standard threads or close approximations in a much smaller table. Anyway, getting closer but still polishing it up.

I found that on my PM1440GT the highest TPI value available, while not using the FEED bar to thread was about 73 TPI, But that the values were rather sparce and not standard values. So it occurred to me that why not use some other gears for the transfer position. Say a 30:127 rather than 120:127. This will push the TPI values up by a factor of 4. A 35:127 then changes the by the 30/35 ratio ... etc. Of course, the gears must fit.

I really like the double Norton gear box on my old South Bend 10 Heavy. It provided much more variety and I seldom had need to even change the external gears. It has 10 basic TPI values (4, 4.5, 5, 5.5, 6, 6.5, 6.75, 7, and 7.5) and these were multiplied by 7 factors of 2x. i.e 70 easy to find threads or feeds. Best of all it has a table on the front listing all of these values. No feed bar, only a lead screw. So the biggest thread was 4 TPI and the tightest is 480 TPI. Someday, I hope to rebuilt the saddle and feed lead screw etc. so I can use it. I might even put a three phase motor back into it and get a VFD for it. It is leather belt driven from the 1 ph motor that I purchased when I got it used and so only has three speeds. But first, I need to finish fixing my PM940M-CNC up.

Dave L.

 

[Larry$]

Anyway, that is how I have been teaching myself.

I'm older than dirt but still feel the need to learn new things. However, I got sucked into a project that is taking way more time than expected. My daughter decided she would remodel a basement bathroom. Of course with no experience, tools or time (3 kids!) This is in a rural area of the next county, no local supplies! I've done enough plumbing to know I'd rather not. Remove a big tub and all the circulating hardware, replace with a one piece tub/shower fiberglass unit. Drain in concrete floor in a different location! Framing needs to be changed. Concrete floor not level.

The result is I'm likely to be slow in getting back to you. Thank you for taking the time to do this for me.

 

[B2]

Whoa! I have been there ... You have my sympathy. Some sorts of jobs you have done enough times it is hard to find any fun in them. I grew up in a small town in southwest Mo. When I would go back to visit and would need to repair something, if you couldn't do it with what you had on hand or it did not fit on a tractor you had to drive 35 miles to a decent hardware/supply store. Take care.

 

[B2]

Hi Folks,

I finally finished with the newer version of TIP generating spread sheet. I just posted it at:

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

I thought it deserved its own HM "thread".

This one is much improved with a lot of new features. Including automatically generating a table for the Standard threads (Imperial and Metric). You can add or delete from this table so the user gets to select what is in the table that he prints out and keeps at the lathe. It has much better wild card features and with those you can get approximations to a TPI value where are both slightly smaller as well as slightly larger. It also has a macros macros for hiding columns you do not need to display.

Let me know what you think of it.

Dave L.