1

I am trying to change units from ug/Kg to mg/Kg with decimal numbers. To do this I am using a function to pass the cell with the original units (ug/Kg), and returning the modified units (mg/Kg). The issue has been trying to keep the same number of significant digits. I have figured out how to calculate the sig figs, but I am having trouble modifying the cell to be the correct number of digits dynamically. The examples I see online look something like this Range(A1).NumberFormat ="0.000" But this references the cell A1, and I need it to affect the cell I'm trying to return to.

The following code below is an example of what I'm trying to do, if anyone could help I would appreciate it.

Function ToMg(rng As Range)

Dim OrigNumb As Double    
Dim NewNumb As Double

OrigNumb = rng.Cells(1).Value

' Converts ug/Kg to mg/Kg    
NewNumb = OrigNumb / 1000

' **Assume that this stared comment code represents code that calculates that  
' **the format for the new mg/Kg cell needs to be "0.000" dynamically

' The following command is what I need help with.  
' I don't know either how
' to reference the cell ToMg is returning data too, or how to correctly change  
' the number format to for example "0.000"

Me.NumberFormat = "0.000"

'This passes the modified data into the cell as desired,(but sig figs are wrong)

ToMg = NewNumb

End Function

****Edit****

Test

The goal is to have the following

Link to example

In my head, I see the left column (Lets say C), being called by column D. So contents of D would be "=ToMg(C1)" (where C1 is the first data entry in the left column) with the number format of D being set dynamically. I don't think any non-dynamic number formatting will work in all the cases I have outlined above.

Community
  • 1
  • 1
Jason G
  • 11
  • 5
  • i tried understanding your post, with no luck. Can you post a screen-shot of your range you are trying to send, the value there, and the result you want to get ? the format of it ? (simulate it manually). – Shai Rado Dec 10 '16 at 07:08
  • are you using this function as a UDF (i.e. called form Excel UI) or in a VBA code? – user3598756 Dec 10 '16 at 07:39
  • You cannot do this with a function called from a worksheet - those have limitations: https://support.microsoft.com/en-us/kb/170787 – Tim Williams Dec 10 '16 at 08:00
  • @TimWilliams, I made my answer arguing OP'd most probably have answered "UDF". And in that case I'd have not also pointed out what you just did, but also added a possible workaround as per my answer – user3598756 Dec 10 '16 at 08:42
  • It's not the answer to what you may want; but you could use scientific notation. Therefore a number format of "0.000 E+00" gives you 4 sig. fig. and exponent amount? Or the following may be of use: http://stackoverflow.com/questions/20645757/how-to-get-excel-to-display-a-certain-number-of-significant-figures – Tragamor Dec 10 '16 at 11:31

2 Answers2

1

You could use the following VBA to set the number format, but it would set the format every time it is calculated, so it could potentially add significant delays to your calculation, and it's not the recommended way of doing this.

Function ToMg(rng As Range) As Double 'Use an explicit return type

  Dim OrigNumb As Double

  'Assuming the passed range is a single cell, you can omit the "Cells(1)"
  OrigNumb = rng.Value

  ' Converts ug/Kg to mg/Kg
  ToMg = OrigNumb / 1000

' **Assume that this stared comment code represents code that calculates that
' **the format for the new mg/Kg cell needs to be "0.000" dynamically
  Dim callingCell As Range
  Set callingCell = Application.Caller
  callingCell.NumberFormat = "0.000"

End Function

Far better would be to have either a consistent number of decimal places for all results, or to use special number format # characters, when formatting the cell (but not applying the format within the cell.

For example, let's say I have 3 values 1.2, 12.34, and 123.456 in cells A1:A3

If I apply a custom number format of ##0.0##, then the numbers are displayed as they were:

    1.2
  12.34
123.456

But if I apply a format that requires at least 2 integer digits, and at least 2 decimal digits, as Custom format #00.00#, then Excel displays the data as:

  01.20
  12.34
123.456

You haven't specified the exact format that you're after, but if you explore the Custom number formats that Excel offers, you should be able to come up with a format that accommodates all of your requirements, and which can be set before you start calculating the numbers, and without needing to be set repetitively.

ThunderFrame
  • 9,352
  • 2
  • 29
  • 60
  • Did you test this out? Setting the calling cell's number format from a UDF does not work for me. – Tim Williams Dec 10 '16 at 08:03
  • Yes, I tested the code (but you might need to ensure the cells are formatted with `General` format first), but I don't recommend using the VBA. Far better to use the appropriate number format when designing the sheet layout. – ThunderFrame Dec 10 '16 at 08:11
  • `Application.Caller.NumberFormat = "#00.00#"` in my UDF has no effect on the calling cell: number format remains at "General" – Tim Williams Dec 10 '16 at 08:15
  • Don't set the format in the UDF. Set the format in the cells you intend to use the UDF in *before* or after you enter the UDF. If you use the `#` and `0` characters appropriately, you can have the number formats automatically adjust the the value that is returned by the UDF. – ThunderFrame Dec 10 '16 at 08:18
  • I agree that you shouldn't try to set the formatting in the UDF. I'm specifically responding to your VBA code which suggests that you *could* do that, but will not do anything because you can't do that in a UDF. That's all. – Tim Williams Dec 10 '16 at 08:22
  • I also tested this and found it didn't work. Which is a shame, because it is EXACTLY what I needed. (The top bit). I added an example to my original post of they type of data I am working with and the desired output. I don't think a non-dynamic formatting will work with the examples I provided. My alternative is MANUALLY formatting 1000s of pieces of data for formatting, which I'm trying to avoid. – Jason G Dec 10 '16 at 15:19
1

as already pointed out, if you're using that function as a UDF then you shouldn't reach your goal of formatting the cell itself

I wrote you shouldn't because there are workarounds

for instance:

  • change your Function as follows

    Function ToMg(rng As Range) As String 'Use an explicit return type
  ToMg = (rng.Value / 1000) & "|" '<--| change the passed range content to the concatenation of wanted number and a "|" mark
End Function

  • add the following code in the worksheet code pane

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Right(Target.Value, 1) = "|" Then '<--| if changed cell is the one "marked" by 'ToMg()' function
        With Target '<--| reference changed cell
            .NumberFormat = "0.000" '<--| set its number format
            .Value = CDbl(Left(.Value, Len(.Value) - 1)) '<--| remove the "|" mark from its value
        End With
    End If
End Sub

As a principle you should add Application.EnableEvents = False statement before changing Target Value not to trigger the same Worksheet_Change() again in a possibly infinite loop.

But in this case it wouldn't do that since theIf Right(Target.Value, 1) = "|" check would return False and thus abort this first (and last) inner loop

user3598756
  • 28,893
  • 4
  • 18
  • 28