UDF - How to turn a string that contains a path, workbook name, worksheet name and cell reference into a formula?

Question

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

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

I would appreciate some help!

Do you mean sth like that `Range("A1").Formula = "='C:\FolderAlfa\SubfolderBeta\[Book1.xlsx]Sheet2'!$D$4"`? — Storax, May 05 '19 at 08:26
Not sure why you want a UDF ... you could always use the evil INDIRECT function in a formula instead. Note that none of these methods work when the external workbook is closed — Charles Williams, May 05 '19 at 08:40
Yes, but I would like this to be an UDF so I'm not sure how to use the Range object since it requires a reference (like "A1") — Matthew, May 05 '19 at 08:46
Yeah, that's the problem. The Evaluate method works perfectly when the other workbooks is open but I'm looking for a solution that would work if the other workbook is closed. — Matthew, May 05 '19 at 08:47
Then the formula I posted should work. It will just add a link to the workbook even if it's closed. — Storax, May 05 '19 at 08:49
@Charles Williams: Right but I am not saying the OP should use an UDF. If he would like to use an UDF then he will need to go another direction. — Storax, May 05 '19 at 08:52
@Storax: I agree with you. But since I'm doing a UDF (not a procedure), what argument should I put in Range() in order to work? For example, I created a UDF whose arguments are path, workbook name, worksheet name and cell. This UDF creates a string of all these arguments. Now I need some method to turn this string into a formula so the actual value would be returned — Matthew, May 05 '19 at 08:53
@Matthew: I added an answer where an UDF inserts a formula into a worksheet. — Storax, May 05 '19 at 15:16

JvdV · Answer 1 · 2019-05-05T15:15:41.063

0

You can't use a UDF to insert a formula. I'm afraid you'll have to use an XLM macro to reference a range in a closed file. I found this information HERE

So in practice:

My testfile value:

The somewhat edited code:

Private Function GetValue(path, file, sheet, ref)

Dim arg As String
If Right(path, 1) <> "\" Then path = path & "\"
If Dir(path & file) = "" Then
    GetValue = "File Not Found"
    Exit Function
End If
arg = "'" & path & "[" & file & "]" & sheet & "'!" & Range(ref).Range("A1").Address(, , xlR1C1)
GetValue = ExecuteExcel4Macro(arg)

End Function

Sub TestGetValue()

With ActiveWorkbook.Sheets(1)
    path = .Cells(1, 3)
    file = .Cells(2, 3)
    sheet = .Cells(3, 3)
    ref = .Cells(4, 3)
    .Cells(1, 1) = GetValue(path, file, sheet, ref)
End With

End Sub

Use a button and assign macro to run on click

edited May 05 '19 at 15:15

answered May 05 '19 at 08:56

JvdV

53,146
6
36
60

But why should the OP use this complexer solution instead of what I suggested in my comments. He would like to have an UDF and as you absolutely correctly stated you cannot insert a formula with an UDF. – Storax May 05 '19 at 08:59
@Storax, you are correct! Your method should also work. BTW, I never stated he should use this solution instead of your ;). If he can build strings like in his question, than your suggested method may be even more elegant. – JvdV May 05 '19 at 09:05
Sorry I did not want to imply anything (not sure if imply is the right word). – Storax May 05 '19 at 09:08
@JvdV: Thanks for this solution. However, I need a plain UDF :) – Matthew May 05 '19 at 09:08
@Storax: Can you make a quick example? Cause I'm still confused how to use Range in an UDF. – Matthew May 05 '19 at 09:10
@Matthew: What do you mean? You can't use an UDF to insert a formula into a worksheet. – Storax May 05 '19 at 09:13
@Storax: OK, I missunderstood you. Thanks anyway! I will use your method if I'll need a procedure instead of a UDF for my problem – Matthew May 05 '19 at 09:15

Storax · Accepted Answer · 2019-05-05T14:50:39.840

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

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

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

You are welcome! I learned that it is possible to change with an UDF cells in a worksheet although MS documentation states otherwise. — Storax, May 05 '19 at 17:50

UDF - How to turn a string that contains a path, workbook name, worksheet name and cell reference into a formula?

2 Answers2

Linked