convert 123 macro to visual basic in excel 2000

  • Thread starter Thread starter Joe Silvers
  • Start date Start date
J

Joe Silvers

Here is an example of a short lotus 123 macro that I use
to enter data into a formula sheet. I need to convert
this to a excel 2000 macro. I would like for it to
function exactly the same way it did in lotus 123.

{goto}E3~
'{?}{D}'{?}{D}'{?}~
{GOTO}C8~'{?}~
{GOTO}A1~{GOTO}D10~
{?}{D 2}{?}{D}{?}
{D}{?}{D}{?}{D}{?}
{D}{?}{D}{?}{D 4}"{?}~
{IF $E$98<D10}{HOME}{GOTO}D10~{BEEP}
{?}~{HOME}{GOTO}A25~{CALC}{?}

Thanks in advance.
 
I'll give this a try

range("e3").select 'not necessary to select
Click to expand...

The single quotes indicate that 123 would be forcing the entries to be labels no
matter what the user enters. You should do the same in Excel.

range("e3").Formula = "'" & inputbox("enter data")
range("e4").Formula = "'" & inputbox("enter data")
range("e5").Formula = "'" & inputbox("enter data")

Ditto.

range("c8").Formula = "'" & inputbox("enter data")

Ambiguous. Going to A1 puts the A1 cell at the top of the document window, but
then going to D10 is unnecessary. In this case, the Excel macro should include

Range("A1").Select

[regrouped]
range("d10")=inputbox("enter data") ...
range("d18")=inputbox("enter data")
Click to expand...

So far so good.
range("d19")=inputbox("enter data")
Click to expand...

Nope. {D 4} means go down 4 rows. Also note the double quote. This should be

Range("D22").Formula = InputBox("enter data")
Range("D22").HorizontalAlignment = xlHAlignRight
' I can't figure out what below is doing?
{IF $E$98<D10}{HOME}{GOTO}D10~{BEEP}
{?}~{HOME}{GOTO}A25~{CALC}{?}
Click to expand...
if [e98]<[d10] then
range("d10")=inputbox("enter data")
calculate
range("a25")=inputbox("enter data")
end if
Click to expand...

As written, the {IF} only applies to the other statements in the same macro
line, so this should be

If Range("E98").Value < Range("D10").Value Then
Range("A1").Select
Range("D10").Select
'no close equivalent to 123's {BEEP} - playing some sound would come closest
End If

ActiveCell.Formula = InputBox("enter data")
Range("A1").Select
Range("A25").Select
Application.Calculate
ActiveCell.Formula = InputBox("enter data")

In 123, it's unreliable to end a macro with {?}, but that's up to the OP to
handle.

For the OP: nothing in Excel functions *exactly* the same as 123's {?}, which
accepts *any* series of keystrokes other than [Enter]. It's possible to run some
Classic Menu macro commands while in {?} entry mode. If you're used to
constructing formulas while in {?} entry mode, you'll have to get used to doing
things differently in Excel.
 
Back
Top