vba 在excel中创建临时表来执行计算
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28497542/
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
Create temporary sheets in excel to perform calculations
提问by Reetika Choudhary
First of all, I have searched and read similar threads and I have tried the solutions that were proposed and solved the other people's problem but it hasn't worked in my case.
首先，我搜索并阅读了类似的线程，并尝试了提出的解决方案并解决了其他人的问题，但在我的情况下并没有奏效。
I basically want to create temporary excel sheet where I want to do all the calculations in the back-end and display only the output to the user in the front-end sheet.
我基本上想创建临时 Excel 工作表，我想在后端进行所有计算，并在前端工作表中仅向用户显示输出。
Can anyone tell me how do I go got creating the temporary sheets and deleting them as soon as numbers are copied in the front-end sheet i.e. the sheet that user will see.
谁能告诉我如何创建临时工作表并在前端工作表（即用户将看到的工作表）中复制数字后立即删除它们。
My main motive is not let the user see the various calculations that are happening in the back-end.
我的主要动机是不让用户看到后端发生的各种计算。
回答by Raugmor
you can create a temp sheet in VBA by using
Sheets.Add.Name = "Temp Sheet"or any other name really
and after you calculations done on it, delete it with
Sheets("Temp Sheet").Deletebear in mind if your alerts are on it will prompt user to agree to delete the sheet, you can avoid that by using Application.DisplayAlerts = Falsebefore deleting and follow by Application.DisplayAlerts = Trueafter
您可以通过使用Sheets.Add.Name = "Temp Sheet"或任何其他名称在 VBA 中创建临时表，
并在对其进行计算后，将其删除
Sheets("Temp Sheet").Delete，请记住，如果您的警报出现，它将提示用户同意删除该表，您可以通过以下方式避免这种情况Application.DisplayAlerts = False在删除之前使用并Application.DisplayAlerts = True在之后使用
回答by Maciej Los
I'd suggest to hide one sheet to perform calculations instead of creating temporary sheet.
我建议隐藏一张工作表来执行计算而不是创建临时工作表。
Let's say you have input sheet, where user input his data. Another sheet is hidden. There the calculations are performed.
假设您有输入表，用户可以在其中输入他的数据。另一个工作表被隐藏。在那里进行计算。
How to hide Sheet?
如何隐藏工作表？
Go to VBA Code editor window (ALT+F11). Select sheet you want to hide. In a "Properties" window choose option: SheetHidden. Now, you can copy data from hidden sheet into destination sheet via using macro.
转到 VBA 代码编辑器窗口 (ALT+F11)。选择要隐藏的工作表。在“属性”窗口中选择选项：SheetHidden。现在，您可以使用宏将隐藏工作表中的数据复制到目标工作表中。
ThisWorkbook.Worksheets("HiddenSheetName").Range("A1:C56").Copy
ThisWorkbook.Worksheets("VisibleSheetName").Range("A66:C106").PasteSecial xlValues
Note: i wrote above code straight from my head. Didn't tested!
注意：我直接从脑海中写下了上面的代码。没测试！
回答by BrakNicku
Sample code to create temp sheet:
创建临时表的示例代码：
Sub Sample()
Dim TempSheet As Worksheet
Application.ScreenUpdating = False
On Error Goto ErrorHandler:
Set TempSheet = ThisWorkbook.Worksheets.Add
TempSheet.Visible = xlSheetHidden
'Do the calculations
Application.DisplayAlerts = False
TempSheet.Delete
Application.DisplayAlerts = True
ErrorHandler:
Application.ScreenUpdating = True
End Sub
