Contact US

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

How to use the Visual Basic in Excel 97 for technical calculations.

How to use the Visual Basic in Excel 97 for technical calculations.

How to use the Visual Basic in Excel 97 for technical calculations.

If you don't know how to do any of the following steps, we'll talk about it on the Forum.

1) In Excel, give names to all of your input and output
2) Make the "Control Toolbox" toolbar visible (View,
3) Click on the "Button button" (fourth down, first column)
    and draw one somewhere on the worksheet just as you
    would draw any graphic.
4) RIGHT click on the new button and choose "View Code"
5) A whole new kind of screen shows up (To return to Excel,
     press click the Excel button on the upper lefthand
     corner of the screen.)
6) Your program will be between the lines that say
      "Private Sub CommandButton1_Click()" and "End Sub"
7) Assume you named an input cell on your
    worksheet "Height" and another one "Radius." If you
    type H = [Height] and R =[Radius] as your first two
    lines in you program then you can write a program just
    as you would in Fortran or Basic. For instance
        V = H * R * R * 3.14159
8) Assume you named a cell "Volume" on your worksheet. Then
    to print the results above to the worksheet, add the
    line [Volume] = V to your program.
9) Return to the worksheet (by pressing the Excel button on
   the upper lefthand corner)
10) MOST IMPORTANT Get out of the "design mode" by pressing
     the button with a blue triangle on it that is located
     at the top of the Control Toolbox toolbar.
11) When you click on your new button, it should take the
     input from the named cells on the worksheet and output
     the results to the named cells on the worksheet

RE: How to use the Visual Basic in Excel 97 for technical calculations.

The naming of Excel columns and using those names as range names is a great gimmick, not known to many who would just love it.


RE: How to use the Visual Basic in Excel 97 for technical calculations.

How cam I realise forms with strange shapes.For examples how can I do a circular form.

RE: How to use the Visual Basic in Excel 97 for technical calculations.

There are a couple of pitfalls with the methods proposed above:

1. The calculated volume is not dynamically updated. You would have to hit the calculation button each time you made a change to one of the input values. In most instances, if an input is changed, you would want the result to be updated automatically.

2. This calculation is only available on one sheet and only handles the single set of named inputs. Creating user-defined functions will allow calculations to be accessed from any sheet in a workbook. You can also export a global module that contains all of your engineering calculations. Then, just import that module into any workbook and have all of the functions available for use.

Expanding on the above example:
1. Go to the Visual Basic Editor.
2. Select Insert > Module.

if you are going to export the module:
  select View > Properties Window
  rename Module1 to modFunctions, or something of the sort
  right-click the module in the Project window
  Select Export > File
  Save the module

In the module, insert this code:

Option Explicit

Public Const PI = 3.14159

Public Function CylinderVolume(rRad As Double, rHgt As Double) As Double
    CylinderVolume = PI * rRad ^ 2 * rHgt
End Function

Now, the value for PI will not have to be redefined since it is declared as a Public variable in a global module. In addition, the CylinderVolume calculation can be used in any worksheet and can be used multiple times, independant of your naming convention.

Let's say cell D1 contains the Radius, D2 contains the Height and you want the results in cell D3:

Insert one of these formulas into cell D3

Without Naming:

With Naming:

Now, anytime that you change the Radius or Height values, the calulated Volume is automatically updated. This gives you more flexiblity in your engineering spreadsheets and lends itself to reuse.

RE: How to use the Visual Basic in Excel 97 for technical calculations.


I personally prefer the button method.  Functions work great if the answer is only one number.  I've tried making functions that return arrays, but I find them to be awkward and I don't usually want all of the answers to be output in a column.

Also, for the long engineering calculations I work with, it is good that the calculations don't dynamic updating after each new number input.  If you do want auto calculation, however, you can right click on the worksheet tab and select "View Code". Delete any partial code segments and then write the program like this:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    D = [Diameter] : H = [Height]
    V = 3.14159*D*D*H
    [Volumn] = V
End Sub

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members! Already a Member? Login


Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close