29

I am a VBA newbie, and I am trying to write a function that I can call from Excel cells, that can open a workbook that's closed, look up a cell value, and return it.

So far I know how to write a macro like this:

Sub OpenWorkbook()
    Dim path As String
    path = "C:\Users\UserName\Desktop\TestSample.xlsx"

    Dim currentWb As Workbook
    Set currentWb = ThisWorkbook


    currentWb.Sheets("Sheet1").Range("A1") = OpenWorkbookToPullData(path, "B2")
End Sub


Function OpenWorkbookToPullData(path, cell)

    Dim openWb As Workbook
    Set openWb = Workbooks.Open(path, , True)

    Dim openWs As Worksheet
    Set openWs = openWb.Sheets("Sheet1")

    OpenWorkbookToPullData = openWs.Range(cell)

    openWb.Close (False)

End Function

The macro OpenWorkbook() runs perfectly fine, but when I am trying to call OpenWorkbookToPullData(...) directly from an Excel cell, it doesn't work. The statement:

    Set openWb = Workbooks.Open(path, , True)

returns Nothing.

Does anyone know how to turn it into a working VBA function that can be called from Excel cell?

pnuts
  • 58,317
  • 11
  • 87
  • 139
user780069
  • 431
  • 2
  • 5
  • 6
  • 1
    Thanks for the help. @pnuts My problem though isn't how to do write VBA function generally, but more specifically here. OpenWorkbookToPullData(...) does get called and I can step in. But the problem is on the line where Workbooks.Open(...) is executed, it's returned as Nothing. The sub OpenWorkbook(), which calls OpenWorkbookToPullData(...), works fine. – user780069 Oct 26 '13 at 01:10
  • Thanks @PortlandRunner However, the function itself "works" in the sense that the macro sub OpenWorkbook() works fine. The problem isn't really the syntax there. The problem is that, if you call OpenWorkbookToPullData(...) from Excel cell, the line Workbooks.Open(...) returns Nothing. If it's called from a subroutine, it works fine. – user780069 Oct 26 '13 at 01:11
  • @PortlandRunner Unfortunately no. The string "path" is passed into the Function correctly even via an Excel call. The problem is that the method call Workbooks.Open(...) behaves differently whether it's called from a subroutine or from an Excel cell. In the former case, it works. In the latter, it returns Nothing. – user780069 Oct 26 '13 at 01:19

3 Answers3

51

Here's the answer

Steps to follow:

  1. Open the Visual Basic Editor. In Excel, hit Alt+F11 if on Windows, Fn+Option+F11 if on a Mac.

  2. Insert a new module. From the menu: Insert -> Module (Don't skip this!).

  3. Create a Public function. Example:

    Public Function findArea(ByVal width as Double, _
                         ByVal height as Double) As Double
    ' Return the area
    findArea = width * height
End Function

  4. Then use it in any cell like you would any other function: =findArea(B12,C12).

FloatingRock
  • 6,741
  • 6
  • 42
  • 75
  • 1
    As soon as I enter a value for the second parameter (height) it reports me about "some general problem" with my formula and refuses to store it in a cell. Unfortunately, it reports in German, so i can't provide the text here as an example. – Paul Sep 19 '16 at 11:54
  • @Paul without an error message, version & environment details it's hard to dig further into this – FloatingRock Sep 20 '16 at 05:57
  • 9
    Typically I thought I could do better than just follow instructions. Step 2 is important! – Adam Jan 31 '17 at 10:12
  • And add `Application.Volatile` in the top of your function so that the value is updated when dependent cells are modified – A. Richard Jun 10 '20 at 11:40
1

The issue you have encountered is that UDFs cannot modify the Excel environment, they can only return a value to the calling cell.

There are several alternatives

  1. For the sample given you don't actually need VBA. This formula will work
    ='C:\Users\UserName\Desktop\[TestSample.xlsx]Sheet1'!$B$2

  2. Use a rather messy work around: See this answer

  3. You can use ExecuteExcel4Macro or OLEDB

Community
  • 1
  • 1
chris neilsen
  • 52,446
  • 10
  • 84
  • 123
  • @chrisneilson, thanks. The problem with the syntax ='filepath' is that the workbook has to be opened. There are about 100 workbooks lying around, and I want specific cells from each of them. Do you have other workarounds? I tried ExecuteExcel4Macro, but that also seems unable to run under Excel cell call. Do you have example using OLEDDB? – user780069 Oct 26 '13 at 13:34
  • FYI, I'm looking at the option #2. It seems promising. Do you have any hints on how to modify it? I'm not quite sure how to modify it to do what I want to do... – user780069 Oct 26 '13 at 15:04
  • With the formula option, the wb does _not_ need to be open. That's why I included it in the answer. – chris neilsen Oct 26 '13 at 19:18
0

A Function will not work, nor is it necessary:

Sub OpenWorkbook()
    Dim r1 As Range, r2 As Range, o As Workbook
    Set r1 = ThisWorkbook.Sheets("Sheet1").Range("A1")
    Set o = Workbooks.Open(Filename:="C:\TestFolder\ABC.xlsx")
    Set r2 = ActiveWorkbook.Sheets("Sheet1").Range("B2")
    [r1] = [r2]
    o.Close
End Sub
Gary's Student
  • 95,722
  • 10
  • 59
  • 99
  • Thanks. I know how to write the subroutine but I want a function that can do the same thing. If you write a subroutine you more or less have to hardcode the path, cell location, etc. I am trying to update something that can pull a couple of cells from each of the 100 Excel workbooks that I have, and a Function is a lot more flexible. – user780069 Oct 26 '13 at 14:46
  • I think the best you can do is to store the filespecs, sheetnames, and cell addresses in arrays and run code similar to mine in a loop....good luck! – Gary's Student Oct 26 '13 at 14:55