0

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!

Matthew
  • 15
  • 1
  • 5
  • Do you mean sth like that `Range("A1").Formula = "='C:\FolderAlfa\SubfolderBeta\[Book1.xlsx]Sheet2'!$D$4"`? – Storax May 05 '19 at 08:26
  • 1
    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
  • 1
    @Storax: But a UDF cannot insert a formula into a range. – Charles Williams May 05 '19 at 08:51
  • @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

1 Answers1

0

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

enter image description here enter image description here

Storax
  • 11,158
  • 3
  • 16
  • 33