Another Excel Question

[mathare]

I'm trying to get Excel, well VBA in truth, to generate some HTML tables for me. I have all the formatting tags for the HTML tables, that's not a problem. I want the tables to use data from certain cells in Excel and I am having a few issues.

One of the lines of HTML I am printing to a text file needs to contain the odds for a selection. The odds are stored in the Excel workbook in a cell with a ???/??? format but when I use that cell in my HTML I get the decimal form of the fraction so 2/9 comes out as 0.222222222 etc.

How can I stop this happening?

I have tried using the Format function in my output but it doesn't seem to recognise ?/? or similar as fractional formats.

Any ideas?

[mathare]

An alternative would be to convert the decimals to fractions myself somehow.

I thought of using a for loop along the lines of

For i = 2 to 100
'multiply decimal by i
'test to see if result is integer or not
'if so then result is numerator and i is denominator so exit for
Next i

Once I have numerator and denominator I can construct my own string.

But I can't find a decent way of testing to see if a value is an integer or not. The Mod function I thought of using (if result mod 1 returns 0 then we have an integer)converts all arguments to integer so won't work as I had hoped it would.

I also thought of copying the relevant cells to another place in the worksheet and then changing the cell format to text but that preserves the decimalisation of the fraction as that is how Excel treats the number, regardless of the cell format.

[mathare]

OK, I have a very dirty hacky solution. This is the true cowboy programmer coming out in me now, anything will do as long as it does the job :)

I have created a function that compares the decimal odds (shortened to 5 decimal places for convenience) with a case statement of the decimal odds for a number of known fractional odds, i.e.

Function ConvertOddsToString(ByVal DecOdds As Double) As String

Odds = Format(Odds, "#.#####")

Select Case Odds
Case 0.30303
ConvertOddsToString = "1/33"
.....
Case 0.22222
ConvertOddsToString = "2/9"
.....
End Select

End Function

It works. It's not nice, and it will take some setting up but it works.

[John]

Hi buddy, I would have help you but I can't remember the first thing about VBA since I only did it for about two months, and that was three years ago!

Can see how you've implemented it though, job done. :wink

[mathare]

Can see how you've implemented it though, job done. :wink
It's not pretty but it works. I'm not writing performance critical code so it doesn't matter how inefficient it is really

[TheOldhamWhisper]

Don't know if you could convert this VB Code:

http://www.freevbcode.com/ShowCode.Asp?ID=582

[mathare]

Oldham - once more you have come up with the goods!

A straight copy of that VB works. I think, only tested it briefly so far. Looks like it will do the business though and is much neater than the 85 case statements I was going to write this evening :)