Workbook doesn't open from Excel Custom Function

Hi Guys,

So I have the below macro, which takes 3 pieces of data to 1.) open the correct file with the date and 2.) get the value at the row and column number based on the data entered into 'OU" and "Acct".  The result is the answer variable.  This is working.  But what I would like to do is put this into a function and be able to call it from a worksheet, passing in the three values.  When I try this, it won't open the workbook.  When it gets to that step, it just returns #VALUE!.

Any idea how I can make this work as a function?

Sub Macro1()
'
' Macro1 Macro
'

'
Dim ou As String ' Parameter 1
Dim acct As String ' Parameter 2
Dim mydate As String ' Parameter 3
Dim fn As String


Dim row As Double
Dim column As Double
Dim wb As Workbook
Dim ws As Worksheet
Dim OURange As Range
Dim AcctRange As Range

row = 0
column = 0
ou = "b"
acct = "1"
mydate = 20171215
fn = "Q:\Desktop Files 2015-03-31\test1" & mydate & ".xlsx"


Set wb = Workbooks.Open(fn) 'name of the with the info
Set ws = wb.Sheets("Sheet1")  'name of the worksheet with the info
Set OURange = ws.Range("D:D") 'column of the worksheet where the OU is
Set AcctRange = ws.Range("a1:zz1") 'row of the worksheet where the acct is
row = Application.WorksheetFunction.Match(ou, OURange, 0)
For Each c In AcctRange
    If c.Value2 = acct Then
        column = c.column
    End If
Next
If (column > 0) Then
    answer = ws.Cells(row, column).Value2
End If

End Sub

Open in new window

LVL 1
Elena QuinnAsked:
Who is Participating?
 
Martin LissOlder than dirtCommented:
Try this version. You'll need to change the path to the workbook in the Sheet1 Worksheet_Calculate event. Again the (changed) formula is in C1.
29074084a.xlsm
0
 
Wayne Taylor (webtubbs)Commented:
If your plan is to use the function in a cell, you can't do that. User Defined Functions are unable to open workbooks.
0
 
Fabrice LambertFabrice LambertCommented:
If your plan is to use the function in a cell, you can't do that. User Defined Functions are unable to open workbooks.
Wrong !
Almost anything can be done with VBA.

@Elena:
You wrote a procedure, not a function.
Precedures do not return any value, ehence why you don't get the expected result.
So change your procedure tu a function, and ensure a value is returned.
This is done by assigning a value to the function name.
Sample code:
Public Function myValue() As String
    myValue = "John Fitzgerald"
End Sub

Open in new window


Additional notes:
You seems to have undeclared variables wich is bad.
To prevent this, always make sure to use the "Option Explicit" statement at top of your modules.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Wayne Taylor (webtubbs)Commented:
Fabrice, I suggest you try opening a seperate workbook in a UDF and report back. Also reread the question. The OP knows they want a function but it's returning an error, which is expected due to not being able to open a workbook in a UDF.
0
 
Fabrice LambertFabrice LambertCommented:
I managed to do it, by instanciating a new excel application (instead of using the current workbook's one).
But there is an heavy performance cost.

I wouldn't suggest calling such function within a cell, as every time the workbook perform its calculations (wich is pretty much every time the user change something), the function is run.
Better have a button (or whatever control) somewhere in a worksheet (or in the ribbon) that will call the function.
Sample code:
Public Function myFunction() As String
    Dim app As Excel.Application
    Dim wb As Excel.Workbook
    Dim ws As Excel.Worksheet
    
    Set app = New Excel.Application
    Set wb = app.Workbooks.Open(ThisWorkbook.path & "\data.xlsx")
    Set ws = wb.Worksheets(1)
    myFunction = ws.Range("A4").Value
    Set ws = Nothing
    wb.Close savechanges:=False
    Set wb = Nothing
    app.Quit
    Set app = Nothing
End Function

Open in new window

0
 
Martin LissOlder than dirtCommented:
in my article about creating your own Excel formulas, in the "Doing the impossible" section I describe a method by which you can do what you want. In your case I suggest
1) Removing the three similar variables from your function and adding them plus a fourth in a code module.
Public gbOpen As Boolean
Public ou As String ' Parameter 1
Public acct As String ' Parameter 2
Public mydate As String ' Parameter 3

