0

I am trying to call a function in Excel in one file using a vlookup from another file. The code works correctly if I am using it from the sheet that has the lookup table. However, when I run it from another file, I usually get a #Value error. Until I am using the source file for other things. Then it magically works in the background. Until I click on it and then it goes back to #Value. Here is the top of the function from VBA:

'Requires the correct workbook to work
Function testFunction(body As String)

'open the source file (didn't work)
'Dim location As String
'location = "C:\Users\moish\OneDrive\Documents\space flight.xlsx"
'Dim book As Workbook

Dim sheet As Worksheet

'Set book = Workbooks.Open(location)
'Set sheet = book.Sheets("Bodies")

Set sheet = ActiveWorkbook.Sheets("Bodies")

'clean up input
Dim rng As Range
Dim matchValue, matchType
    
Set rng = ActiveSheet.Range("A2:A221")
    
'try exact match
matchValue = Application.match(body, rng, 0)
If Not Application.IsNA(matchValue) Then
    matchType = "Exact"
    testFunction = WorksheetFunction.VLookup(body, sheet.Range("A1:J221"), 2, False)
End If
End Function

The rest of it shouldn't matter to the problem. Usage: =testFunction("Jupiter")

UPDATE: This works if I open the correct file manually and just leave it in the background. I am not sure why it needs to be coded this way though and I cannot figure out how to get it to open the correct file automatically.

Dim sheet As Worksheet
Workbooks("space flight.xlsx").Worksheets("Bodies").Activate
Set sheet = Workbooks("space flight.xlsx").Worksheets("Bodies")
Improve this question
6
  • Application.match(body, rng, 0) ? where is body defined ? Set rng = ActiveSheet.Range("A2:A221") ? Why ActiveSheet and not ActiveWorkbook.Sheets("sheetname") ? Commented May 2 at 21:29
  • A function called from a worksheet is restricted in the actions it can take - when used in this context it can return a value or array of values, but it can't change the excel environment such as by opening another workbook. support.microsoft.com/en-us/topic/…. And also: stackoverflow.com/questions/23433096/… Commented May 2 at 21:48
  • 1
    ...even if it did work, every function call would need to open the workbook (and did you mean to close it when done?), so performance would be a bit slow. You might consider caching the data in a 2D array as a global variable, and then looping on the first column to find the best match. That will still be faster than opening a file. Commented May 2 at 22:11
  • I shortened a bunch of stuff when I created the post and apparently didn't correctly test the behavior before I posted the shortened version. The input variable "str" was originally "body". Commented May 8 at 20:41
  • I do not actually know how to read the file into VBA and just turn it into something permanent. That would probably be ideal as I don't intend to expand the data or anything. I sure wouldn't want to manually type the array that is currently a 200 by 10 ten cell worksheet. Commented May 8 at 20:44

1 Answer 1

Reset to default
0

Okay, so I found a solution that works. Apparently I can't use Open to open excel in excel. I have to use workbooks.open. This solution simply opens the source file, copies the relevant sheet into the current document and closes the sourcefile. problem solved. (The help document that it is based on is here, but it is a bit incorrect and needed playing with: https://learn.microsoft.com/en-us/office/vba/api/excel.workbooks.open)

Sub ImportWorksheet()
    Dim sourcefile, sourceloc, sourcetab As String
    sourcefile = "space flight.xlsx"
    sourceloc = "C:\Users\moish\OneDrive\Documents\space flight.xlsx"
    sourcetab = "Bodies"
    
    Destination = ActiveWorkbook.Name
    
    Workbooks.Open sourceloc
    
    Sheets(sourcetab).Copy Before:=Workbooks(Destination).Sheets(1)
    
    Windows(sourcefile).Activate
    ActiveWorkbook.Close SaveChanges:=False
    Windows(Destination).Activate
End Sub
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.