Yossi Faybish - hobbies - XL Visual Basic
Home Page
back to Hobbies...

Visual Basic fever...

...was another kind of fever (see the stamps one, lol) I was afflicted with, lasting for about three years while working for Orbotech.

It was born out of necessity. I was Technical Manager and got an additional nomination of Logistics Manager, a job destined for failure if to judge by the previous managers and by the perception surrounding it. I decided I will succeed, and one of the tools I brought in was Visual Basic programming.

Application.ScreenUpdating = False
Application.DisplayAlerts = False

No, I had no idea what it was, but I had a guide and mentor to follow, a colleague called Alex Alaluf, I got my own "logistics improvements" ideas to develop, and this tool was handy, available and (as I discovered lately) extremely powerful. I had only basic programmimg knowledge, the Visual Basic was (is) an extremely vast domain with relatively little good supporting literature (an exception being John Walkenbach; great books, but lacking sufficient depth and... quite expensive), and time (in)availability was a crucial element. So I started reading, writing basic routines, chasing Alex for advice, and starting to work very early in the morning until late in the evening to find the necessary time. Also taking jobs home, of course.

On Error Goto CanNotDeleteSCR
DirCheck = Dir(Range("=ScriptPath") & Range("=CustomerName") & "*_*.scr")
Kill Range("=ScriptPath") & Range("=CustomerName") & "*_*.scr"

To make a long story short - in a matter of several months I became a specialist on the subject, at least in those aspects I needed, and created some great applications, mostly logistics and/or service oriented. The advantage being: INDEPENDENCE. I originated the ideas, developed them, created them, issued them, corrected them - all by myself. No need for budgets, chasing programmers, project definitions, lengthy discussions.., it was all BANG! DONE. And the results on the quality of services provided by logistics were not late in showing up.

Sheets("dirty").select
Cells.Clear
Range("A1").Select
Sheets("AutocadTable").Select
Range("A1").CurrentRegion.Select
Selection.Copy
Sheets("dirty").Select
ActiveSheet.Paste

Visual Basic in combination with XL is a terrifically powerful tool. The official logistics program (called OpenUPTIME) did the basic admin work. But it lacked hugely in many aspects, which my programs complemented in providing. Among the programs I created:

- follow up repair orders sent and repaired, follow-up scrap, create distribution of received stuff based on stock levels in the various stocks.
- create statistics of shipments and advice cheapest option as per a variety of needs and suppliers, provide delivery success level, estimate arrival time/delay.
- calculate minimum stock levels based on history, special considerations, price, obsolescence, safety, forecasted peaks, etc.
- find part numbers based on engineer's definition, memory, logical naming, allow engineers to define their own part names.
- create a data base of parts' pictures, various search tools, colored labels per products family, purchased parts, compatibility back and fore.
- service calls history sorted per part, description, time frame, keywords (acting on data extracted from OpenUPTIME), customers, engineers.
- draw graphs of consumables usage.
- Golden Hole, my masterpiece, a program creating a registration template for a UV plotter based on customer printed circuits boards tooling, and resulting in drawing scripts to be run on AutoCAD and XL drawings, providing immediate and accurate information for sales and manufacturing. The printed program is about 1000 printed A4 pages.

ActiveSheet.Shapes.AddShape(msoShapeRectangle, Ox - 9.8, Oy, 9.6, 6.4 + YSFor).Select
Selection.ShapeRange.ZOrder msoSendToBack
ActiveSheet.DrawingObjects.Select
Selection.ShapeRange.Group.Select
ActiveWindow.LargeScroll ToRight:=1

I defined Visual Basic programming an art. I still maintain this opinion, since there is place to so much creativity in this tool that no other definiton would suit it. Pity that there are no really complete books and guides (schools) to handle it completely. I guess that even afer 3 years I was 10% knowledge and 90% hole. But even with such limited knowledge - the resulting projects can be magnificent.


stamps1
stamps1
stamps1