2) Add this code to your worksheet.
Private Sub Worksheet_Change(ByVal Target As Range)
    If gbOpen Then
        'open workbook here using the variables you added to the code module.
    End If
    gbOpen = False

End Sub

Open in new window


In your macro1 add this line after line 27 of the code you posted.
ActiveSheet.Range("A1") = ActiveSheet.Range("A1")

Open in new window

It may seem strange to do that but it will trigger the Change event without actually changing anything.
0
 
Elena QuinnAuthor Commented:
Hi Martin,

I want to return the value to a cell where I have entered a formula.  Like '=GetGL("b", 2, 20171215)'.  There will be other cells where I would need this as well.  So how do I pass the values to the function?

I guess I don't understand what we accomplish by doing what you are suggesting.

I also tried to call the macro from the function and open the file that way, but it didn't work.  It doesn't even work if the workbook is already open.  At the 'set wb = Application.Workbooks.Open(fn)' line, wb is still 'nothing'.
0
 
Martin LissOlder than dirtCommented:
Do the ou , acct and mydate variables map to "b", 2, 20171215?
0
 
Martin LissOlder than dirtCommented:
Try the attached workbook. Enter =GetGL(A1,"b", 2, 20171215) in any cell except A1. The function returns a #Value error because I'm not sure what you want its output to be.
29074084.xlsm
0
 
Elena QuinnAuthor Commented:
Yes, that is the mapping.  The date is in reference to which file to open.  The ou and acct are the row and column that cross references the value I am searching for.
test120171215.xlsx
0
 
Martin LissOlder than dirtCommented:
Did you try the workbook I posted? Was it anything close to what you wanted?
0
 
Elena QuinnAuthor Commented:
I can see where you are going with this.  However, after it opens the worksheet, it doesn't go any further.  I don't get to the 'set ws' line.

it just returns #VALUE!.

Like I said, I can't get it to work even if the workbook is already open.
0
 
Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.ScDirectorCommented:
Please take look on this:

This is illustrated in the following example.

VBA Function Procedure Example: Perform a Mathematical Operation on 3 Numbers
The following code shows an example of a simple VBA Function procedure that receives three arguments, each of which are 'Doubles' (double precision floating point numbers). The Function returns a further 'Double', which is the sum of the first two arguments, minus the third argument:

' Function to Add Two Numbers and Then Subtract a Third Number
Function SumMinus(dNum1 As Double, dNum2 As Double, dNum3 As Double) As Double
SumMinus = dNum1 + dNum2 - dNum3
End Function

The above very simple VBA Function procedure illustrates the way in which data arguments are supplied to a procedure. It is also seen that the Function procedure return type is defined as being a 'Double' (i.e. by the term "As Double" which is included after the Function arguments).
The above example also shows how the Function procedure result is stored in a variable that has the same name as the Function.

Calling VBA Function Procedures
If the above simple Function procedure is typed into a Module in the Visual Basic Editor, it will then be available to be called from other VBA procedures or to be used in the worksheets of your Excel workbook.
Calling a VBA Function Procedure From Within VBA
You can call a Function procedure from within your VBA program by simply assigning the Function to a variable. The following example shows a call to the simple SumMinus function that was defined above:
Sub main()
Dim total as Double
total = SumMinus(5, 4, 3)
End Sub

Calling a VBA Function Procedure From A Worksheet
You can call VBA Function procedures from an Excel Worksheet, in the same way as you can call any of the built-in Excel functions.
Therefore, you could call the SumMinus Function procedure by typing the following into any cell of your worksheet:
=SumMinus(10, 5, 2)

VBA Sub Procedures
The VBA editor recognises a Sub procedure, because the commands are positioned between the following start and end commands:
Sub
.
.
.
End Sub

VBA Sub Procedure Example 1: Center and Apply Font Size to a Selected Range of Cells
The following code shows an example of a simple VBA Sub procedure that applies formatting to the current selected cell range. The cells are formatted to be aligned centrally (both horizontally and vertically) and to have a user-supplied font size:
' Sub Procedure to Center and Apply a Supplied Font Size to the Selected Range
Sub Format_Centered_And_Sized(Optional iFontSize As Integer = 10)
Selection.HorizontalAlignment = xlCenter
Selection.VerticalAlignment = xlCenter
Selection.Font.Size = iFontSize
End Sub

The above example illustrates how Sub procedures perform actions but do not return values.
This example also includes the Optional argument, iFontSize. If iFontSize is not supplied to the Sub, then the default font size of 10 is used. However, if iFontSize is supplied to the Sub, then the current range is set to have the user-supplied font size.

