Link to home
Start Free TrialLog in
Avatar of Dale Fye
Dale FyeFlag for United States of America

asked on

Calling a UDF from a worksheet cell

Excel in O365

I have a public function (fnDebugging) defined in a code module in my workbook.

Public Function fnDebugging() as Boolean
    fnDebugging = true
End Function

Open in new window

When I deploy my workbook, I'll set this value to False, but while developing, I would like to use this function to display some messages on several worksheets when certain conditions are met.  But when I type:

=fnDebugging

Open in new window

in a cell, I get the #NAME? error message.


When I use the Fx wizard, and select UserDefined, the function is visible, but when I select it, it displays as:

='MyWorkbookName.xlsm'!fnDebugging()

Open in new window

Is there an easier and shorter way for me to make sure this works without all of the additional text of the workbook name?


ASKER CERTIFIED SOLUTION
Avatar of Martin Liss
Martin Liss
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Dale Fye

ASKER

@Martin,

I could swear I saved the code module and that did not work previously, but it is working now.



The difference between the first example you posted and my solution are the parentheses.
You might want to do this instead. When you want to start debugging, run the Debugging sub first. Then you can do things like the Test sub. You can create a shortcut to the Debugging sub by:
In Excel, press Alt+F8 to open the "Macro" dialog window. Select the macro, click ‘Options…’,  hold down the Shift key and type the letter D (or any other letter) and click ‘OK’. Then anytime you want to run the macro press Ctrl+Shift+D

Note that to turn off debugging, run the Debugging sub again.

Option Explicit

Public gbDebugging As Boolean
Sub test()
If gbDebugging Then
  MsgBox "debugging"
End If

End Sub
Sub Debugging()
gbDebugging = Not gbDebugging
End Sub

Open in new window

Thought of that, but wanted to be able to use it within a formula in a cell as well.
=IF(fnDebugging()=-1,"Some value", "")
= IF(fnDebugging()=-1,"Some message", "Some other message")