423,109 Members | 2,116 Online
Need help? Post your question and get tips & solutions from a community of 423,109 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
17 Replies

 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.

Attached Images
 InterpolationTable.jpg (29.1 KB, 468 views)
Mar 8 '12 #4

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

 Expert Mod 10K+ P: 12,236 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

 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

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

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
 How to interpolate.doc (63.5 KB, 315 views) How to interpolate.pdf (40.1 KB, 382 views)
Mar 10 '12 #9

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
 Table interpolation_v1.4.xls (31.5 KB, 473 views) How to interpolate_2.doc (70.0 KB, 351 views) How to interpolate_2.pdf (52.5 KB, 352 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

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
 Table interpolation_v1.1.zip (7.7 KB, 153 views)
Mar 13 '12 #14

 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 Private Sub CmdCalc_Click() '§ coordinates SPEED Dim Sy1 As Double Dim Sy2 As Double '§ coordinates bottom speed Dim m_SLOPE As Double '§ m is the slope Dim b_Y_INTERSECT As Double '§ b is the y-intercept    If TextMassB1 = "" Then       MsgBox ("Enter a value for Mass Bottom 1")       Exit Sub    End If    If TextMassB2 = "" Then       MsgBox ("Enter a value for Mass Bottom 2")       Exit Sub    End If    If TextMassT1 = "" Then       MsgBox ("Enter a value for Mass Top 1")       Exit Sub    End If    If TextMassT2 = "" Then       MsgBox ("Enter a value for Mass Top 2")       Exit Sub    End If    If TextTempTB1 = "" Then       MsgBox ("Enter a value for Temp Top Bottom 1")       Exit Sub    End If    If TextTempTB2 = "" Then       MsgBox ("Enter a value for Temp Top Bottom 2")       Exit Sub    End If    If TextSpeedT = "" Then       MsgBox ("Enter a value for Speed Top")       Exit Sub    End If    If TextSpeedB = "" Then       MsgBox ("Enter a value for Speed Bottom")       Exit Sub    End If    If TextTempCalc = "" Then       MsgBox ("Enter a value for Temp Calc")       Exit Sub    End If    If TextSpeedCalc = "" Then       MsgBox ("Enter a value for Speed Calc")       Exit Sub    End If    Sy1 = (((TextSpeedCalc.Text - TextSpeedB.Text) / _          (TextSpeedT.Text - TextSpeedB.Text)) * _          (TextMassT1.Text - TextMassB1.Text)) + TextMassB1.Text    Sy2 = (((TextSpeedCalc.Text - TextSpeedB.Text) / _          (TextSpeedT.Text - TextSpeedB.Text)) * _          (TextMassT2.Text - TextMassB2.Text)) + TextMassB2.Text    m_SLOPE = (Sy2 - Sy1) / (TextTempTB2.Text - TextTempTB1.Text)    b_Y_INTERSECT = Sy1 - (m_SLOPE * TextTempTB1)    TextMassCalc.Text = (m_SLOPE * TextTempCalc.Text) + b_Y_INTERSECT End Sub Do you want to write the project in "VBA.net in Excel" or in "Visual studio VB.net" ? Mar 14 '12 #15

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
 Table interpolation.zip (553.3 KB, 171 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

 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