如何创建一个在重新打开文档时起作用的 Excel VBA 函数？
好的，我刚刚编写了一个非常简单的 VBA 脚本，该脚本会获取在指定位置找到的文件的文件大小（编辑来自 dscarr)：
Public Function FileSize(path As String) As Variant
On Error GoTo Err_FileSize:
Dim retVal As Variant
Dim filesys As Object
Dim file As Object
retVal = ""
Set filesys = CreateObject("Scripting.FileSystemObject")
Set file = filesys.GetFile(path)
retVal = file.Size
Exit_FileSize: On Error Resume Next
FileSize = retVal
Exit Function
Err_FileSize: retVal = "Error: " & Err.Description
Resume Exit_FileSize
End Function
如果我将其导入到新工作簿中，请将文档另存为“启用 Excel 宏的工作簿”以及名为的空文件readme.txt 然后将“./readme.txt”放入 A1 并将“=FileSize(A1)”放入 A2，A2 会正确计算出 readme.txt 的文件大小>, 0 字节。伟大的。如果我随后保存文档，关闭并重新打开它，则会警告我宏已被禁用。如果我启用它们，A2 的内容将更改为 #VALUE!，并且我所做的任何操作都无法使该功能再次工作。有人以前见过这个吗？有人能指出我在这里犯的错误吗？
编辑：问题似乎是由我使用相对路径引起的。我无法解释为什么它适用于新工作簿，但保存并重新打开后就不行，但使用绝对路径可以解决问题，正如 dscarr 所指出的那样，这是一个“找不到文件”问题。
OK, I've just written a very simple VBA script that goes off and grabs the file size of a file found at a specified location (edit update code from dscarr):
Public Function FileSize(path As String) As Variant
On Error GoTo Err_FileSize:
Dim retVal As Variant
Dim filesys As Object
Dim file As Object
retVal = ""
Set filesys = CreateObject("Scripting.FileSystemObject")
Set file = filesys.GetFile(path)
retVal = file.Size
Exit_FileSize: On Error Resume Next
FileSize = retVal
Exit Function
Err_FileSize: retVal = "Error: " & Err.Description
Resume Exit_FileSize
End Function
If I import this into a new workbook, save the document as a "Excel Macro-Enabled Workbook" alongside an empty file called readme.txt and then put "./readme.txt" in A1 and "=FileSize(A1)" in A2, A2 evaluates properly to the file size of readme.txt, 0 bytes. Great. If I then save the document, close and reopen it, I'm warned that macros are disabled. If I enable them, the content of A2 has changed to #VALUE! and nothing I do will make the function work again. Has anyone seen this before, can anyone point out the mistake that I'm making here?
edit: the issue seems to be caused by me using relative paths. I can't explain why it works for a new workbook, but not once saved and reopened, but using absolute paths solves the problem, which as dscarr identified, was a "File Not Found" issue.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
我在 Excel 2007 启用宏的工作簿中复制了您的代码，发现它在以下注意事项下工作得很好
当您打开工作簿时，它不会自动更新值（例如运行 FileSize 函数）。这可以通过向计算工作簿的 Worrkbook_Open 事件处理程序中添加一些代码来弥补。
当找不到指定的文件时，我收到 #VALUE 错误。事实上，当文件丢失或错误命名时，“Set file = filesys.GetFile(path)”行会抛出错误号 53“File Not Found”。在这种情况下，永远不会设置返回值，因为设置它的代码行永远不会被调用。在实际尝试检索文件大小之前，您可以尝试设置默认值“找不到文件”。
I duplicated your code in an Excel 2007 Macro Enabled workbook and found that it worked just fine with the following caveats
It does not automatically update the value (e.g. run the FileSize function) when you open the workbook. This can be compensated for by adding some code to the Worrkbook_Open event handler that calculates the workbook.
I get the #VALUE error when it cannot find the file that is specified. Indeed, when the file is missing or incorrectly named the line "Set file = filesys.GetFile(path)" throws the error number 53 "File Not Found". In this case the return value is never set because the line of code that sets it is never called. You could try setting a default value of "File Not Found" before actually attempting to retrieve the file size.
在 Excel 2010 中，转到“文件”->“选项”->“信任中心”->“宏设置”，并确保已选择“启用”选项按钮。
In Excel 2010, Go File->Options->Trust Center->Macro Settings and make sure the Enable option button has been selected.