如何从Excel工作簿中选择值并通过活动工作簿上的功能返回 [英] How can I pick values from an Excel workbook and return them by function on active workbook
问题描述
由于我还没有掌握过很多VBA,所以我试图实现一个简单的到另一个工作簿中的单元格并返回值的函数:
函数Test()As String
Dim name As String
With Workbooks.Open(D:\ ExcelTest\WbSource.xlsm)。表(Sheet1)
name = .Cells(2,3)
结束
测试=名称
ActiveWorkbook.Save
ActiveWorkbook.Close
结束功能
问题是它给了我一个 #VALUE!
错误,但是使用的每个变量被定义为一个字符串,并且单元格具有通用格式(如果我将单元格格式更改为文字给我相同的消息)。
这是一种在队列中调度UDF执行的方法,并且在UDF之外处理以允许摆脱UDF限制。所以通过一个链接通过 ExecuteExcel4Macro()
获取封闭工作簿的价值。
将以下代码放入一个的VBAProject模块:
公共队列,QueueingAllowed,UDFRetValue
函数UDF(ParamArray Args() )
如果IsEmpty(Queue)然后
Set Queue = CreateObject(Scripting.Dictionary)
UDFRetValue =
QueueingAllowed = True
End If
如果QueueingAllowed然后Queue.Add Application.Caller,(Args)
UDF = UDFRetValue
结束函数
函数进程(Args)
如果UBound(Args)< ;> 4然后
Process =Wrong args number
Else
'Args(0) - 工作簿的路径
'Args(1) - filename
'Args 2) - sheetname
'Args(3) - row
'Args(4) - 列
On Error Resume Next
Process = ExecuteExcel4Macro('& Args )&和Arg(2)&R& Args(3)&C& Args(4))
如果
结束函数
将以下代码放入VBAProject Excel Objects的ThisWorkbook部分
$ b
Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
Dim Item,TempFormula
如果不是IsEmpty Queue)然后
Application.EnableEvents = False
QueueingAllowed = False
对于队列中的每个项目
TempFormula = Item.FormulaR1C1
UDFRetValue =进程(队列(项))
Item.FormulaR1C1 = Te mpFormula
Queue.Remove项
下一个
Application.EnableEvents = True
UDFRetValue =
QueueingAllowed = True
End If
End Sub
之后,您可以使用UDF通过工作表公式从封闭的工作簿中获取值:
= UDF(D:\ExcelTest\;WbSource.xlsm;Sheet1; 2; 3)
无论如何,您可以添加 Workbooks.Open()
或任何其他进入函数进程(Args)
,使其按照所需的方式工作。上面的代码只是一个例子。
我已经回答了类似的问题此处和此处,以便说明可能会有所帮助。
My goal is to implement some of functions where I give them parameters of power, frequency and speed of an electric motor, and look in another workbook (in which I have motor data) and return the size, shaft diameter and other motor details.
As I have not mastered much VBA I tried to implement a function that simply goes to a cell in another workbook and returns the value:
Function Test() As String
Dim name As String
With Workbooks.Open("D:\ExcelTest\WbSource.xlsm").Sheets("Sheet1")
name = .Cells(2, 3)
End With
Test= name
ActiveWorkbook.Save
ActiveWorkbook.Close
End Function
The problem is that it gives me a #VALUE!
error, but each variable used is defined as a string and the cells has general format (if I change cells format to text it gives me the same message).
Here is an approach with scheduling UDF execution in queue, and processing outside UDF that allows to get rid of UDF limitations. So the value from the closed workbook got via ExecuteExcel4Macro()
by a link.
Put the following code into one of the VBAProject Modules:
Public Queue, QueueingAllowed, UDFRetValue
Function UDF(ParamArray Args())
If IsEmpty(Queue) Then
Set Queue = CreateObject("Scripting.Dictionary")
UDFRetValue = ""
QueueingAllowed = True
End If
If QueueingAllowed Then Queue.Add Application.Caller, (Args)
UDF = UDFRetValue
End Function
Function Process(Args)
If UBound(Args) <> 4 Then
Process = "Wrong args number"
Else
' Args(0) - path to the workbook
' Args(1) - filename
' Args(2) - sheetname
' Args(3) - row
' Args(4) - column
On Error Resume Next
Process = ExecuteExcel4Macro("'" & Args(0) & "[" & Args(1) & "]" & Args(2) & "'!R" & Args(3) & "C" & Args(4))
End If
End Function
Put the following code into ThisWorkbook section of VBAProject Excel Objects:
Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
Dim Item, TempFormula
If Not IsEmpty(Queue) Then
Application.EnableEvents = False
QueueingAllowed = False
For Each Item In Queue
TempFormula = Item.FormulaR1C1
UDFRetValue = Process(Queue(Item))
Item.FormulaR1C1 = TempFormula
Queue.Remove Item
Next
Application.EnableEvents = True
UDFRetValue = ""
QueueingAllowed = True
End If
End Sub
After that you can get the values from closed workbook via worksheet formula using UDF:
=UDF("D:\ExcelTest\";"WbSource.xlsm";"Sheet1";2;3)
Anyway you can add Workbooks.Open()
or any other stuff into Function Process(Args)
to make it to work the way you want. The code above is just an example.
I've answered the similar questions here and here, so that descriptions might be helpful.
这篇关于如何从Excel工作簿中选择值并通过活动工作簿上的功能返回的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!