VBA Function Based on Parameters & Data Range

I've been working on this for a couple months and I have been stalling on it. Unfortunately, Excel just shows #VALUE?! in the cell where I have this user defined function entered in to. I am having a tough time finding out where the problem is. I suspect it has to do something with how I bring the range of data in. Here is the full code...

Please Login or Register  to view this content.

All help is appreciated.

Please Login or Register  to view this content.

morttabarray is an unitialized range variable; that won't work.

Please Login or Register  to view this content.

seg3yrs is undefined

Originally Posted by shg

Perhaps you could describe what it's supposed to do, so we don't have to reverse-engineer your code?

It is supposed to read mortality data from an M x N. Each column is a different type of mortality table and each row is the number of lives from a base population of one million at a given age from 0 to 200. The parameters bring in some variables to tell the function which table to use (pqm & sqm) and which age to start at (page & sage). The other parameters are variables that go into a type of life annuity including three different interest rates at segments of the following 5 years, the 15 years after that and the remainder of life after those first 20 years.

The calculations work with this data row by row in a given column at a given set point in three different segments hence the three For loops. I suspect I am bringing in the range of data incorrectly.

Originally Posted by shg

Probably not relevant to the problem you're having, but in this declaration,

Please Login or Register  to view this content.

... all but cfuts are implicitly defined as Variants.

Edit: I don't think anyone can help without having the workbook containing the data ...

It didn't change anything, but thanks for the tip.

This is as far as I can go without more information:

Please Login or Register  to view this content.

You should get in the habit of putting Option Explicit at the top of all modules. With seg3yrs undefined, it doesn't compile.

Edit: You should also include morttab as an argument to the function; otherwise, changes to the table will not cause recalculation of the function.

Originally Posted by shg

This is as far as I can go without more information:

You should get in the habit of putting Option Explicit at the top of all modules. With seg3yrs undefined, it doesn't compile.

Edit: You should also include morttab as an argument to the function; otherwise, changes to the table will not cause recalculation of the function.

I recall "Option Explicit" from my days of learning C++ 4-7 years ago. How would I include morttab as a parameter? That is your suggestion, correct?

Application.Goto Reference:="morttab"
lvalues = Range("morttab").Value

I tried this method once before with no luck, and this time it resulted in an error message saying it cannot be assigned to an array.

How would I include morttab as a parameter?

Add it to the list of arguments in the signature (the Sub declaration), and amend the spreadsheet forulas accordingly.

I tried this method once before with no luck, and this time it resulted in an error message saying it cannot be assigned to an array.

It's not declared as an array in the code I posted.

Originally Posted by shg

Add it to the list of arguments in the signature (the Sub declaration), and amend the spreadsheet forulas accordingly.

It's not declared as an array in the code I posted.

Hmmm, still no luck...

Please Login or Register  to view this content.

Here is the message I found in regards to this function when Error Checking...

"A value used in this formula is of the wrong data type."

My inputs as follows are:

Please Login or Register  to view this content.

Is anyone else available? I guess the only other piece of information I can give is the data range that I am reading in, but it is just a block of numbers.

I did not see a rule against bumping a thread, so here it goes....

Shg, if you are reading this, do you have any other feedback or comments?

Thanks,
Colby

There's no rule at all against bumping your threads.

Did you write the original function?

Originally Posted by shg

There's no rule at all against bumping your threads.

Did you write the original function?

Yeah, I wrote the original function and all consequent changes.

Here is my latest code:

Please Login or Register  to view this content.

I commented out the end result for MV. I want to see if I can just get a value returned. I still receive an error. This tells me that the table is not being read in to the function correctly...

I then changed the end result for MV to simply be equal to 1. I still get a #VALUE! error. I am very puzzled.

Originally Posted by shg

Edit: I don't think anyone can help without having the workbook containing the data ...

Can you attach a small sample workbook?

Originally Posted by StephenR

Can you attach a small sample workbook?

Per your request, attached is a sample Excel file with the data table, parameters, function in a cell, and the VBA code for the function. It is an 07 version of Excel. I tried uploading an 03 version as well, but it exceeds the kilobyte limit.

I haven't got 07. Did you try zipping it?

Originally Posted by StephenR

I haven't got 07. Did you try zipping it?

Good call...

bumping as this was moved off of the front page without anyone viewing the 03 or 07 versions of the sample file..

Thanks a lot for the technical support so far!

It's nearly time for me to climb the wooden steps to Bedfordshire, but if nobody else has picked up in the meantime I will look at tomorrow (no guarantees that I'll solve it though!).

It's pretty tough-going because it's hard to get my head round your function. But a few thoughts:

Please Login or Register  to view this content.

In your example, limit1=0 so I guess this does nothing as there is nothing between the 'Then' and the 'Else'.

Please Login or Register  to view this content.

In both these loops, you're successively assigning different values to p and sp as you go through the For-Next loop but not storing them so only the last value will be stored. In other words there doesn't appear to be any point to these loops unless you mean to store all the p/sp values.

Originally Posted by StephenR

It's pretty tough-going because it's hard to get my head round your function. But a few thoughts:

Please Login or Register  to view this content.

In your example, limit1=0 so I guess this does nothing as there is nothing between the 'Then' and the 'Else'.

Well, I want nothing to happen if one of the limits is zero. Basically, that means that the given interest rate in that segment is not valued.

Originally Posted by StephenR

Please Login or Register  to view this content.

In both these loops, you're successively assigning different values to p and sp as you go through the For-Next loop but not storing them so only the last value will be stored. In other words there doesn't appear to be any point to these loops unless you mean to store all the p/sp values.

Good call, I think I need the following in the If statement:

Please Login or Register  to view this content.

I will try this small change out to see if anything happens, but the problem remains the same. If I set MV = 1 at the end of the function, then shouldn't the result in the cell be equal to 1? If not, then it means there is some error in code. Whether it is calculating it correctly or not is not an error, so the only problem I foresee is the data is being read incorrectly.

Thanks for the input!

-COlby

Yep; that didn't rid my function of its erroneous result.

Spotted a couple of other little things. Try changing lvalues to Variant:

Please Login or Register  to view this content.

In the line below a dot was missing before Max:

Please Login or Register  to view this content.

I turned your code into a standard macro and for your example it produced a value, 85.2710023814286. Is this what you expected?!

Originally Posted by StephenR

Spotted a couple of other little things. Try changing lvalues to Variant:

Please Login or Register  to view this content.

In the line below a dot was missing before Max:

Please Login or Register  to view this content.

I turned your code into a standard macro and for your example it produced a value, 85.2710023814286. Is this what you expected?!

I will check the number in a second, but that .Max and Variant seems to have fixed my problems. There was no error with how the data was being read in (besides the variant/range assignment to lvalues). Thanks!

Originally Posted by crs245

I will check the number in a second, but that .Max and Variant seems to have fixed my problems. There was no error with how the data was being read in (besides the variant/range assignment to lvalues). Thanks!

I'll be back to comment later, but it seems that you hit the nail on the head. Compared to another calculation, I have a 6.5% error. I believe I can expect a 5% error when going from annual estimations of annuities to the actual monthly annuities. The calculations are what I can do best. Thanks a lot!

I am not sure what happened, but the function is not working anymore. I haven't looked at it in a month due to other work, but it could be something small:

Please Login or Register  to view this content.

Never mind, it was that dot before the Max again! It seems that I didn't save my last work...