vba 如何在 Excel 中重置工作表命名
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26556943/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to reset sheet naming in Excel
提问by Geddes
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".
如果我打开一个 Excel 工作簿，然后手动删除“Sheet2”和“Sheet3”，当我下次单击“插入工作表”按钮时，它会将新工作表命名为“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".
但是，删除“Sheet2”和“Sheet3”后，如果我保存并重新打开工作簿，当我下次单击“插入工作表”按钮时，它会将新工作表命名为“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.
因此，Excel 在某处存储了一个“最高工作表编号”变量，并在工作表关闭时将其重置。我想用 VBA 重置它。
Many thanks and best wishes.
非常感谢和最良好的祝愿。
采纳答案by Alter
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).
一旦找到正确的内存地址，请尝试将其归零（它可能是 4 个字节，但我会尝试将重要的字节归零）。
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)
这是我用来查看内存的函数（它获取worksheets.count的内存地址后的前100个字节，然后添加一张表，再次获取100个字节）
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
回答by Olorin
No, there is no way to do this "naturally", without saving, closing and reopening the workbook. The sheets.count property is indeed read-only.
不，没有办法“自然地”做到这一点，而无需保存、关闭和重新打开工作簿。sheet.count 属性确实是只读的。
But you could change names with vba, as you apparently know. I do agree that it is not really satisfying.
但是您可以使用 vba 更改名称，正如您显然知道的那样。我确实同意这并不令人满意。
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 :
补充/更正：在 Excel 中，工作表对象具有两个不同的“名称”属性：sheet.Name 和 sheet.CodeName。Name 属性是读/写（即可以读取，可以，但也可以更改）并包含您看到的名称，该名称出现在 Excel 的工作表选项卡上。CodeName 属性是只读的...请参阅：
http://msdn.microsoft.com/en-us/library/office/ff837552(v=office.15).aspx
http://msdn.microsoft.com/en-us/library/office/ff837552(v=office.15).aspx
回答by Mike LaCombe
This will change the sheet numbers and leave the names in tact
这将更改工作表编号并使名称保持原样
Sub BatchChange_WSRefName()
' THIS IS MAINTENANCE CODE ONLY RUN MANUALLY
' Changes the Reference Names for all Worksheets
' in the active Workbook to Sheet + incrementing integer
Dim i As Integer, ws As Worksheet
i = 0
' Change to Temp first to prevent Naming errors
For Each ws In ActiveWorkbook.Worksheets
i = i + 1
On Error Resume Next
ws.Parent.VBProject.VBComponents(ws.CodeName).Properties("_CodeName") = _
"Temp" & i
On Error GoTo 0
Next ws
' Change to Sheet + incrementing integer
i = 0
For Each ws In ActiveWorkbook.Worksheets
i = i + 1
On Error Resume Next
ws.Parent.VBProject.VBComponents(ws.CodeName).Properties("_CodeName") = _
"Sheet" & i
On Error GoTo 0
Next ws
Set ws = Nothing
End Sub
回答by Paul
I'm very late to the party, but I was just looking into this today and found another solution.
我参加聚会很晚，但我今天只是在研究这个问题并找到了另一个解决方案。
The sheet numbers (Sheet1, Sheet2, etc.) can be changed manually if you open the Visual Basic window and change it in the Sheet Properties section. This also has the added benefit of resetting the count so the next new sheet will continue in sequence.
如果您打开 Visual Basic 窗口并在“工作表属性”部分更改它，则可以手动更改工作表编号（Sheet1、Sheet2 等）。这还具有重置计数的额外好处，因此下一个新工作表将按顺序继续。
