UDF-如何将包含路径，工作簿名称，工作表名称和单元格引用的字符串转换为公式? [英] UDF - How to turn a string that contains a path, workbook name, worksheet name and cell reference into a formula?

问题描述

我正在尝试编写一个UDF，它将把包含路径，工作簿名称，工作表名称和单元格引用的字符串转换为公式，以便我可以轻松地从其他工作簿返回值.例如，我的字符串如下所示:

I'm trying to write a UDF that will turn a string that contains path, workbook name, worksheet name and a cell reference into a formula so I can easily return values from other workbooks. For example, my string looks like:

='C:\FolderAlfa\SubfolderBeta\[Book1.xlsx]Sheet2'!$D$4

我尝试了Application.Evaluate(string)和ExecuteExcel4Macro(string)方法，但是它们都不起作用.

I tried Application.Evaluate(string) and ExecuteExcel4Macro(string) methods but none of them are working.

我将不胜感激！

推荐答案

基于我发现的内容

Based on what I found here it is possible to change cells with an UDF and insert a formula.

这有点麻烦(也许有人可以改善它)，但是它可以工作.您还需要重新计算无法从函数getValue和子addFormula触发的内容.您必须将它放在其他地方.参数rg是要在其中放置公式的单元格.确保不是放置getValue的单元格.

It is a little bit cumbersome (maybe there is someone out there who can improve it) but it works. You also need to do a recalculation what you can't trigger from the function getValue and the sub addFormula. You must put it somewhere else. The parameter rg is the cell where you want to put the formula. Make sure is is not the cell where you put getValue.

Function getValue(rg As Range, path As String, file As String, sheet As String, ref As String)
    Evaluate "addFormula( " & Chr(34) & rg.Address & Chr(34) & "," & Chr(34) & "'" & path & "[" & file & "]" & sheet & "'!" & ref & Chr(34) & ")"
    getValue = ""
End Function

Sub addFormula(trgAddress As String, myFormula As String)
    Dim trgRg As Range

    Set trgRg = Range(trgAddress)
    trgRg.Formula = "=" & myFormula

End Sub

在工作表选择更改中，我添加了计算方法.当然，这不是最好的方法，但它表明是可能的.

In the worksheet selection change I added the calculate method. This is for sure not the best way to do it but it shows it is possible.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Me.Calculate
End Sub

以及如何使用它:函数在D5中，结果在E5中

And that how you can use it: The function is in D5, the result in E5

这篇关于UDF-如何将包含路径，工作簿名称，工作表名称和单元格引用的字符串转换为公式?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！