How to create a spreadsheet

[mwingeier]

I am a longtime programmer of BASIC, but relatively new to VB, and I am creating a program for my company that requires a spreadsheet-like screen full of sales data. What I am having trouble doing is updating dependent textboxes and such based upon the values entered in other textboxes, like an autocalculate feature of any major spreadsheet program. I have tried tying everything to focus events, but that just makes the main logic a nightmare to create since any control not having a proper value will generate an error. Does anyone have a simple way to create an auto-calculating spreadsheet? Thanks.

I'm not sure if this works, but place Option Explicit in to (General) (Declarations). It seem not to stop on errors (atleast for me), but you need everything dimmed, like Dim A As Integer, if you have a for loop using A. You find more help at VB help on Option Explicit.

Hope this helps,

[Shrog]

Firstly, Option Explicit simply means that you have to declare all variables you use. If you do not use Option explicit, you can just use any variable without declaring it as in:

  Prompt = "Enter your name"
  Print Promt

VB will create a variable called Prompt as a variant, and place the text in it. This is not a good way of working, because if you make a typing error, you will not know, as in the second line where I left out the "p". VB will create a second variant called Promt and print it. With Option Explicit you have to say:

  Dim Prompt As String

  Prompt = "Enter your name"
  Print Promt 'this will give and error because of the typo

But that aside - back to the spreadsheet. You have several option here, and none of them are totally simple, but they are very much simpler that the way you were using up till now.

You can add an OLE control to your form, and then use an Excel spreadsheet, or any other object in the control. You can programatically control the embedded spreadsheet. (BTW, OLE stands for "Object Linking & Embedding" and that's exactly what this is.)

Another option is to use one of the many grid controls available. For example the FlexGrid control. This control has the look and feel of a spreadsheet, but does not have the builtin functionality that Excel does. It's easy to create your own though.

I often use the ListView control to quickly display simple lists and results.

There are many other controls that you can use. It is not always easy to determine which of the options to use, because it depends very much on what exactly you want to do.

Shrog

[mwingeier]

Thanks for the input thus far, but maybe some clarification is in order on what it is I am doing. What I have set up is numerous fields for material cost, markup, handling, etc, and there are other fields that calculate and store results. Not exactly a whole spreadsheet, nor is it static. I actually did design an Excel spreadsheet to do this, but I want to make it all self-contained in this one program if possible.

[Shrog]

If you've already created the spreadsheet in Excel, why not use it in an OLE control on your form? You have access to everything in the Excel application, and it's all contained within your program. You can pass data from VB controls to the spreadsheet and vice versa. It's like an Excel Control.

Shrog

[mwingeier]

I could import my Excel spreadsheet, that's true. I guess I'd just rather not if I can get away with it, because I would not like to have any externally readable data files for the program. With the users I am dealing with, someone's bound to find the Excel file and screw with it.