0

I'm trying to get the .NumberFormat of a cell.

I tried putting the UDF both in the project where I'm using it and in my PERSONAL.XLSB.
I tried declaring them as both Function and Public Function, and neither are recognised. I get a #NAME? error.

The function, in PERSONAL.XLSB.

Public Function GetFormat(r As Range) As String
    GetFormat = r.NumberFormat
End Function

I've gone through this page. I can't figure out what's missing.

EDIT: It works if I specify PERSONAL.XLSB!GetFormat, but I'd prefer to be able to call it with just the =GetFormat([CELL]) format.

Community
  • 1
  • 1
Andrew Perry
  • 743
  • 2
  • 11
  • 32
  • try preface your call to the function with Personal.xlsb! –  Aug 15 '16 at 07:24
  • That works, yeah - but is there any way of setting it up so I can use it like any normal Excel function? – Andrew Perry Aug 15 '16 at 07:43
  • Is it in a `Module`? – Dirk Reichel Aug 15 '16 at 07:45
  • At the moment, yes - in a module in my `PERSONAL.XLSB`. I've also tried putting it in the workbook itself and in the sheet's own code. – Andrew Perry Aug 15 '16 at 07:58
  • 1
    To avoid using the workbook name as a prefix it must either be in the same workbook where you are using it in a cell, or in an add-in. It must also be in a normal module and that module should not have the same name as the function. – Rory Aug 15 '16 at 08:47
  • That's what I thought, but it didn't seem to help. I haven't tried making it an addin, but my first attempt was in a module. Ah well - I'll go with prefixing it for now and figure out why the other version wasn't working later. Thanks, folks! – Andrew Perry Aug 15 '16 at 11:17

2 Answers2

0

Reading .NumberFormat property return non-string and non-informative Null for multiple selection with various formats. Please, look:

'''''''
Public Function GetFormat(r As Range) As String
Dim rTmp As Range
    Select Case (Selection.Address = r.Address)
        Case True
            Set rTmp = ActiveCell ' #1 - for selection
        Case False
            Set rTmp = r(1)       ' #2 - for referenced range, having no active cell
    End Select
    GetFormat = rTmp.NumberFormat
    Stop
End Function

Private Sub sb_Test_GetFormat() ' for test purposes
Dim rTmp As Range, rSel As Range
    Set rSel = Selection ' save current Selection

    ' assign reference to any temporary Range with multiple formats
    Set rTmp = Range("$C$17:$C$27")

    rTmp.Select ' Select our tmp Range
    Debug.Print GetFormat(Selection)         ' #1 - work with Range as Selection
    rSel.Select ' restore Selection

    Cells(1).Select ' Move selection to rTmp independent area
    Debug.Print GetFormat(rTmp)              ' #2 - work with not selected Range
    rSel.Select ' restore Selection

    Debug.Print GetFormat(rTmp.Offset(1, 1)) ' #2 -work with other not selected Range

    Stop
End Sub
'''
user6698332
  • 407
  • 3
  • 14
  • Thanks, but the function I've got works fine - the question was just about making it work without needing a workbook reference. In the end I wound up just using a workbook reference for now. – Andrew Perry Aug 16 '16 at 06:49
0

Answer may be here: https://stackoverflow.com/a/16296990/3285233

Synopsis: don't create functions in the Microsoft Excel Objects; create a separate module and put them there.

trindflo
  • 311
  • 3
  • 12