Avatar of Dale Fye
Dale Fye
Flag 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?


Microsoft ExcelMicrosoft Office

Avatar of undefined
Last Comment
Dale Fye

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Martin Liss

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Dale Fye

ASKER
@Martin,

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



Martin Liss

The difference between the first example you posted and my solution are the parentheses.
Martin Liss

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

Your help has saved me hundreds of hours of internet surfing.
fblack61
Dale Fye

ASKER
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")