422,024 Members | 1,091 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 422,024 IT Pros & Developers. It's quick & easy.

Table interpolation..

P: 6
Hello all,

I'm very new to visual basic and would like to have your help.

I have a two dimensional table with the values representing the mass. Each row representing a speed and each column represents a temperature. So that for a particular speed and temperature, you have a unique mass value.

The speed and temperature values are entered by the user. The code should give me the mass value for that particular speed and temperature. The code should give a interpolated value in case the speed or the temperature entered by the user is not found on the table.

How to implement this using visual basic? I just have the values. I need to create a table or array variables which represents the table mentioned above. Then the interpolation based on user input (speed and temperature).

Any directions on this would be really appreciated.

Thanks in advance.
Mar 5 '12 #1
Share this Question
Share on Google+
17 Replies


Guido Geurs
Expert 100+
P: 767
Please, is it possible to attach in Bytes a demo of the sheet with the values and an example of the end results?
If the sheet is to big, only a few values as example are enough.
Mar 7 '12 #2

P: 6
Hi Guido,

I have the sample table in excel file but not sure how to attach the file to this post. Please help me.

Thanks..
Mar 8 '12 #3

P: 6
@Guido Geurs
Hi Guido,

I might not have been clear in my earlier post. I have attached an image of the table.

The sample table represents the mass values for various speed and temperature.

For example, 40 is the mass for temperature 285 K and 1600 rpm

What if i need to find the mass for a temperature of 277 K at 1500 rpm?

Also, i need to know how to extrapolate the data.

What if i need to find the mass for a temperature of 265 K at 1500 rpm?

I would like to know how to implement the entire idea in visual basic, like implementing the table structure, interpolation and so on.

Hope my problem is clear now. Please guide me in this.

Thanks in advance.
Attached Images
File Type: jpg InterpolationTable.jpg (29.1 KB, 442 views)
Mar 8 '12 #4

Guido Geurs
Expert 100+
P: 767
attached is a zippd file with DOC and PDF file explaining how to attach files in Bytes.
Attached Files
File Type: zip Bytes_manual_v3.zip (241.2 KB, 202 views)
Mar 8 '12 #5

Rabbit
Expert Mod 10K+
P: 12,170
Your chart looks wrong, should 1200 @ 270 = 26? It seems to break the pattern of the other values. Besides, you haven't specified what kind of interpolation you want to do, there are different types.
Mar 8 '12 #6

Guido Geurs
Expert 100+
P: 767
Do you want the results in a sheet with multible temps and speeds?
Or do you want to use a form on which the user enter the temp and speed in two textboxes and the interpolated mass is in a third textbox?
Mar 8 '12 #7

Guido Geurs
Expert 100+
P: 767
The function is linear.
Only the transposition is 5 for the first line (other = 6).
See attachment
Attached Files
File Type: xls Table interpolation_v1.0.xls (14.5 KB, 427 views)
Mar 8 '12 #8

Guido Geurs
Expert 100+
P: 767
How to interpolate:
1 - find the function for the line of the speed = interpolation between 2 speeds.
2 - mass = intersection between temp and found function for speed.
For more details: see attachment.
Attached Files
File Type: doc How to interpolate.doc (63.5 KB, 298 views)
File Type: pdf How to interpolate.pdf (40.1 KB, 351 views)
Mar 10 '12 #9

Guido Geurs
Expert 100+
P: 767
This is the code to use with an userform (see attachments)

Expand|Select|Wrap|Line Numbers
  1. Private Sub CommandButton1_Click()
  2. Dim TOPspeed As Double
  3. Dim BOTTOMspeed As Double
  4. ' coordinates BOTTOM and TOP speed
  5. Dim TBSx1 As Double
  6. Dim TBSx2 As Double
  7. Dim By1 As Double
  8. Dim By2 As Double
  9. Dim Ty1 As Double
  10. Dim Ty2 As Double
  11. ' coordinates SPEED
  12. Dim Sy1 As Double
  13. Dim Sy2 As Double
  14. ' coordinates bottom speed
  15. Dim m_SLOPE As Double ' m is the slope
  16. Dim b_Y_INTERSECT As Double ' b is the y-intercept
  17.     Range("B3").Activate
  18.     ' find first value > speed
  19.     Do Until ActiveCell.Value = ""
  20.         If ActiveCell.Value > Val(TextBoxSpeed.Text) Then
  21.             TOPspeed = ActiveCell.Value
  22.             BOTTOMspeed = ActiveCell.Offset(-1, 0).Value
  23.             TBSx1 = Range("C2").Value
  24.             TBSx2 = Range("D2").Value
  25.             By1 = ActiveCell.Offset(-1, 1).Value
  26.             By2 = ActiveCell.Offset(-1, 2).Value
  27.             Ty1 = ActiveCell.Offset(0, 1).Value
  28.             Ty2 = ActiveCell.Offset(0, 2).Value
  29.             Sy1 = (((Val(TextBoxSpeed.Text) - BOTTOMspeed) / (TOPspeed - BOTTOMspeed)) * (Ty1 - By1)) + By1
  30.             Sy2 = (((Val(TextBoxSpeed.Text) - BOTTOMspeed) / (TOPspeed - BOTTOMspeed)) * (Ty2 - By2)) + By2
  31.             m_SLOPE = (Sy2 - Sy1) / (TBSx2 - TBSx1)
  32.             b_Y_INTERSECT = Sy1 - (m_SLOPE * TBSx1)
  33.             TextBoxMass = (m_SLOPE * TextBoxTemp) + b_Y_INTERSECT
  34.             Exit Sub
  35.         End If
  36.         ActiveCell.Offset(1, 0).Activate
  37.     Loop
  38. End Sub
