Assign function to button

**DMA** · 07-09-2017, 11:25 AM

Hi. I've spent a couple days already trying to figure out how to assign a function in a module to a button on a worksheet.
I don't know VBA but i looked around trying to understand how to call the function from a subroutine so I could assign it to a form control button.
Then I tried to do the same with and ActiveX button, same result. The debugger game me different errors as i tried with different combinations.
The function (currency exchanger) works just fine. It starts like this:

Please Login or Register  to view this content.

And tried calling it mostly using this:

Please Login or Register  to view this content.

I also tried with Public and Private Subs and putting the function in the worksheet but I bet it requires more than that. Maybe declaring some variable?

**Norie** · 07-09-2017, 11:27 AM

You can't assign a function to a button.

What is it you actually want the button to do?

**DMA** · 07-09-2017, 11:31 AM

Hi Norie. I just want to execute the function to get exchange rates updated as needed.

**AliGW** · 07-09-2017, 11:32 AM

Just create a button and assign the macro to it. Or am I missing something?

**DMA** · 07-09-2017, 11:35 AM

The function can't be assigned as a macro. Not knowing VBA I can't tell you why but the function does not appear as an option. It is not considered a macro unless it is inside a subroutine (I think).

**xladept** · 07-09-2017, 11:59 AM

You don't call a function like that - you need something like:

Please Login or Register  to view this content.

**Norie** · 07-09-2017, 12:20 PM

Perhaps CurrEx shouldn't be a function.

How does it normally work?

**mikerickson** · 07-09-2017, 12:26 PM

Buttons are for doing something.
Functions return a value, but they don't do anything. (Which is why Excel allows macros to be assigned to a button, but not functions.)

A button that does "Calculate the value" is kind of useless.
A button that does "Calculate the value. Put that value in cell C1" is useful.

When the user presses the button what changes do you want on the worksheet?

**DMA** · 07-09-2017, 12:30 PM

Thanks xladept. The function allows you to type what currency ISO code to use, for example USD and GBP.
I'm guessing the code you gave me implies I would need to set the two currencies in the function instead of being able to choose one on the sheet. Am I understanding it right?

**DMA** · 07-09-2017, 12:35 PM

Mikerickson, what I was trying to accomplish was not exactly calculating the value, but more like refresh the values selected in the sheet. USD to GBP for example.

**DMA** · 07-09-2017, 12:44 PM

Norie, the reason I'm using a function is because I found the code and it does what I needed it to do.
I don't know VBA so I'm working with what I have. But in this case I would prefer to have something that could be assigned to a button.

I have it set up so it would either get the exchange rate between two currencies, entering the ISO code. Or converting a certain amount from one currency to the other.
Right now the only way to do it is to enter the ISO code again, meaning changing the value of the cell. But if I just want to refresh the current values on the sheet it would be easier to just press a Refresh button.

**Norie** · 07-09-2017, 12:44 PM

If you are already using the formula in cells on the worksheet they should automatically recalculate/refresh when a change is made on the sheet.

If that's not happening there might be a problem with your function.

**DMA** · 07-09-2017, 12:53 PM

It does that. I just edited my previous post explaining how it works.

**xladept** · 07-09-2017, 01:00 PM

Yeah - you must specify both arguments - you're welcome.

**xlnitwit** · 07-10-2017, 03:28 AM

Hi,

You can in fact assign a function to a button but there is usually little point to it which, I assume, is why it is not made obvious. Here I don't see how it could help.

I suspect you might simply make the function volatile- add

Please Login or Register  to view this content.

after the function declaration line. You might also add a button that simply forces recalculation of the application.

**DMA** · 07-10-2017, 07:14 AM

xlnitwit, The volatile function is an interesting solution, except in this case, because of the web query, it creates a long delay on any change to the sheet.
I guess the best option in this case will be the button to force recalculation.
Thank you!

**xlnitwit** · 07-10-2017, 07:20 AM

You may be able to use the Range.Calculate method rather than calculating the entire workbook or, if you have multiple such function calls, simply link them all to one controlling cell and edit that value instead. Thus rather than a simple
=CurrEx(A2,B2)
type call, you can use
=IF($A$1=1,CurrEx(A2,B2),"")
in each call and then you need only re-enter the 1 in A1 to update all the UDF calls.

**DMA** · 07-10-2017, 09:40 AM

That's a clever solution. I guess I'll use that one.
Thanks for the help!

Assign function to button

LinkBack

Thread Tools

Rate This Thread

Display

Assign function to button

Re: Assign function to button

Re: Assign function to button

Re: Assign function to button

Re: Assign function to button

Re: Assign function to button

Re: Assign function to button

Re: Assign function to button

Re: Assign function to button

Re: Assign function to button

Re: Assign function to button

Re: Assign function to button

Re: Assign function to button

Re: Assign function to button

Re: Assign function to button

Re: Assign function to button

Re: Assign function to button

Re: Assign function to button

Thread Information

Users Browsing this Thread

Similar Threads

Assign a Private Sub to a button

Custom Function to Create and Assign Button

Assign Macro to Button

Assign Macro to a Button

Cannot assign macro to button....HELP!!!

Cannot assign a hyperlink to a button

[SOLVED] How can I assign a symbol to a button (like the $ button)

Bookmarks

Bookmarks

Posting Permissions