Custom number format sometomes fails

[JenniferMurphy]

Maybe the Alzheimer's is worse than I thought. I am trying to use this custom format to get numbers right-justified, but with a small margin on the right.

0.00_.

It works almost all of the time. There is one situation in one workbook where it fails and I cannot figure out why.

If I enter some numbers in some cells and apply this format, plus set the alignment to the right, it works perfectly. But when I apply it to a column of numbers that are the result of a UDF, it fails. As far as I can tell, all of the other cell attributes re identical (right align, right indent 0, bottom alignment, wrap off, shrink to fit off, merge cells off, no border.

If I copy the value of the cells to another column, the same thing happens.

BUT if I manually type the exact same value into the cell, the format works.

Is it possible that my UDF is returning a value with some attribute that conflicts with the custom format? If so, what is it? I have lots of other UDFs and none of them do this. This UDF returns a Variant value.

Help!!!

 

[footoo]

Is your UDF returning text instead of a number?

 

[JenniferMurphy]

Is your UDF returning text instead of a number?

Doh! (Where is the smack forehead emoji?)

Yes, it does return a string value. The UDF is Variant, so I thought it would consider it a number if it looked like one. I forgot that the "_x" format code does not work with text strings.

I changed it to return a number and now the formatting works.

But I can't change the UDF to Double and still use the CVErr function, right?

Thanks

 

[Rick Rothstein]

Yes, it does return a string value. The UDF is Variant, so I thought it would consider it a number if it looked like one. I forgot that the "_x" format code does not work with text strings.

I changed it to return a number and now the formatting works.

But I can't change the UDF to Double and still use the CVErr function, right?

Do not change the UDF to Double... leave it as a Variant... rather, apply the CDbl function to the text number your code is assigning to the UDF name, that way the UDF ends up returning a Double instead of a String.

 

[JenniferMurphy]

Do not change the UDF to Double... leave it as a Variant... rather, apply the CDbl function to the text number your code is assigning to the UDF name, that way the UDF ends up returning a Double instead of a String.

Is it correct that the UDF has to be Variant to use the CVErr function?

This is how the code used to look:

Public Function WtdRtg(pRatings As Range, . . . ) As Variant
   . . .
Dim SumWtdRtgs As Double
Dim SumWts As Double
   . . .
WtdRtg = SumWtdRtgs / SumWts
WtdRtg = Format(WtdRtg, "0.00")

I simply removed the last statement and now the formatting works. Do I also need to use the CDbl function? All of the variables that go into these calculations are declared as Double.

 

[Rick Rothstein]

Is it correct that the UDF has to be Variant to use the CVErr function?

As far as I know, the answer is "yes". The return value from a CVErr function is an Error, not a number, so your UDF has to be able to handle numbers and errors... the only type of variable that can do that is a Variant.

This is how the code used to look:

Public Function WtdRtg(pRatings As Range, . . . ) As Variant
   . . .
Dim SumWtdRtgs As Double
Dim SumWts As Double
   . . .
WtdRtg = SumWtdRtgs / SumWts
WtdRtg = Format(WtdRtg, "0.00")

I simply removed the last statement and now the formatting works. Do I also need to use the CDbl function? All of the variables that go into these calculations are declared as Double.

You have to remember that you did not post the UDF code originally, so we here had no idea how your text number was being produced (there are lots of ways that could happen), so I gave you a general method of how to make a text number into a Double value. Given your now posted code, no, you do not need to use the CDbl function. You had a Double value in your next to last line of code and then you used the Format function on it... the return value from the Format function is a text String, hence your problem. Omitting the Format function leaves the assigned return value a Double which is what you wanted. Note for future posts... if you have code that is not working, post it along with your description of the problem you are having with it (it is hard for us to be able to tell you how to fix code we cannot see). However, do not just post the code and ask "why doesn't it work?"... we need all of the information in order to make a fully informed response.

 

[JenniferMurphy]

As far as I know, the answer is "yes". The return value from a CVErr function is an Error, not a number, so your UDF has to be able to handle numbers and errors... the only type of variable that can do that is a Variant.

OK

You have to remember that you did not post the UDF code originally, so we here had no idea how your text number was being produced (there are lots of ways that could happen), so I gave you a general method of how to make a text number into a Double value. Given your now posted code, no, you do not need to use the CDbl function. You had a Double value in your next to last line of code and then you used the Format function on it... the return value from the Format function is a text String, hence your problem. Omitting the Format function leaves the assigned return value a Double which is what you wanted. Note for future posts... if you have code that is not working, post it along with your description of the problem you are having with it (it is hard for us to be able to tell you how to fix code we cannot see). However, do not just post the code and ask "why doesn't it work?"... we need all of the information in order to make a fully informed response.

OK. I didn't post the code originally because it didn't occur to me that the problem might be with the UDF. I think I have been reasonably diligent about posting code when I believed that the question was related to code.

In any case, the problem appears to have been resolved, so thanks for that.