Lotus vs Excell 6

If it ain't broke, don't fix it. If Lotus does what you need, why switch? Other than, of course, "everybody uses Excel."

Hg

Eng-Tips policies: faq731-376

I was forced to switch from lotus to excel also - but that was 12 years ago when I started using Windows... I can't say I have even seen a lotus spreadsheet in the last 10 years. So I would say, it's about time to switch. However, I am still using the same old HP41CX calculator that I got in 1985...

vs
Seems pretty straightforward.

Cheers

Greg Locock

SIGlease see FAQ731-376 for tips on how to make the best use of Eng-Tips.

to CVG

In your old Lotus days did you do any macro programming. If so how was the transition to VBA. Did you ever find a workaround for the Lotus {?} command.

old Ca SE

You can go mid-way and use OpenOffice.

Can't you use excel as a front end for your 123 spreadsheets?



Cheers

Greg Locock

SIGlease see FAQ731-376 for tips on how to make the best use of Eng-Tips.

I used 123 almost exclusively until about 3-4 years ago (just checked, make that 5 years!), when Strand7 (FEA package) launched their API. It worked with Excel VBA, but not Lotus Script, so I started working with VBA and found it more productive to do everything in Excel. Also for macros used by other people I was having to maintain 123 and Excel versions, which was a waste of time.

As a spreadsheet programming language I think VBA has many advantages over Lotus Script, but I still prefer the actual spreadsheet interface in 123. The main things I miss are 3D fuctionality, and being able to write a "classic" macro for a quick and dirty application. Also I find the 123 help system way better, particularlty when you just need a quick reminder of some detail. Another major hassle with Excel is that importing data from text files is nowhere near as easy as in 123, but I've got that sorted now ( )

Advantages of Excel include formula highlighting and better tools for drawing and multi-level undo.

Converting Lotus Script to VBA is actually not that hard once you get the hang of it, but converting old keyboard macros obviously involves much more work.

Where my old 123 spreadsheets are still applicable to my current work, I still use them, but I'd say I used 95% Excel now.

Finally, if you make much use of macros, or particularly if you use Lotus Script, I wouldn't recommend trying to convert them to Open Office, or at least not until they have full VBA support. Open Office basic is a very different beast to either VBA or Lotus Script, and I couldn't justify the time in trying to learn it.

Doug Jenkins
Interactive Design Services

I never did much macro programming other than keystroke macros. Excel will do the keystroke macro also, but you would have to recreate them. I would agree that if you have a need for programming, that VBA in Excel is quite powerful and way better than Basic - which I also used back in 1985. I still wish I had my old Turbo Pascal compiler up and running though... (perhaps with an upgrade to use more than 640K of memory)

@mudflaps

Read and - Visual Basic Equivalents for Lotus Macro Commands (WE1277) -

{?}
Suspends macro execution until the user presses ENTER, letting the user type any number of keystrokes.

No direct macro equivalent.
As an alternative use an input box:

ActiveCell.Value = Inputbox("Enter string or value")

Click to expand...

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

How about macro equivalents to Quattro Pro version 5(DOS)???

Joerd - I know about the "inputbox" but input is only one of the many other reasons for wanting to pause a macro. AND the input may need to be a formula made up of cell references. Once you get the input box can you navigate the worksheet to build formulas like you can in manual input mode? Very helpful if you can. Is there a way to make Excel supress the input box and let you enter your input directly into the cell where the cursor (oops - marque) is currently located?

S. Gregory - I've never used Quatro, but one of my partners in another office has. I'll check this out with him. Obviously you think Excel might have something from Quatro that's similar. Or are you just thinking of making the change too.

old Ca SE

I use both Quattro Pro and Excel. I have moved some of the simple ones to Excel. I use Excel now when I can instead of the old DOS program. But I really liked the macro commands and the macro recording features in Quattro Pro. I'm sure they are not as powerful as VBA, but easier to understand and use.

Attached are two VBA forms I created to function like a MsgBox and an InputBox, but they are non-modal, meaning that macro execution will pause until you interact with the box. While the box is displayed you can do anything you want to the spreadsheet.

