How to reset sheet naming in Excel

Question

If I open an Excel workbook, then manually delete "Sheet2" and "Sheet3", when I next click the 'insert worksheet' button it will name the new worksheet "Sheet4".

However, after deleting "Sheet2" and "Sheet3", if I save and re-open the workbook, when I next click the 'insert worksheet' button it will name the new worksheet "Sheet2".

So Excel is storing a 'highest sheet number' variable somewhere, and this is reset when the worksheet is closed. I want to reset this with VBA.

Many thanks and best wishes.

Answer 1

No, there is no way to do this "naturally", without saving, closing and reopening the workbook. The sheets.count property is indeed read-only.

http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.sheets.count(v=office.15).aspx

But you could change names with vba, as you apparently know. I do agree that it is not really satisfying.

Complement/correction : In excel a sheet object has two different "name" properties : sheet.Name and sheet.CodeName. The Name property is read/write (i.e. can be read, fine, but changed also) and contains the name that you see, that appears on the sheet tab in excel. The CodeName property is read-only... See :

http://msdn.microsoft.com/en-us/library/office/ff837552(v=office.15).aspx

Answer 2

Well you can try hacking it, I was checking out the memory and it looks like there are two integers that keep the worksheet count. But if you delete some sheets the count also decreases so you probably need to widen your search to find the one you're looking for

Once you find the right memory address, try zeroing it (it will probably be 4 bytes, but I would try to just zero the byte that matters).

Here's the function I used to look through the memory (it gets the first 100 bytes after the memory address for worksheets.count, then adds a sheet, and gets the 100 bytes again)

Option Explicit

Private Declare Sub CopyMemory Lib "kernel32.dll" Alias "RtlMoveMemory" (Destination As Any, Source As Any, ByVal Length As Long)
Sub test2()
    'void ZeroMemory(
    '  [in]  PVOID Destination,
    '  [in]  SIZE_T Length
    ');

    'void CopyMemory(
    '  _In_  PVOID Destination,
    '  _In_  const VOID *Source,
    '  _In_  SIZE_T Length
    ');

    Dim b(0 To 99) As Byte
    Call CopyMemory(b(0), VarPtr(ActiveWorkbook.Worksheets.Count), 100)

    Dim output
    Dim i
    For Each i In b
        output = output & " " & Hex(i)
    Next i

    ActiveWorkbook.Worksheets.Add
    output = output & vbNewLine
    output = output & vbNewLine
    DoEvents

    Call CopyMemory(b(0), VarPtr(ActiveWorkbook.Worksheets.Count), 100)
    For Each i In b
        output = output & " " & Hex(i)
    Next i

    MsgBox output
End Sub