Is there any way to calculate a hex "literal" number?

HCL Domino Domino Forum
1

I am trying to color the font in a particular cell in an Excel object with a custom color. To do so is quite simple - You simply issue a LotusScript command similar to “xlSheet.Range(“B1”).Font.Color=&H00FF00”. OK, so now I have offerred the user a color picker on a document to pick the color they want the font to be in Excel. That’s not a problem, since Notes passes an 8-character hex string back to me as the color field value.However, I can’t pass that string value on when I set the font color in excel - It is expecting a hex value. I have tried using “Val(&H00FF00)”, but I end up with a totally different colour (as it is passing the Double value).

Is there a way (Yes, I know I am supposed to be working with “literals” here) that I can calculate a hex value based on the string returned in the field value (some thing like cHex)?

2

Subject: Is there any way to calculate a hex “literal” number?

Shouldn’t be too hard: just make sure you pass a numeric value in the right data type (Val returns a Double and Excel expects a Long).

ColorText=“&h00ff00”

xlSheet.Range(“B1”).Font.Color=Clng(ColorText)

cheers,

Bram

3

Subject: You could always write your own cHex function

It wouldn’t be anything more than a loop and a select structure.

4

Subject: RE: Is there any way to calculate a hex “literal” number?

The function doesn’t really expect a hex number – it expects a number. Number values are not stored in a particular format – they’re all binary behind the scenes. If you were entering a literal, you could enter it in decimal or whatever base you want – using hex just makes your code more readable for humans.

Added comment: I like Bram’s way better. Doh!

Function HexToLong(Byval x As String) As Long

Dim intPos As Integer, intChr As Integer

x = Ucase(x)

For intPos = 1 To Len(x)

	intChr = Asc(Mid$(x, intPos, 1))

	If intChr >= &h30 And intChr <= &h39 Then ' zero thru 9

		HexToLong = HexToLong * 16 + intChr - &h30

	Elseif intChr >= &h41 And intChr <= &h46 Then ' A thru F

		HexToLong = HexToLong * 16 + intChr - &h41 + 10

	End If

Next

End Function

5

Subject: Thanks for your response - It’s Excel that’s the issue…

Unfortunately, I had tried your solutions earlier and had very weird results. You see, I use the proper hex values for the color I require, but Excel picks the “best fit”. This usually results in a very different color to the one selected in the Notes color-picker. Fro the moment, I will allow my users to select Green, Blue, Red, etc…, and then simply set the ColorIndex property of the Excel font.

Thanks for your feedback though!!