It's not quite as flexible as a MsgBox/InputBox (can't pass arguments for different buttons, etc.), but you can change the form/code as you see fit if it doesn't quite match your current situation.

Included is a little demo Excel file to show how the YesBox works. To add the YesBox or InBox to your macro, just right-click in the Project Explorer portion of the VBA editor and choose "Import".

-handleman, CSWP (The new, easy test)

Handleman - Kudos to you! Your Yesbox macro seems to capture the essence of the Lotus {?} macro function. I've got at least one very heavy and thick Excel book that said "It's not possible", but you did it. You other guys should take a real hard look at this thing. Some of it's uses aren't readily apparent. Besides just data input, one use is having a macro select part of a range to move or copy, then have Yesbox make a pause so you can make adjustments to the range. This would be done when there is no earthly way your macro could ever anticipate the exact area you wanted to move/copy. Have Yesbox pause again so you can point to the move/copy destination.

I can now justify spending more time on Excel again. Handleman, I'm impressed!

Old Ca SE

Glad you enjoy them, mudflaps! I originally wrote them for use in SolidWorks, but tried to make sure they would work for any VBA. There are a couple of caveats I probably should have mentioned before. One is that since the whole point of the thing is to let the user do anything, the user really can do anything. Change sheets, open some other file, delete a bunch of stuff, whatever. The user could easily take any number of actions that put Excel in a state that your macro is not able to handle properly. Not so bad when you're writing stuff for your own (or other intelligent people's) use, but definitely something to keep in the back of your mind.

The other caveat is that in order to keep the usage syntax as simple and as much like MsgBox as possible, the code in the form uses a DoEvents loop, which does take up processing power. If you'll check your Task Manager while the YesBox is displayed, you'll see Excel using 100% of your processor. Basically, using DoEvents in a While loop as I've done tells the processor, "Go do whatever other stuff you have to do, but as soon as you're done, check back with me and see if I need you again." There are more code-intensive (and less processor-intesive) ways to make Excel sort of "pause" code, so if you need to do any heavy-duty processing actions while code is paused you might want to look into those. As I said earlier, my main goal with these was easy implementation and similar syntax to MsgBox.

-handleman, CSWP (The new, easy test)

I am a fan of Excel VBA. It took me a long time and four years to crack the message of the godawful unintellegible VBA references. Once I got myself up and running I realised like everything else in life, 95% is cr&p and what I needed to know was quite simple.

My favourite trick is redrawing graphics to scale alongside changing numbers. Looks so cool after the static spreadsheets with meaningless graphics; you know the ones showing a square foundation for rectangular numerical inputs and don't know which one is the shortest side, relative to the applied moment?

In Excel 2008 and beyond it will become VSTA and this is intended to support VBA but I don't think 123 will qualify. there is much concern that even VBA will be obsoleted, of it will in time.

Robert Mote

@ GregLocock (Hyundai Excel vs Lotus Esprit):

"Top Gear" recently broadcast an episode here in Oz (recorded 2007 in UK) where Jeremy Clarkson had to review a "sensible car" - BBC management said they spent too much time looking at only super-cars. He chose a Daihatsu Materia 1.3 litre hatchback - and then goes on to do a side-by-side comparison against its "close rival" - the Ascari A10 5-litre super-car.

His verdict: the Daihatsu won on all counts - 4 doors vs 2, much more practical interior, a tenth of the fuel consumption, 1/100 th the purchase price, etc. (Although he did "concede" that the Ascari was a bit quicker in a straight drag, and around the race track!)

Hilarious!

I am always appreciative when people offer new ideas. However, I don't yet grasp the sweeping advantages that mudflaps is describing. I can already have the user select a range via inputbox:

I guess the advantage is that I can prompt the user to do other more general tasks while the program waits?

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

Good tip, EP. I didn't know about Excel's InputBox. It's certainly much more powerful than VBA's.

-handleman, CSWP (The new, easy test)