Attached Files
File Type: xls Table interpolation_v1.4.xls (31.5 KB, 437 views)
File Type: doc How to interpolate_2.doc (70.0 KB, 322 views)
File Type: pdf How to interpolate_2.pdf (52.5 KB, 329 views)
Mar 10 '12 #10

P: 6
Thanks for all your replies.

@ Rabbit, The table was framed with my own sample values and does not replicate the actual data. I was at the moment looking for linear interpolation only.


@ Guido - It was an excellent effort from you. I'm mesmerised indeed. Thanks a million.

I have few more questions on this.
1. Does it work well with extrapolation also? for example,the mass value @ 265 temp and 980 speed
2. I want the table to fit in the code itself. The user just specifies the temperature and speed value. How do i formulate the table in the code (using Array variables?..)?

Once again many thanks in advance.
Mar 13 '12 #11

P: 6
@Guido,
To make it clear..

I am looking for a subroutine\function which takes temperature and speed as arguments and returns me the mass value (interpolated or extrapolated value).

Sorry for troubling you.

Thanks a lot.
Mar 13 '12 #12

P: 6
@Guido,
The code doesn't work with the last speed value (i.e, 2000)...
Mar 13 '12 #13

Guido Geurs
Expert 100+
P: 767
It's normal because the code has no error detections!
It search for the speed bigger then 2000 and there is not one! so it calculates on an error.
It needs more fine tuning!
This code was so complex because the values for speed where not linear ! (error in the valus ??)
If it's all linear than the code is even simple : only need 8 values and no search for bigger speed.

But this was only the first step in solving the problem.

If all the functions are linear than the solution is even simple.
It only needs 8 values to enter to calculate all the values (interpolated or extrapolated ).
From the 8 given values we can calculate the Slope and the y-intersect for a speed.
Once we have the function we can calculate the mass for any temp.

Attached is an demo in VB6.
I have pre-entered the first values for speed 1000 and 1200 but it can be any value for any speed.
Enter them in the green and bleu textboxes and the speed and temp to calculate in the red textboxes.
Click on "Calc" to calculate the Mass (=red).

Do you want the solution in Excel or in VB6 ?
Attached Files
File Type: zip Table interpolation_v1.1.zip (7.7 KB, 140 views)
Mar 13 '12 #14

Guido Geurs
Expert 100+
P: 767
In the ZIP is a "Table interpolation_v1.1.exe" file which you can run to see the program I have written in VB6.
You can use this program for any mass, temp and speed !

No need to enter the data IN the program (function) because once the data changes, you have to rewrite the program or function code !

This is the code:
the first IF's are only to check if all needed data is entered.
The real calculation is in the 5 last lines.

