PDA

View Full Version : Solved: NumberFormat not working in a UDF



ALe
10-03-2006, 08:32 AM
Hi, I lost my mind in this UDF. I can't change the numberformat of a cell calling the formula Frazione.

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

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

Zack Barresse
10-03-2006, 09:24 AM
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
10-03-2006, 09:27 AM
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
10-03-2006, 09:33 AM
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
10-03-2006, 09:40 AM
Thanks Zack.
I'll have to find another solution because I need calculation on cells (strings don't allow it).

Zack Barresse
10-03-2006, 09:57 AM
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
10-03-2006, 10:13 AM
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
10-03-2006, 11:00 AM
Why not combine it with a Worksheet event?
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If UCase(Left(Target.Formula, 5)) = "=FRAZ" Then
Target.NumberFormat = "# ?/?"
End If
End Sub

matthewspatrick
10-03-2006, 07:25 PM
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
10-03-2006, 07:36 PM
Patrick

So what I said was incorrect?

matthewspatrick
10-03-2006, 07:39 PM
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
10-03-2006, 09:37 PM
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
10-03-2006, 10:14 PM
Here's an example (http://www.dailydoseofexcel.com/archives/2006/02/05/in-cell-charting/) of using a UDF to add/modify a chart in the cell.

ALe
10-04-2006, 12:07 AM
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
10-04-2006, 01:14 AM
AM I missing something? Why not just apply that format to the cells and leave it at that?

ALe
10-04-2006, 01:49 AM
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
10-04-2006, 02:45 AM
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



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


and worksheet event code of



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

ALe
10-04-2006, 03:03 AM
Yes, I agree worksheet event is the best solution we can reach.

matthewspatrick
10-04-2006, 05:36 AM
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 :)