none
referring from function in xlsb to cells in another workbook RRS feed

  • Question

  • I have tried to centralize a lot of vba code (functions mainly) in a personal workbook (xlsb file). This workbook typically has no worksheets of itself. The functions are called from worksheets in many different workbooks and the code in my personal workbook should refer to cells in these worksheets.

    So far, I have only been able to refer to external workbooks and worksheets manually by typing their names. This is, however, ruining the context independence of the code in my personal workbook. I would like to pass the context (workbook, worksheet, selection) along with the function call from the "other" worksheet, if necessary via public variables. The code in my personal workbook should be fully capable of reading from and writing to cells in different workbooks.

    I have seen many people looking for a solution to this problem. For some length of time, I have not been able to find one. Not knowing a solution really blocks me to develop a decent application architecture with Excel. Please help me out!

    Many thanks, Han Schouten

    Wednesday, August 6, 2014 9:30 AM

Answers

  • You can pass a range as an argument to your function, e.g.

    Function MyFunc(MyRange As Range) As Double
        Dim MyCell As Range
        MyFunc = 1
        For Each MyCell In MyRange
            ' Perform a calculation
            MyFunc = MyFunc * (1 + MyCell.Value)
        Next MyCell
    End Function

    You can call this in a worksheet formula as follows:

    =Personal.xlsb!MyFunc(A1:A4)

    or

    =Personal.xlsb!MyFunc(OtherSheet!A1:A4)

    or

    =Personal.xlsb!MyFunc([OtherWorkbook.xlsx]OtherSheet!A1:A4)


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Wednesday, August 6, 2014 2:54 PM

All replies

  • You can pass a range as an argument to your function, e.g.

    Function MyFunc(MyRange As Range) As Double
        Dim MyCell As Range
        MyFunc = 1
        For Each MyCell In MyRange
            ' Perform a calculation
            MyFunc = MyFunc * (1 + MyCell.Value)
        Next MyCell
    End Function

    You can call this in a worksheet formula as follows:

    =Personal.xlsb!MyFunc(A1:A4)

    or

    =Personal.xlsb!MyFunc(OtherSheet!A1:A4)

    or

    =Personal.xlsb!MyFunc([OtherWorkbook.xlsx]OtherSheet!A1:A4)


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Wednesday, August 6, 2014 2:54 PM
  • Thanks Hans, for your quick reply. I will try this out today.

    Just to state my objective more clearly: I am after the simplest, most efficient solution to reduce maintenance. Therefore, a single point of storage for each shared macro or function is a prerequisite. Excel seems a quite hostile environment to achieve this objective.

    Yesterday - waiting for a reply - I have tried out the only alternative I know: Excel add-in (xlam). This would be simplest solution, but in the past there has been a problem and I have forgotten which. After some playing around, I remember.

    To rule out all doubts: I have properly installed the add-in. When I open the Visual Basic Editor, I can see and edit all macro's and functions. I can run every macro and function with the debugger.

    However, the macro's in my add-in don't show-up in the "Macro" pop-up, after I press the "Macro's" button under the "Developer" tab in the Excel ribbon. I have tried the only thing I could imagine: bringing down the security level with the "Macro security" button. This is neither the problem, nor the solution!

    By the way, Internet appears to be swamped with developers stepping into this pitfall. The same question over and over again, but not a single useful answer. Questions go back many years and ditto versions. Is this a bug, or is this a feature? Is there a work-around? Why is this so difficult?

    Many people, including myself, will be grateful for a qualified opinion about this issue!

    Regards, Han Schouten

    Thursday, August 7, 2014 8:15 AM
  • I think this is by design. If you have many add-ins, and if their macros were listed in the Macros dialog, it would become very crowded. An add-in is supposed to provide an interface to its functionality through a custom toolbar, custom ribbon tab/group, and/or custom keyboard shortcuts.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Thursday, August 7, 2014 1:48 PM
  • Dear Hans,

    To make the above picture complete: everything now works as it should.

    Initially, I couldn't get "Personal.xlsb" reinstalled. This file resides in the right directory "C:\Users\<user name>\AppData\Roaming\Microsoft\Excel\XLSTART". However, the file had the "hidden" and "read only" attributes set. Moreover, in the meantime, it turned out to contain several errors that caused compilation errors. After mending all this, the Personal Macro workbook loaded as soon as you opened another Excel workbook.

    I experienced a few troubles with "ThisWorkbook" in the context of the Personal Workbook macro's. I had to replace "ThisWorkbook.Sheets(" by "Worksheets(". Also, I had to replace "ThisWorkbook.Name" with "ActiveSheet.Parent.Name" several times. The quintessence is, that you should never use "ThisWorkbook" in macro's that are intended for a Personal Workbook. It will inevitable point at "Personal.xlsb".

    The calling context of the "WorkSheets" collection and the "ActiveSheet" object, however, remain in force, irrespective of where your macro's are stored.

    Yet, I sincerely would like to know what went wrong with my Excel add-ins.

    Regards, Han Schouten


    Thursday, August 7, 2014 2:03 PM