VBA Sub Procedure Example 2: Center and Apply Bold Font to a Selected Range of Cells
The following code is similar to example 1, but instead of supplying a font size to the selected range, the cells are set to have a bold font. This example has been included to show a Sub procedure that does not receive any arguments:
' Sub Procedure to Center and Bold the Selected Range
Sub Format_Centered_And_Bold()
Selection.HorizontalAlignment = xlCenter
Selection.VerticalAlignment = xlCenter
Selection.Font.Bold = True
End Sub

Calling Excel VBA Sub Procedures
Calling a VBA Sub Procedure From Within VBA
You can call a VBA Sub procedure from your VBA program by typing the Call keyword, followed by the Sub name and then the Sub procedure arguments enclosed in brackets. This is shown in the example below:
0
 
Elena QuinnAuthor Commented:
Hi Martin,

Still getting #VALUE!.  I am now onto a new possible option, which is ExecuteExcel4Macro.  I can get what I need using an Index(match(match))), but I am still having trouble getting it to work in VBA with the ExecuteExcel4Macro.

In my spreadsheet, I can enter the below and get the correct answer - with or without the target file being open.
=INDEX('Q:\Desktop Files 2015-03-31\[test120171215.xlsx]Sheet1'!E3:L11, MATCH("d",'Q:\Desktop Files 2015-03-31\[test120171215.xlsx]Sheet1'!D3:D11,0), MATCH(4,'Q:\Desktop Files 2015-03-31\[test120171215.xlsx]Sheet1'!E1:L1,0))

Open in new window


I put all of this into a module to slowly build up the string to use with ExecuteExcel4Macro, and it still returns #Value!

Function LookUpGL(ou As String, acct As String, mydate As String)

Dim wb As Workbook
Dim ws As Worksheet


'Dim mac As String 'macro to run the ExecuteExcel4Macro
Dim path As String 'path where the file is located
Dim fn As String ' string to build the filename from the date
Dim shtName As String 'Sheet Name where the info is located
Dim SearchRange As String 'range where the target value will be found
Dim RowRange As String ' range where the row will be determined from
Dim ColumnRange As String ' range where the column will be determined from

Set wb = ActiveWorkbook
Set ws = wb.Worksheets("Sheet1")

'slowly build the string for the macro
'we are going to use an index-match-match
'=INDEX([test120171215.xlsx]Sheet1!E3:L11, MATCH("d",[test120171215.xlsx]Sheet1!D3:D11,0), MATCH(4,[test120171215.xlsx]Sheet1!E1:L1,0))
'from the above
path = "Q:\Desktop Files 2015-03-31\"
fn = "[test1" & mydate & ".xlsx]"
shtName = "Sheet1"
SearchRange = "E3:L11"
RowRange = "D3:D11"
ColumnRange = "E1:L1"

'now that all is defined we can build the macro string
mac = "Index(" & "'" & path & fn & shtName & "'" & "!"
mac = mac & SearchRange & ", Match("
mac = mac & """ & ou & """ & ","
mac = mac & "'" & path & fn & shtName & "'" & "!"
mac = mac & RowRange & ", 0),Match("
mac = mac & """ & acct & """ & ","
mac = mac & "'" & path & fn & shtName & "'" & "!"
mac = mac & ColumnRange & ", 0))"


callrow = Application.Caller.Row
callcol = Application.Caller.Column
ws.Range(Cells(callrow, callcol)).Value2 = ExecuteExcel4Macro(mac)
End Function

Open in new window


The last line doesn't work.  I still end up with #VALUE! in the cell.

This seems like it shouldn't be this hard.
0
 
Martin LissOlder than dirtCommented:
If the workbook open then I believe I've answered this question. So please ask a new question for the rest.
0
 
Elena QuinnAuthor Commented:
Thanks Martin!
0
 
Martin LissOlder than dirtCommented:
When you do post the new question it would really help if you could post a copy (sanitized if necessary) of the workbook you want to open.

In any case you’re welcome and I’m glad I was able to help.

If you expand the “Full Biography” section of my profile you’ll find links to some articles I’ve written that may interest you.

Marty - Microsoft MVP 2009 to 2017
              Experts Exchange MVE 2015
              Experts Exchange Top Expert Visual Basic Classic 2012 to 2017
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.