1

I want to create a function that takes a string as input, use this string as argument in a vlookup to get a value from a closed workbook.

The following code works when the data and the string are both in the same worksheet:

Function get_value(inputString as String)

    Dim dataRange as Range

    Set dataRange = Range("A1:B4")

    get_value = Application.WorksheetFunction.Vlookup(inputString, dataRange, 2, False)

End Function

Simply referencing the range (like the code below) doesn't work (I assumed that this is because Functions can't handle Workbooks.Open like Subs).

Set workbookVariable = Application.Workbooks.Open(path_to_file)

dataRange = workbookVariable.Sheets(1).Range("A1:B4")

My table (saved as 'names.xls', saved in Desktop) looks like:

           A       |        B
    1    Olivia    |       Spaghetti
    2    John      |       Steak
    3    Samuel    |       Rice
    4    Brian     |       Chicken

I want want a function call like:

=get_value(A1) and that will return the food of the name in A1.

How can I adjust my code so get_value works on other workbooks too ?

Renan Tardelli
  • 143
  • 1
  • 12
  • This may give you an idea, not tested thou. With the external workbook active, get the string from immediate window for `Thisworkbook.Path & Application.PathSeparator & Range("A1:B4").Address(External:=true)`. You may need parts of it and setup Connection to that workbook. – PatricK Jul 17 '17 at 22:57
  • https://stackoverflow.com/a/42800024/4539709 – 0m3r Jul 18 '17 at 03:53
  • 0m3r, not the same question, I need a Function. – Renan Tardelli Jul 18 '17 at 08:19

1 Answers1

0
Function auto_open()

Workbooks.Open ("C:\Users\bmartin598\desktop\name")

Workbooks("Book2").Activate 
'make this match the workbook this code is in

End Function



Function get_value(inputString As String)

Dim dataRange As Range

Dim workbookVariable As Workbook

Dim strVal As Variant

Set workbookVariable = Workbooks("name") 'change to match workbook name

Set dataRange = workbookVariable.Sheets(1).Range("A1:B4")

strVal = Workbooks("name").Sheets("Sheet1").Range(inputString).Value 
'make sure workbooks matches the name of your workbook

get_value = WorksheetFunction.VLookup(strVal, dataRange, 2, False)


End Function

Function auto_close()
Workbooks("name").Close
End Function

You can use this as a workaround. It automatically opens the other workbook whenever this one is open and puts it in the background. When you close the workbook you are working in the reference workbook closes as well.

Hope this helps.

bmartin598
  • 37
  • 4
  • 10
  • Still doesn't work, it's weird that works for you. I was suspicious that my problem was due to Functions' limitations.(https://stackoverflow.com/questions/7693530/excel-vba-cant-open-workbook) – Renan Tardelli Jul 18 '17 at 19:02
  • Where is the code failing? Also what version of excel are you using? – bmartin598 Jul 18 '17 at 19:20
  • The function returns #VALUE! for any input. Running Excel for Mac 15.33 (2017) and Excel 2010 (windows). – Renan Tardelli Jul 18 '17 at 20:40
  • @Renan could you please provide your entire code including where the get_value function is getting the input from. Using mine I can not replicate your error. – bmartin598 Jul 19 '17 at 15:09
  • Updated answer to one that should better meet your needs. Hope it helps. – bmartin598 Jul 20 '17 at 13:47
  • I still get an error, this is very weird. Just to clarify: Suppose both workbooks are in Desktop and are called "Data_Workbook" and "Function_Workbook". One thing I don't get is why you are opening "name" (i.e. Data_workbook) and later in the code you use "name" workbook to get the string value from. I'm a little confused, either I can't understand why you did that or my question is not clear. – Renan Tardelli Jul 21 '17 at 14:07
  • What I want is basically a Vlookup, to avoid writing a long formula referencing the whole path every time, I want a function that does that for me. So, in "Function_workbook" I'll call =get_value and vba under the hood will make a vlookup to "Data_workbook" and return the value that corresponds to my String. – Renan Tardelli Jul 21 '17 at 14:10