Simple VBA

[Tony]

Hello - I'm trying to write a simple VBA function to calc Income Tax given a
value for income. I have several worksheets in my workbook. From sheet 1 I
want to set a value in the Income_Tax sheet, have it do the calc, then
return the results. Here's what I got:

Function IncomeTax(Income As Double) As Double

ActiveWorkbook.Sheets("Income_Tax").Range("A1").Value = Income

ActiveWorkbook.Sheets("Income_Tax").Range("B1").Calculate

IncomeTax = ActiveWorkbook.Sheets("Income_Tax").Range("B1").Value

End Function

But when I try to use that function in sheet 1, I get a #Value! error.
What's wrong with this code?

Thanks for your help.

 

[AB]

You might be on a bit misleading path there.

I could be wrong, but i don't think that executible functions (that one can
enter into a cell and it would alter constants in other cells) are supported
anymore in Excel (i think there once were some 'call' type functions but
they were a security threat), i.e., if you need the vba to perform some
actions, you need to use Sub instead of a function, something like this:

Sub IncomeTax(Income as Double)'<------ you still need to pass the variable
of Income

dim myResultRange as range

set myResultRange = activesheet.Range("A1")' <------ the cell you want
the result to show up.

ActiveWorkbook.Sheets("Income_Tax").Range("A1").Value = Income

myResultRange.value =
ActiveWorkbook.Sheets("Income_Tax").Range("B1").Value

End sub

And then you'd need to call the macro from macro windonw (Alt+F8).

Even though i put the (untested) code above - it would seem that you'd be
better off by figuring out how to do that with ordinary Excel functions
(lookkups, indexes etc.) rather than a vba.

If you insist of using VBA then you would need to provide the Tax
calculation logic within your vba function (and not on a ws) and calculate
it solely within the vba function rather than trying to put a value onto
another sheet and then grab the output and put it back somewhere.

 

[Ron Rosenfeld]

Hello - I'm trying to write a simple VBA function to calc Income Tax given a
value for income. I have several worksheets in my workbook. From sheet 1 I
want to set a value in the Income_Tax sheet, have it do the calc, then
return the results. Here's what I got:

Function IncomeTax(Income As Double) As Double

ActiveWorkbook.Sheets("Income_Tax").Range("A1").Value = Income

ActiveWorkbook.Sheets("Income_Tax").Range("B1").Calculate

IncomeTax = ActiveWorkbook.Sheets("Income_Tax").Range("B1").Value

End Function

But when I try to use that function in sheet 1, I get a #Value! error.
What's wrong with this code?

Thanks for your help.

A function can only return a value. Most likely, if you use the debug tools,
you would find the problem to be in your second line above (....Calculate)

I would advise that you set up a sheet with the various tax tables on it. You
could then use appropriate logic (or VBA but it's not necessary), to decide
which table to use (e.g. S, MFJ, MFS, HH) and do the calculation with a
VLOOKUP.
--ron

 

[Tony]

I can make vlookup work, but it is very inconvenient. It would be much
better if I could make my technique work.

I think I'll keep at it. Rather than have the Income_Tax worksheet do the
logic of figuring out the tax, I could have VBA do it but reference the
amounts from the Income_Tax worksheet.

Anyway thanks for the suggestions.

 

[Ron Rosenfeld]

I can make vlookup work, but it is very inconvenient. It would be much
better if I could make my technique work.

I think I'll keep at it. Rather than have the Income_Tax worksheet do the
logic of figuring out the tax, I could have VBA do it but reference the
amounts from the Income_Tax worksheet.

You could have the tables stored on a worksheet, and then reference that sheet
in your VBA routine. (Of course, you could also hard code them into VBA
arrays).

In the US, I would use the 2009 Tax Rate Schedules (published on the IRS form
1040-ES, to do this.


--ron