Avoiding "Type Mismatch" in function using Range as argument

[dsillers]

As a newbie to VBA, I am trying to build a procedure that loops through a set of rows -- each containing numerical values for several country-specific parameters -- and for each row apply the Newton-Raphson method to find the value of "H" that solves an equation for that country. That method involves starting with a "guess" of H, computing two functions: Diff (the difference between the two sides of the equation with the current value of H) and Diff_prime (the slope of the equation at the current value of H), then computing a new value of H as Hnew = Hcurrent - Diff(Hcurrent)/Diff_prime(Hcurrent); and repeating until Diff is close enough to 0.

By liberally borrowing from others' examples, I cobbled together a sub-procedure that works, but it's ugly: I can can declare H as a Range and then pass the value of H to the functions Diff(H) and Diff_prime(H), one row at a time; however, the only way I've been able to get the other parameter values into the functions is in the form of Offsets relative to the cell containing H. As you can see from the code below, that makes the functions VERY hard to understand.

But whenever I try to replace one of those offsets with an argument that points directly at a parameter other than H, I get a Type Mismatch error.

Can anyone suggest a way to write such functions so they avoid this problem? Ideally, I'd like to be writing Diff as (something like)
Diff(H as Range, theta as Range, gamma as Range, delta as Range, mu as Range, Povline as Range) and then, for each row, solving H as a function of the values of theta, gamma, delta, etc in that row.

From looking at a Watch window, I can see that VBA understands those parameters as Ranges (period) rather than looking at the values of the parameters in the row containing the information for the country the function is working on.

Can anyone suggest a way to do this? Thanks a million!

--------------------

Please Login or Register  to view this content.

[Norie]

How exactly did you try passing more arguments than H to the function(s)?

Do the other ranges you try to pass consist of more than one cell unlike H which is only one cell?

If they did that would cause a type mismatch.

PS Can you add code tags?

[dsillers]

Thanks, Norie -- I've inserted code tags (as you can see, I'm not just a newbie at VBA!)

Actually, H as well as the other parameters theta, gamma, delta, mu, and povline are ALL ranges -- in columns with H on the right (Range $J$3:$J$58) and the rest arranged to the left of that column (for instance, the "theta"s are in Range($D$3:$D$58), so in function Diff(H) I can refer to the cell with the value of theta corresponding to the current H as H.Offset(0,-6), and so forth with the rest. But as noted, that makes a really ugly expression.

When I try to rewrite Diff as a function of H plus one or more of those other parameters -- as a first step, replacing Diff(H) with Diff(H as Range, theta as Range) and replacing H.Offset(0,-6) with theta inside the function, that's when the problems start -- I'm pretty sure that the problem is that I don't know how to tell VBA which cell in range "theta" it should be looking at to find the value corresponding to the current H.

Is that clearer?

[shg]

Have you considered using Solver instead?

[dsillers]

Thanks, shg, my first version used Solver, which certainly gets the job done but is arguably overkill for this one-variable equation. Also, I want to be able to share this application widely, without people having to install Solver if they haven't already.

But after some further search around the forum, I figured out how to solve my own problem -- which is to include a set of lines immediately after "For each H in Range(...)" that Set each parameter value using the Offset relative to H; that converts the into single values for each line of the calculation. Having done that, I could then re-write Diff and Diff_prime as functions of one Range argument plus multiple Single arguments. Works like a charm!

Many thanks for your suggestion!

BTW, the new code looks like this:

Please Login or Register  to view this content.

[shg]

For a single-variable equation, how about Goal Seek? No installation required.

Either way, good job getting it sorted.