One of the newer tools devised to make on-line data storage more effective is called Google Sheets. This free function allows the user to create XL files and to have them available for use on their smartphone or tablet, or any device with web access. It will not run Visual Basic macros, but it will execute the formula installed in any given cell. An example is shown here:
This is a section shot so you can see the formula bar. The user is clearly warned not to enter anything in the light blue cells, lest he delete the formula. I have used Google Sheets in this way quite often when working on the shop floor. It saves a lot of time locating and recalling formulae and notations, especially during machine set-up.
Its especially useful when doing R&D work. There’s no need to pull out the old trig book to figure out how much to change an offset after you’ve found that the 82-deg counter sink is 0.012 shy of the diameter tolerance. Simply plug into the sheet the measured diameter and the print diameter, and the amount of adjustment is indicated under the Z offset column. If the value is negative then you must go deeper; if it’s positive, then you must go shallower.
Here’s a note of caution: not every shop uses the same type of height offset adjusting or setting strategy. The simplest approach is to take the Z height for every tool. The problem will be that the tools need to be reset from job to job, even when the tools are the same for the next job and don’t need changing.
On large milling centers its best to take all offsets from a common point, for instance, from the table surface to the top of a 6-inch block. This will eliminate the problem of having to show an operator the new designated place for the offset for each job running. It becomes standard and eliminates guesswork.
Then, the offset is modified by a work offset such as G54 or G55 in a Fanuc or Haas CNC mill. So if all the Z offsets are measured from a point 6 inches above the table surface and the measured surface from the Z datum to the 6 inch surface is -1.223 inches, then the G54 Z work offset would be set to +1.223 to compensate for the difference. So, only the tools that are changed need to be reset.
On most machine tools these days the number of tool holders usually is enough to do most jobs. I used to reserve the first five to eight commonly used tools to keep set-up time to a minimum. For instance spot drills and a variety of end mills. This worked ut quite well in the job shop where I was employed.
Currently, I am currently working on a VB utility that will change the tool call-outs according to a preset list, so no matter what the programmer has defined for the upcoming job it would readjust the tool call-outs and offsets to match the list. Only tools not present will be used, unless there is a tool already there, in which case it will pick another pocket not used, or notify the programmer.
The next example shows the entire file, so you can see the links to speeds, feeds, etc. By clicking on these links the user is directed to the web site that has the needed information. Below is the full view:
The next view shows how this appears on my Android smart phone. It’s pretty much the same as the original, and it works great. I have the information I need with me all the time because the phone is with me all the time. The user only needs a Google e-mail account to access Google Sheets. Then, the file is uploaded into sheets and is accessible to the user, as well as any designated colleague whose e-mail address is registered in Sheets.
I believe there are many uses for this. It could be used to get QC data from an operator at will, to monitor progress or any quality problems that develop. It also can also track tool-wear using a Visual Basic macro.
For example, if the shop began to assign all perishable tools with shop numbers, their availability and use could be managed easily. As each tool is being used, the shop would assign a number to it. Then, the number could be tracked from job to job along with the feeds and speeds used in each operation. With some analysis, this information could be crunched and used to inform tool purchase options using a Visual Basic routine that is in the initial XL file, using the data gathered by Sheets.
Most tool vendors are eager to let customers use tools on a trial basis, so shops and programmers may be able to gather performance results without the full cost of investing in tools If you play your cards right they may even take you out to lunch.
The data-management functions freely available on the web are very useful to small shops. It allows you to manage the business without spending a fortune on software. It can be customized for your particular needs, and it’s easily changed as needs dictate.
The Visual Basic is a very powerful but easy-to-use programming tool. If you can program a job — that is, do the engineering, come up with the tool list and program it — then I am sure you can learn Visual Basic.
As I mentioned in an earlier article, I have created a variety of what I call VB utilities like SUB. These also can save a lot of time — which is what improved productivity all about. Time and quality.
Arvin Cook has been active in precision manufacturing for over 40 years, including work as an application engineer for a large machine-tool sales organization, and a contract tool maker for the Scientific Instrumentation Shop of Harvard University’s School of Engineering and Applied Sciences.
Contact him at [email protected]