First of all, let's deal with the overflow problem. How are you declaring the variables? What data type? I mean, Double precision Real is -1.79769313486231E308 to -4.94065645841247E-324 Long Integer is -2,147,483,648 to 2,147,483,647
that seems pretty big!!! But, let's say you're still overrunning the variable. You can use: On Error Resume Next then, after the calculation, check for: if (Err.Number > 0) then Or, you can write an error handling code to modfy your values and restart. hope this helps. Paul On Nov 6, 7:40 pm, Steve J <dzia...@aol.com> wrote: > Hi, > > I'm new to the group and my first question is as follows: > > "Can I program an override to a VBA Overflow Error 6 and continue on > in my VBA code?" > > What I am trying to do is write a "Successive Bisection" routine in > VBA (and later a "Successive Inverse Interpolation" for the math guys > on this forum). I want to use a pretty extensive Excel Spreadsheet as > my "VBA slave" (approx 1200 rows and about a dozen columns). For a > single given value x determined > by the VBA code, the Excel will return a value y after going thru tons > of calcs. The goal is to find > a value of x which hits to the value of y (call it "y_solution") that > I want. > > Now, my routine works something like this. > > I make an educated guess at a low value x1 and high value x2. The > intent is establish 2 guesses that 1st straddle the solution. I want > y1 computed from x1 to be less than y_solution and y2 computed form x2 > to be greater (needless to say, if either hit to the solution > immediately I'd be happy...but that never happens!!) So, if the y1 > value for x1 is *greater* than y_solution, I guessed too high for x1. > BUT, from there, I'd just halve x1 and try again. Similarly, if the y2 > value for x2 is *less* than y_solution, I guessed too low for x2 so I > just double x2 and try again (well my program does, I'm not.quite that > persistent!!) > > Once I get a "good" x1 and x2, I know the x value that I want is > somewhere in between. So, my program takes the average of x1, x2 (to > get a "new" x) and tries that. If I now, get an answer that's less > than y_solution, I replace x1 with my "new" x...and try again... > Likewise if the answer is greater than y solution except I replace > x2. I keep doing this until I zero in on the answer. > > OK then. Here's the problem in VBA terms. Most of the time for my > starting X1 and X2 values, I can readily zero in on a "low" and a > "high". But sometimes the X1 value is so far off, it returns an > incredibly large negative y1 (or visa versa for x2) and aborts my > program. If I could progamatically detect the overflow error and > override the abort, I could simply double my x1 (or halve my x2) and > try again, thus "patching up" my routine. Once I'm in the non-overflow > realm, things are good to go. > > BTW, I should point out that my starting values for x1, x2 are formula > driven based on innumerable variables in the Excel Spreadsheet and > finding a a set of two formulas that always work without getting an > overflow seems impossible. > > Any is help appreciated. > > Thanks, > > Steve --~--~---------~--~----~------------~-------~--~----~ ---------------------------------------------------------------------------------- Some important links for excel users: 1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at http://www.excelitems.com 2. Excel tutorials at http://www.excel-macros.blogspot.com 3. Learn VBA Macros at http://www.vbamacros.blogspot.com 4. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com If you find any spam message in the group, please send an email to: Ayush Jain @ jainayus...@gmail.com or Ashish Jain @ 26may.1...@gmail.com <><><><><><><><><><><><><><><><><><><><><><> HELP US GROW !! We reach over 6,500 subscribers worldwide and receive many nice notes about the learning and support from the group. Our goal is to have 10,000 subscribers by the end of 2009. Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe -~----------~----~----~----~------~----~------~--~---
