Excel 2016 VBA String Format Function Is Not Available

Question

I have recently purchased Office and the Excel 2016 VBA doesn't recognize the Format function for formatting strings in the VBA code.

Does anyone know what to use in place of this or if this can be corrected such that it recognizes the Format function in the VBA code?

Answer 1

Could we see an example of your code that is not working?

Here is a short sub that works fine in Excel 2010:

Sub TestFormat()

  Worksheets("Sheet1").Range("A1") = Format(Now(), "dd-mmm-yyyy hh:mm")

  Worksheets("Sheet1").Range("A2") = Format(1234567.89, "$#,##0.00")

End Sub

Answer 2

Seeing your code I opened a new blank workbook and the following works as it should:

Sub FormatTest()

  Dim myStr As String

  myStr = Format(123.453, "###.#")

End Sub

However, when I use my spreadsheet created in Excel 2010 and also used on a machine running Excel 2003 the same code does not work.  The following error is received.

So now I'm unsure why using the spreadsheet from previous versions fails on the Format function.  Everything else in the code appears to work.

Answer 3

In your case, a reference / library seems to be missing. Repairing Excel should resolve this problem.

https://support.office.com/en-NZ/Article/Repair-an-Office-application-7821d4b6-7c1d-4205-aa0e-a6b40c5bb88b

Answer 4

Thanks for the information on repairing.

But it appears this version of Excel has a number of issues with VBA when saved as an older version of Excel.  When using the Save As option and the compatibility mode it seems to have various issues with standard functions.  This appears to be a bug in Excel 2016 as the former versions I've been using do not have this issue.

Answer 5

With your problem workbook open, open the VB Editor, click Tools-References and tell us which reference(s) is/are checked and have 'MISSING:' at the start of their names.