How to Call VBA Function from Excel Cells

Question

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

User · Answer

Here s the answer  Steps to follow    Open the Visual Basic Editor  In Excel  hit Alt F11 if on Windows  Fn Option F11 if on a Mac  Insert a new module  From the menu  Insert -  Module  Don t skip this    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  Then use it in any cell like you would any other function   findArea B12 C12

User · Answer

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   For the sample given you don t actually need VBA   This formula will work    C  Users UserName Desktop  TestSample xlsx Sheet1   B 2   Use a rather messy work around  See this answer You can use ExecuteExcel4Macro or OLEDB

User · Answer

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

[vba] How to Call VBA Function from Excel Cells?

Examples related to vba

Examples related to excel