For one of my classes there is a major project that requires many repeated calculations. It was suggested to me that Visual basic might meet my needs, but they don't know if it could really be used in my situation. I have only basic coding experience (Introduction to Java).

What I need VB to do:
Search Excel file for a specific value
Perform calculations based on that value.
If checks in calculations fail, go to next value and repeat.
Once a value passes all checks, VB code will spit out a results sheet into another excel sheet.

Is this doable with VB?

Thank you for your help on this matter.

3/6/2007 11:07:14 PM

what you really want is VBA

it's essentially a scripting language for excel

3/7/2007 12:14:14 AM

VB.NET integrates Microsoft Office programming pretty well, but the API can be freakish to get a good understanding of initially ... i.e. a high amount of initial learning time. However, this is probably overkill, and is going to suck if you need to port the project to anybody else, including the instructor. From the basic functionality you described, you could probably do it with a set of macros (with is based on VBA), which anybody could run.

[Edited on March 7, 2007 at 5:43 AM. Reason : .]

3/7/2007 5:42:17 AM

convert xls to csv/tsv textfile.
use any language.
depending on math, 20 lines or less of perl im betting.

3/7/2007 5:55:37 AM

^ Yep. If it were me, I'd use Perl. There are many perl modules that work with Excel and it could be done easily.

Is it a BUS or CSC class? I'm guessing a BUS prof would suggest VB.

But to answer your question, yes it's doable with VB. You can do things like what you're describing with many languages.

3/7/2007 8:13:06 AM

The guy has basic coding experience, perl is not a realistic suggestion, especially considering all the data is in excel to begin with. VBA is fine.

3/7/2007 8:54:51 AM

yeah, this is, no question, a problem for VBA. There's absolutely no reason to go outside of Excel for the solution, since both the input and the output are in Excel.

A lot of the code can be written for you using the Macro recorder.
For example, in Excel go to one of the values you want to calculate on. Then go to Tools > Macro > Record New Macro and hit Record, then perform the calculation you want. Then hit Stop, and you can go into the VBA editor (Tools > Macro > Visual Basic Editor) and see what the generated code looks like. You can do something similar to perform a find - start recording, do a "find", and see what the generated code looks like. Then you'll have to wrap the "find" code in a loop for your worksheet or range, then have it call the calculation code whenever a hit is made.

3/7/2007 9:11:14 AM