PDA

View Full Version : Sleeper: Highlighting Cell in Variant



karrotman
07-24-2012, 11:31 AM
Hi guys,

So I've been working on a simple linear interpolation code. Basically it takes a matrix of timedata and interpolates from the lowest time to the highest. Everything runs smoothly. What I would like is for my VBA code to spit out a matrix with the original values HIGHLIGHTED.

Keep in mind what my code does:

It accepts the input data
It creates a variant called MAT and fills it with dates and Xs for data
It interpolates and changes the Xs for the interpolated values
It sets the function (LinearInterpolation() ) to MATNow I would just use:CellName.Interior.ColorIndex = 4, but I know that will not work because at that point I am working on the virtual matrix MAT rather then the actual workbook. Does anybody have a solution for this? I know I can scan through the cells, but I want to keep this as dynamic as possible. In other words I want the function to do this without knowing where the interpolation is taking place.


This is what I have:
! https://www.quantnet.com/attachments/linearinterp-png.4161/


...and this is what I want:
https://www.quantnet.com/attachments/linearinterpwant-png.4162/

Thanks!

CatDaddy
07-24-2012, 12:11 PM
post the rest of your code

karrotman
07-24-2012, 12:27 PM
Ok, you asked for it...

Function LinearInterpolation(TimeData As Range, m As Integer, T As Integer) As Variant
'PREPARING VARIABLES
Dim TIME1, TIME2, DATA1, DATA2, TIMEX, NewValue As Double
TIME1 = 0
TIME2 = 0
DATA1 = 0
DATA2 = 0
NewValue = 0
Dim NODES As Integer
NODES = T * m + 1
Dim i, j, z As Integer
'THE RESULTING MATRIX
Dim Mat As Variant
ReDim Mat(1 To NODES, 1 To 2)
'FINDS THE LOWEST STARTING TIME
z = TimeData(1, 1)
For i = 1 To Application.WorksheetFunction.Count(TimeData) / 2 'THIS RETURNS HOW MANY TIME VALUES WERE ORIGINALLY GIVEN
If TimeData(i, 1) < z Then z = TimeData(i, 1)
Next i
Mat(1, 1) = z
'FILLS IN THE FIRST DATA VALUE
For i = 1 To NODES
If Mat(1, 1) = TimeData(i, 1) Then Mat(1, 2) = TimeData(i, 2)
Next i
'NOW THE ACTUAL PROGRAM BEGINS
'FILLS ALL OF THE DATA IN MAT WITH XS FOR LATER USE
For j = 2 To NODES - 1
Mat(j, 2) = "X"
Next j
'POPULATES THE DATES SECTION OF MAT BY INCREASING EACH ROW BY 1/M
For i = 2 To NODES
Mat(i, 1) = Mat(i - 1, 1) + (1 / m)
Next i
'CHECKS FOR SIMILARITIES IN DATES AND FILLS IN DATA USING THE GIVEN VALUES
For j = 2 To NODES
For i = 1 To NODES - 1
If TimeData(i, 1) = Mat(j, 1) Then Mat(j, 2) = TimeData(i, 2)
Next i
Next j
Dim CHECK, ABORT As Integer
CHECK = 0
j = 1
'DONE PREPARATION
'INITIATION OF FILLING A SINGLE X CELL WITH INTERPOLATED VALUE
Do While CHECK = 0
ABORT = 0
j = 1
i = j + 1
Do While ABORT <> 1
Do While i < NODES + 1
If Mat(j, 2) = "X" Then
ABORT = 1
Mat(j, 2) = Mat(i, 2)
TIME2 = Mat(i, 1) 'THE TIME IN THE FUTURE (ti+1) FOR INTERPOLATION
End If
i = i + 1
Loop
j = j + 1
i = j + 1
Loop
TIME1 = Mat(j - 2, 1) 'THE TIME IN THE PAST (ti-1) FOR INTERPOLATION
TIMEX = Mat(j - 1, 1) 'TIMEX IS THE TIME WE ARE GOING TO INTERPOLATE
DATA1 = Mat(j - 2, 2)
DATA2 = Mat(j - 1, 2)
'THE LINEAR INTERPOLATION FORMULA
NewValue = ((DATA2 - DATA1) / (TIME2 - TIME1)) * ((TIMEX) - TIME1) + DATA1
Mat(j - 1, 2) = NewValue
'SINGLE X CELL FILLED
'SCANS FOR MORE XS
CHECK = 1
For z = 1 To NODES
If Mat(z, 2) = "X" Then CHECK = 0
Next z
'THIS LOOP WILL CONTINUE AS LONG AS THERE ARE XS
Loop
'FINAL OUTPUT TO WORKSHEET
LinearInterpolation = Mat
End Function

karrotman
07-24-2012, 12:28 PM
Since I am a new poster I will have to post 5 posts before I can show you my actual output.

karrotman
07-24-2012, 12:29 PM
So here is 4th post

karrotman
07-24-2012, 12:30 PM
And here is 5th. Now see original post for what I want.

CatDaddy
07-24-2012, 01:11 PM
Im not sure which of those cells you are trying to highlight

karrotman
07-24-2012, 08:17 PM
I am trying to highlight the cells that are given in the inputs. For example, if one of the given times is 5 with a value of 2, then in the final matrix I want the time 5, data 2 cells highlighted.