Table interpolation..

[harish85]

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.

[Guido Geurs]

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.

[harish85]

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..

[harish85]

@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.

[Guido Geurs]

attached is a zippd file with DOC and PDF file explaining how to attach files in Bytes.

[Rabbit]

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.

[Guido Geurs]

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?

[Guido Geurs]

The function is linear.
Only the transposition is 5 for the first line (other = 6).
See attachment

[Guido Geurs]

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.

[Guido Geurs]

This is the code to use with an userform (see attachments)

Private Sub CommandButton1_Click()
Dim TOPspeed As Double
Dim BOTTOMspeed As Double
'§ coordinates BOTTOM and TOP speed
Dim TBSx1 As Double
Dim TBSx2 As Double
Dim By1 As Double
Dim By2 As Double
Dim Ty1 As Double
Dim Ty2 As Double
'§ 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
    Range("B3").Activate
    '§ find first value > speed
    Do Until ActiveCell.Value = ""
        If ActiveCell.Value > Val(TextBoxSpeed.Text) Then
            TOPspeed = ActiveCell.Value
            BOTTOMspeed = ActiveCell.Offset(-1, 0).Value
            TBSx1 = Range("C2").Value
            TBSx2 = Range("D2").Value
            By1 = ActiveCell.Offset(-1, 1).Value
            By2 = ActiveCell.Offset(-1, 2).Value
            Ty1 = ActiveCell.Offset(0, 1).Value
            Ty2 = ActiveCell.Offset(0, 2).Value
            Sy1 = (((Val(TextBoxSpeed.Text) - BOTTOMspeed) / (TOPspeed - BOTTOMspeed)) * (Ty1 - By1)) + By1
            Sy2 = (((Val(TextBoxSpeed.Text) - BOTTOMspeed) / (TOPspeed - BOTTOMspeed)) * (Ty2 - By2)) + By2
            m_SLOPE = (Sy2 - Sy1) / (TBSx2 - TBSx1)
            b_Y_INTERSECT = Sy1 - (m_SLOPE * TBSx1)
            TextBoxMass = (m_SLOPE * TextBoxTemp) + b_Y_INTERSECT
            Exit Sub
        End If
        ActiveCell.Offset(1, 0).Activate
    Loop
End Sub

[harish85]

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.

[harish85]

@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.

[harish85]

@Guido,
The code doesn't work with the last speed value (i.e, 2000)...

[Guido Geurs]

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 ?

[Guido Geurs]

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.

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" ?

[Guido Geurs]

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.

[Areesh Kumar S]

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

[Guido Geurs]

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 "[e-mail]" with the title of the question so i can trace it in Bytes.