S
Svenn Are Bjerkem
Hi,
I struggle with a function that I try to write in order to create a
visual indication of how good the quality of code, test, integration
and status of different parts of a project. I have been doing this by
hand so far, but would like to create a function that take the input
of one cell, say D2 and show a confidence bar in E2. The cells D1 and
E1 have been merged to create a header for both cells.
The code I have so far looks like this:
Public Function createBar(refCellVal As Integer)
Dim progress As String
Select Case refCellVal
Case 0
' Should be in red color
progress = "?"
Case 1
' Should be in yellow color
progress = "-"
Case 2
' Should be in yellow color
progress = "--"
Case 3
' Should be in green color
progress = "---"
Case Else
If refCellVal < 0 Then
' Should be in deep red color
progress = "!!!"
End If
If refCellVal > 3 Then
' should be in bright green color
progress = "^^^"
End If
End Select
createBar = progress
End Function
In the cell where I want the bar, E2, I place the formula
=createBar(D2) and I get my confidence level indicated as one or more
ascii characters. I only really care about levels 0 1 2 and 3, the
negative range is so killing that it has to be resolved ASAP and
everything above 3 is just too good.
I currently use the character I get when inserting unicode 2588 or
FULL BLOCK in Arial for levels 1, 2 and 3 and haven't really bothered
about the other cases so I have quite a few cells with manually made
confidence bars that I need to modify.
I realize that there must be something that I don't understand about
text and text properties, because I cannot copy and paste that FULL
BLOCK character from my excel spreadsheet and into the function in the
VBA editor. I am also not able to change the color of the text. I have
tried something like progress.colorIndex = 3, but got an error message
that I do not have a proper qualifier. I am getting to the point that
I think I cannot change the color property inside the function, but
have to do this some other way.
I initially used the REPT() function, which works nicely regarding the
number of FULL BLOCKS in a cell, and with some if testing could manage
to keep the number of blocks down to 3 even if I had a confidence
level of 10, but they were all black and that didn't quite solve my
case. Searching on google for text property in cells gave me a lot of
activecell and range, but I am afraid that I do not quite understand
how to use these objects to solve my problem.
Needless to say, I am quite new to programming macros/functions in
excel and hope for some advice.
Kind regards,
I struggle with a function that I try to write in order to create a
visual indication of how good the quality of code, test, integration
and status of different parts of a project. I have been doing this by
hand so far, but would like to create a function that take the input
of one cell, say D2 and show a confidence bar in E2. The cells D1 and
E1 have been merged to create a header for both cells.
The code I have so far looks like this:
Public Function createBar(refCellVal As Integer)
Dim progress As String
Select Case refCellVal
Case 0
' Should be in red color
progress = "?"
Case 1
' Should be in yellow color
progress = "-"
Case 2
' Should be in yellow color
progress = "--"
Case 3
' Should be in green color
progress = "---"
Case Else
If refCellVal < 0 Then
' Should be in deep red color
progress = "!!!"
End If
If refCellVal > 3 Then
' should be in bright green color
progress = "^^^"
End If
End Select
createBar = progress
End Function
In the cell where I want the bar, E2, I place the formula
=createBar(D2) and I get my confidence level indicated as one or more
ascii characters. I only really care about levels 0 1 2 and 3, the
negative range is so killing that it has to be resolved ASAP and
everything above 3 is just too good.
I currently use the character I get when inserting unicode 2588 or
FULL BLOCK in Arial for levels 1, 2 and 3 and haven't really bothered
about the other cases so I have quite a few cells with manually made
confidence bars that I need to modify.
I realize that there must be something that I don't understand about
text and text properties, because I cannot copy and paste that FULL
BLOCK character from my excel spreadsheet and into the function in the
VBA editor. I am also not able to change the color of the text. I have
tried something like progress.colorIndex = 3, but got an error message
that I do not have a proper qualifier. I am getting to the point that
I think I cannot change the color property inside the function, but
have to do this some other way.
I initially used the REPT() function, which works nicely regarding the
number of FULL BLOCKS in a cell, and with some if testing could manage
to keep the number of blocks down to 3 even if I had a confidence
level of 10, but they were all black and that didn't quite solve my
case. Searching on google for text property in cells gave me a lot of
activecell and range, but I am afraid that I do not quite understand
how to use these objects to solve my problem.
Needless to say, I am quite new to programming macros/functions in
excel and hope for some advice.
Kind regards,