The CellForeColor property is a property the developer uses to SET the foreground color of a cell and later READ it if required, but this does not mean that the assigned value will be used "as is" to draw the cell's text. First, its type is OLE_COLOR, and we can assign such special values as vbActiveTitleBar (&H80000002) or even CLR_NONE (-1) to use the grid's foreground color (what is stored in the iGrid.ForeColor property). Second, this value can be overwritten dynamically in the CellDynamicFormatting event. That's why this property returns not what you expected.
We are talking about the EFFECTIVE foreground color. I understand the nature of the problem, and to solve it, iGrid should provide you with a new read-only property that retrieves this effective value. Let's call this property 'CellEffectiveForeColor'. I've noted this suggestion for the future updates, but the question is how to solve the problem now using the existing object model.
I would not use any solution based on setting the CellForeColor property explicitly for all grid cells as it may work slowly if your grid will have thousands of rows. Dynamic formatting is the right tool to solve such a task without performance degrade. To avoid using a mirror grid, I would put the formatting algorithm from the current CellDynamicFormatting event handler into a function and use it in the printing routine too. The function could look like this:
Private Sub GetForeColorForValue(ByVal vValue As Variant, ByRef oForeColor As Long)
Dim iStart, iEnd As Integer
Dim lowLimit, highLimit As String
Dim strTemp1 As String
For i = 2 To 15
strTemp1 = iGrid2.CellValue(lRow, lCol)
'strTemp1 = "|65|90|"
iStart = InStr(1, strTemp1, "|") + 1
If iStart < 1 Then Exit Sub
iEnd = InStr(iStart, strTemp1, "|")
If iEnd < 1 Then Exit Sub
lowLimit = Mid(strTemp1, iStart, iEnd - iStart)
iStart = InStr(2, strTemp1, "|") + 1
If iStart < 1 Then Exit Sub
iEnd = InStr(iStart, strTemp1, "|")
If iEnd < 1 Then Exit Sub
highLimit = Mid(strTemp1, iStart, iEnd - iStart)
If vValue < Val(lowLimit) Then
oForeColor = vbBlue
End If
If vValue > Val(highLimit) Then
oForeColor = vbRed
End If
Next i
End Sub
And you would call it this way in iGrid1_CellDynamicFormatting:
Private Sub iGrid1_CellDynamicFormatting(ByVal lRow As Long, ByVal lCol As Long, ByVal vValue As Variant, oForeColor As Long, oBackColor As Long, oFont As Object)
GetForeColorForValue vValue, oForeColor
End Sub
And a similar way in the printing routine.
****************************
As for me, this color formatting algorithm looks enough strange. My questions regarding it are the following:
1) Why do we need this loop for i from 2 to 15? Do we really need it? Or did you show us only a part of the bigger real algorithm?
2) Why don't use the VB
Split function to parse your strings like "|65|90|"? It would simplify your code, and it would work faster.
3) And I if I understand the structure of your app properly, iGrid1 is used for the visual representation on the screen, but iGrid2 stores the limits, right? Are they stored as string values using the template "|<lowLimit>|<highLimit>|"? If so, can you parse them before your visual grid iGrid1 is displayed? This would increase the performance a lot as the parsing algorithm would not be executed to parse the same cell values again and again. And what about to store these values in a 2-dimensional array in memory? Or in 2 arrays, aLowLimits(,) and aHighLimits(,)? It would get the best performance.
Edited by user
2015-10-21T10:04:20Z
|
Reason: Igor/10Tec: Added new thoughts