Excel VBA无法打开工作簿 [英] Excel VBA can't open Workbook

问题描述

首先：我使用的是Excel 2007，但代码也适用于Excel 2003。

我的问题如下：我需要访问单元格在不同的工作簿，可能会关闭。以下代码可以在网络上找到：

 函数Foo（）
 Dim cell As Range 
 Dim wbk As Workbook 
设置wbk = Workbooks.Open（正确的绝对路径）
'wbk这里没有，所以下一个语句失败。 
设置单元格= wbk.Worksheets（Sheet1）。范围（A1）
 Foo = cell.Value 
 wbk.Close 
结束函数
  

可悲的是，wbk在打开声明后没有什么（我想给一个更好的错误信息，但不知道我是怎么' d这样做;我给一个真正的IDE和一个有用的语言：/）。绝对路径是正确的，并指向一个有效的excel xlsx文件。

此外，我认为最好的方法是这样做，是缓存工作簿而不是打开/每次调用函数时关闭它？任何可能的问题（除了必须处理工作簿已经打开的情况外）？

逐步执行图像：

解决方案

我可以重现这个问题。只有当我尝试将此代码粘贴到用户定义的函数中时，才会发生。

我相信这是通过设计（引用是XL 2003，但是// @ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $¬$ <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

您可以在自定义函数中使用的VBA关键字数量小于您可以在宏中使用的数量。自定义函数不允许执行任何其他操作，除了将值返回到工作表中的公式或另一个VBA宏或函数中使用的表达式。例如，自定义函数无法调整窗口大小，编辑单元格中的公式，或更改单元格中文本的字体，颜色或图案选项。如果在函数过程中包含此类action代码，函数返回#VALUE！错误。

http://office.microsoft.com/en-us/excel-help/creating-custom-functions-HA001111701.aspx

我发现唯一的解决方法是通过一个普通的宏来调用这种代码。像选择单元格应用它，然后循环选择等等。

First: I'm using Excel 2007, but the code has to work for Excel 2003 as well.

My problem is the following: I need to access cells in a different workbook, which may be closed. The following code can be found all around the web:

Function Foo()
    Dim cell As Range
    Dim wbk As Workbook
    Set wbk = Workbooks.Open("correct absolute path")
    ' wbk is Nothing here so the next statement fails.
    Set cell = wbk.Worksheets("Sheet1").Range("A1")
    Foo = cell.Value
    wbk.Close
End Function

sadly, wbk is Nothing after the open statement (I'd love to give a better error message, but no idea how I'd do that; what I'd give for a real IDE and an useful language :/). The absolute path is correct and points to a valid excel xlsx file.

Also I assume the best way to do this, is to "cache" the workbook and not open/close it every time the function is called? Any possible problems with that (apart from having to handle the situation when the workbook is already open obviously)?

Image while stepping through:

解决方案

I can reproduce this problem. It only happens to me when I attempt to paste this code into a user-defined function.

I believe this is by design (the quote is for XL 2003, but the same thing happens to me on XL 2010)

Using VBA keywords in custom functions

The number of VBA keywords you can use in custom functions is smaller than the number you can use in macros. Custom functions are not allowed to do anything other than return a value to a formula in a worksheet or to an expression used in another VBA macro or function. For example, custom functions cannot resize windows, edit a formula in a cell, or change the font, color, or pattern options for the text in a cell. If you include "action" code of this kind in a function procedure, the function returns the #VALUE! error.

http://office.microsoft.com/en-us/excel-help/creating-custom-functions-HA001111701.aspx

The only workaround I've found is to call this kind of code via a normal macro. Something like selecting the cells to apply it to, then looping over Selection or the like.

这篇关于Excel VBA无法打开工作簿的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！