0

I am trying to write an Excel user function in VBA that will allow a user to add a button wherever they need.
I already have a VBA routine that will ping an IP address shown in an excel cell. Suppose a user has spreadsheet with IP addresses in column A. I want to give them the ability to enter a function into e.g. B1: =PING (A1) and this will create a button in C1 called “START PING” which when pressed runs this ping routine on the IP address in A1. Depending on the result, the fill color of A1 can be set to be Green or Red.

I have a function that works (i.e. it adds a button) when I'm in debug mode but not when used in a spreadsheet. I have reduced my code to the basics:

Function AddButton(c As String) As Byte
  ActiveSheet.Buttons.Add Range(c).left, Range(c).top, Range(c).width, Range(c).height
  AddButton = 1  ‘just need the function to return a value
End Function


Sub test1()
  Dim ret As Byte
  ret = AddButton("B1")
End Sub

When I use Debug to step into “test1()” everything works as I expect : I get a button at cell “B1”. But, if I enter =AddButton(“B1”) into a cell on spreadsheet, I get a “#VALUE” error and no button is created.

Because of the #VALUE error I tried changing the Function to be ... "As Variant" but no change in behavior. And because it works correctly when I step through in Debug mode, I added a pause to the function with: Application.Wait (Now + TimeValue("0:00:01")), but this also did not help.

Looking though other Q&A related to: "... only works in Debug mode" I see that a lot of time the error is due to not fully defining the Excel range. But I think that "ActiveSheet...." defines everything?

I know that I could provide a template with buttons already in place, and that adding buttons is not what Excel Functions are designed for. But it would provide users a lot of flexibility in arranging their spreadsheets however they want. Thank you for any assistance in making this work.

Improve this question
4
  • Maybe this is an answer for your question Value error... Commented Jan 14 at 5:36
  • 1
    I think you have a fundamental misunderstanding about the purpose of UDFs. The purpose of an "User Defined Function" is to calculate something. It may not modify anything on the sheet, and it is called whenever Excel thinks it needs to be called. If the worksheet is recalculated 10 times, you would end up with 10 buttons. Commented Jan 14 at 8:52
  • 1
    The documentation explicitly states “Certain kinds of statements, such as statements that select and format ranges, are excluded from custom functions.” — adding buttons is one of those kinds of functions, because it changes the structure of the workbook, rather than simply returning data. However: you can use the HYPERLINK function to call a macro for similar functionality Commented Jan 14 at 11:24
  • 1
    Thanks to all who replied. I had a feeling that using UDFs to create a button was not allowed, but was hoping to find a workaround. Thanks for pointing me to some things I can try. Commented Jan 14 at 21:56
Related questions
1
Excel VBA code only works in debug mode
0
Excel 2016 VBA Workbooks.Open Loses Debug Step Mode.. revisited
1
From EXCEL VBA Click Button on Web
Load 4 more related questions Show fewer related questions

0

Reset to default

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.