Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple VBA
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").Va lue = Income ActiveWorkbook.Sheets("Income_Tax").Range("B1").Ca lculate IncomeTax = ActiveWorkbook.Sheets("Income_Tax").Range("B1").Va lue 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. -- Tony |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple VBA
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").Va lue = Income myResultRange.value = ActiveWorkbook.Sheets("Income_Tax").Range("B1").Va lue 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. "Tony" wrote in message ... 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").Va lue = Income ActiveWorkbook.Sheets("Income_Tax").Range("B1").Ca lculate IncomeTax = ActiveWorkbook.Sheets("Income_Tax").Range("B1").Va lue 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. -- Tony |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple VBA
On Sun, 9 Aug 2009 10:07:38 -0600, "Tony" wrote:
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").Va lue = Income ActiveWorkbook.Sheets("Income_Tax").Range("B1").Ca lculate IncomeTax = ActiveWorkbook.Sheets("Income_Tax").Range("B1").Va lue 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple VBA
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" wrote in message ... On Sun, 9 Aug 2009 10:07:38 -0600, "Tony" wrote: 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").Va lue = Income ActiveWorkbook.Sheets("Income_Tax").Range("B1").Ca lculate IncomeTax = ActiveWorkbook.Sheets("Income_Tax").Range("B1").Va lue 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple VBA
On Sun, 9 Aug 2009 21:58:43 -0600, "Tony" wrote:
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
IF formula-simple question; simple operator | Excel Discussion (Misc queries) | |||
Simple problem, simple formula, no FUNCTION ! | Excel Worksheet Functions | |||
Simple Simple Excel usage question | Excel Discussion (Misc queries) | |||
Make it more simple or intuitive to do simple things | Charts and Charting in Excel | |||
simple question, hopefully a simple answer! | Excel Programming |