0

I am working on the VBA code development on Excel and wanted to ask if we can call a function from a cell.

So for the Excel, we can write functions

  • in a sheet
  • in the workbook
  • in a module

Let assume that I write a function like this:

Public Function Area(x As Double, y As Double) As Double
    Area = x * y
End Function

Whenever I put the function in a module, I can call that function in a cell using the =Area(x,y) and the Area becomes available in Excel's function list. However, when I put the same Area function in a sheet or the workbook, =Area(x,y) doesn't work. I have tried =Sheet1!Area(x,y) or =Book1!Area(x,y), but all of them returned #NAME. I have checked on the net and here but couldn't find a way to use a function in a sheet/workbook in a cell.

Can you please let me know if it is possible or not? And if it is, can you please provide an example of how to call the function from a cell?

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
tempx
  • 408
  • 2
  • 4
  • 15
  • I have checked the provided link which says it is not possible, but on the other two links it is stated that a UDF can be put in a sheet or workbook: https://stackoverflow.com/questions/12955461/what-difference-does-it-make-if-one-runs-a-vba-code-in-sheets-in-thisworkboo and https://stackoverflow.com/questions/3255478/put-excel-vba-code-in-module-or-sheet . Thus I believe putting UDFs in a sheet or workbook is possible but I am still not sure how those functions can be used. – tempx Jun 04 '20 at 16:28
  • 1
    Not possible for UDFs that are used in a cell... see [this comment](https://stackoverflow.com/questions/12955461/what-difference-does-it-make-if-one-runs-a-vba-code-in-sheets-in-thisworkboo#comment17565610_12955737). Hence this is a duplicate and the answer is "it's not possible." – BigBen Jun 04 '20 at 16:34

0 Answers0