Solved: NumberFormat not working in a UDF

[ALe]

Hi, I lost my mind in this UDF. I can't change the numberformat of a cell calling the formula Frazione.

[VBA]Function Frazione(Numer As Integer, Denom As Integer)
Application.Volatile (True)
Dim RgCaller As Range
Set RgCaller = Application.Caller
Frazione = (Numer / Denom)
RgCaller.NumberFormat = "# ?/" & Denom
End Function[/VBA]

The fact is that it doesn't work with any NumberFormat.
Can't you change a cell while it is calculating?

[Zack Barresse]

I don't believe you can do that. You can however, format the cell as you would like, or include the format in your UDF and keep it as string.

[ALe]

I don't believe you can do that. You can however, format the cell as you would like, or include the format in your UDF and keep it as string.

What do you mean by "include the format in your UDF and keep it as string" ?

My aim is to change the numberformat.

[Zack Barresse]

I know your aim, you already stated that. I mean that you can keep it as a string, specifically placing each numerator, the seperator and the denomenator. Of course it would be easiest (IMO) to just manually format the cells as you would with any other.

[ALe]

Thanks Zack.
I'll have to find another solution because I need calculation on cells (strings don't allow it).

[Zack Barresse]

Is there more to this formula? Seems like you could do this natively and it would work better for you. That's what I would recommend (if this is the extent of the issues).

[Norie]

Alberto

You can't use a UDF to alter a worksheet.

All they do is return values.

What is it you are actually trying to do?

I assume it's something to do with fractions?

[mdmackillop]

Why not combine it with a Worksheet event?
[vba]Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If UCase(Left(Target.Formula, 5)) = "=FRAZ" Then
Target.NumberFormat = "# ?/?"
End If
End Sub
[/vba]

[matthewspatrick]

You can't use a UDF to alter a worksheet.
All they do is return values.

Yes, but incomplete. A VBA function *called from a worksheet cell* cannot do anything except return a value. A function called directly from VBA can alter stuff in the Excel UI quite readily.

That said, a while back someone (I want to say Matt Vidas) posted an example (here or at Experts Exchange) of a UDF called from a cell that did seem to do more than just return a value, but I cannot remember the details, and it was very obscure...

[Norie]

Patrick

So what I said was incorrect?

[matthewspatrick]

Patrick

So what I said was incorrect?

I prefer 'incomplete'

It's a matter of what the caller is. If the caller is a cell, then you're right: a function can only return a value. If the caller is from the VB project itself, then a function can alter any Excel object.

[Norie]

Patrick

As far as I can see the OP was using the UDF in a cell, not from any other code.

Personally, but of course I'm probably wrong/and or incomplete, but I wouldn't call a function called from other code a UDF.

If I did why wouldn't I call a sub a UFS(ub) or a UDP(rocedure)?

[Ken Puls]

Here's an example of using a UDF to add/modify a chart in the cell.

[ALe]

Thanks all. It's clear to me now that I can't change a cell with a UDF if this cell is the caller.

As you understood I'm working with fractions. My aim is to display a fraction as it is inserted in a cell (for example 4/10) and not as it is diplayed by excel in its simple fraction 2/5. If I had had only decimal fraction (1/10, 2/10, 4/10,...) I could have used the numberformat "# ?/10" but the problem is that the base number is always different.

To this point, mdmackillop's solution is good.

[Bob Phillips]

AM I missing something? Why not just apply that format to the cells and leave it at that?

[ALe]

hi xld,

I'll try to explain it better.

If I put the value 4/10 in a cell the result will be:
value=0,2
format="# ?/?"
displayed=2/5

I want it displayed as "4/10" so I change its formatnumber in "# ?/10"

If I insert in the same cell the fraction "18/20" then I get:
value=0.9
format="# ?/10"
displayed="9/10" BUT I WANT IT DISPLAYED AS I ENTERED IT!

So I want a fraction displayed as I entered it.
I can convert it as string but I'll lose calculation.

Enough clear?

[Bob Phillips]

Then Malcolm's suggestion looks best, as there is no way to stop Excel reducing a formula without forcing the format, so a formula of

[vba]

Function Frazione(Numer As Long, Denom As Long)
Application.Volatile (True)
Frazione = Numer / Denom
End Function
[/vba]

and worksheet event code of

[vba]

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Const FORMULA_PART As String = "=FRAZIONE("
Dim iPos As Long
With Target
If UCase(Left(.Formula, 10)) = FORMULA_PART Then
iPos = InStr(.Formula, ",")
If iPos > 0 Then
.NumberFormat = "# " & Application.Rept("?", Len(Mid(.Formula, 11, iPos - 11))) & _
"/" & Mid(.Formula, iPos + 1, Len(.Formula) - iPos - 1)

End If
End If
End With
End Sub
[/vba]

[ALe]

Yes, I agree worksheet event is the best solution we can reach.

[matthewspatrick]

Patrick

As far as I can see the OP was using the UDF in a cell, not from any other code.

Personally, but of course I'm probably wrong/and or incomplete, but I wouldn't call a function called from other code a UDF.

If I did why wouldn't I call a sub a UFS(ub) or a UDP(rocedure)?

Fair enough. I was getting too nitpicky