Expand|Select|Wrap|Line Numbers
  1. Private Sub CmdCalc_Click()
  2. ' coordinates SPEED
  3. Dim Sy1 As Double
  4. Dim Sy2 As Double
  5. ' coordinates bottom speed
  6. Dim m_SLOPE As Double ' m is the slope
  7. Dim b_Y_INTERSECT As Double ' b is the y-intercept
  8.    If TextMassB1 = "" Then
  9.       MsgBox ("Enter a value for Mass Bottom 1")
  10.       Exit Sub
  11.    End If
  12.    If TextMassB2 = "" Then
  13.       MsgBox ("Enter a value for Mass Bottom 2")
  14.       Exit Sub
  15.    End If
  16.    If TextMassT1 = "" Then
  17.       MsgBox ("Enter a value for Mass Top 1")
  18.       Exit Sub
  19.    End If
  20.    If TextMassT2 = "" Then
  21.       MsgBox ("Enter a value for Mass Top 2")
  22.       Exit Sub
  23.    End If
  24.    If TextTempTB1 = "" Then
  25.       MsgBox ("Enter a value for Temp Top Bottom 1")
  26.       Exit Sub
  27.    End If
  28.    If TextTempTB2 = "" Then
  29.       MsgBox ("Enter a value for Temp Top Bottom 2")
  30.       Exit Sub
  31.    End If
  32.    If TextSpeedT = "" Then
  33.       MsgBox ("Enter a value for Speed Top")
  34.       Exit Sub
  35.    End If
  36.    If TextSpeedB = "" Then
  37.       MsgBox ("Enter a value for Speed Bottom")
  38.       Exit Sub
  39.    End If
  40.    If TextTempCalc = "" Then
  41.       MsgBox ("Enter a value for Temp Calc")
  42.       Exit Sub
  43.    End If
  44.    If TextSpeedCalc = "" Then
  45.       MsgBox ("Enter a value for Speed Calc")
  46.       Exit Sub
  47.    End If
  48.    Sy1 = (((TextSpeedCalc.Text - TextSpeedB.Text) / _
  49.          (TextSpeedT.Text - TextSpeedB.Text)) * _
  50.          (TextMassT1.Text - TextMassB1.Text)) + TextMassB1.Text
  51.    Sy2 = (((TextSpeedCalc.Text - TextSpeedB.Text) / _
  52.          (TextSpeedT.Text - TextSpeedB.Text)) * _
  53.          (TextMassT2.Text - TextMassB2.Text)) + TextMassB2.Text
  54.    m_SLOPE = (Sy2 - Sy1) / (TextTempTB2.Text - TextTempTB1.Text)
  55.    b_Y_INTERSECT = Sy1 - (m_SLOPE * TextTempTB1)
  56.    TextMassCalc.Text = (m_SLOPE * TextTempCalc.Text) + b_Y_INTERSECT
  57. End Sub
Do you want to write the project in "VBA.net in Excel" or in "Visual studio VB.net" ?
Mar 14 '12 #15

Guido Geurs
Expert 100+
P: 767
Attached is a demo in VB.net 2010.

I have make it possible to save and load the data because my personal experience is that putting the data in the program not the optimal solution is.
When each time some of the values changes, you have to rebuild the program and distribute it!

With this solution it's possible to save the values for each different situation (product, item, ...) and load it in the same program.

the files have the extension *.TSM_data and are just a textfile with the data of the 8 values necessary to calculate any mass for that situation.
Attached Files
File Type: zip Table interpolation.zip (553.3 KB, 165 views)
Mar 17 '12 #16

P: 1
Hi Mr. Guido Geurs, I just saw your VB files. I am a civil engineer and i need a VB macro file for an excel sheet that is being used to design RCC Column members of buildings. I would like to know whether is that possible for me to learn Visual Basic so that I could prepare Macro files by myself. I am not a Computer Programmer and should honestly say I am zero in it. But I am aware of PC Hardwares and softwares, since I use it daily. I need a macro file to be created for an interpolation similar to the above case.S, could you tell me whether is that possible for me to learn that? Or should I learn C & C++ in first ?? Thank you
Jan 6 '18 #17

Guido Geurs
Expert 100+
P: 767
Dear Areesh Kumar S

If you are new on the topic of programming then it is good to start with learning writing Algorithms (just google for "learning algorithms".
This is a method to see with blocks how a problem comes to a solution in a computer language .
Mostly with questions and statements like "IF .... then... else.....
Second is the language itself.
Then you have to learn the syntax of the code (each language has his own methode of writing the code) and the properties if each element in the language.
Now you have to chose a language for your problem.
If you want to write macros in Excel you have to learn VBA (visual basic for Applications).
If you want to write small programs for using on a PC you have to learn VB6 (visual basic from Microsoft visual Studio) or VB.net (=later generation of VB6 with other syntaxes) .
I'm still working on Win7 and VB6 does still the work for me.
If you want to write for a webpage you have to learn HTML (totally different syntax than VB6)
...aso.

If you have a problem with a macro in Excel or VB6 I can always help you to solve it with an explanation how i have written the code.
Just open a new topic (question) in Bytes with a little demo attached.
Also send me a mail "guido.geurs@gmail.com" with the title of the question so i can trace it in Bytes.
Jan 7 '18 #18

Post your reply

Sign in to post your reply or Sign up for a free account.