Run VBA function with a button

Question

I defined a VBA function that returns a filesize. Now I want to invoke it with a button that's calling a different macro. My expectation is that after running the macro it'll invoke my function at the very end. My problem is that when I put a formula into a cell it will return a current filesize only the moment I enter the formula. When I edit the file, save it and reopen, the =wbksize() will still display the filesize from before my edits.

So the purpose of this macro run by a button is to refresh the filesize value. Here's my attempt to do it.

function:

Function wbksize()
    myWbk = Application.ThisWorkbook.FullName
    wbksize = FileLen(myWbk)
End Function

refresh:

Worksheets("Sheet2").Range("K1").Calculate

The above doesn't seem to work :/

It looks like you are not triggering the execution of your macro/function. By default, the function will not be executed just because you Calculate the workbook/sheet. Functions that do Calculate like this are known as 'Volatile', and you can explicitly define them as such by adding Application.Volatile near the top of your Function. See more here: excel.tips.net/… — Simon Wray
– Simon Wray, Commented Apr 1, 2016 at 13:13
Alternatively, if you want to only execute your Function when the button is clicked, you can Assign your Function to your button - you should be able to right-click the button and select "Assign Macro" (assuming your button is a basic Form Control and not an ActiveX Control), then your Function name should be listed in the dialogue for you to select. — Simon Wray
– Simon Wray, Commented Apr 1, 2016 at 13:22
Functions return a value, and as such, cannot be called by a button (because there is nowhere to return the value to). You'd have to encapsulate it, like in Michal Wolinski's answer. — Vegard
– Vegard, Commented Apr 1, 2016 at 13:24
Application.Volatile also does not seem to work (in Excel 2013 anyway), and I suspect it has something to do specifically with FileLen. — Vegard
– Vegard, Commented Apr 1, 2016 at 13:25

Michał Woliński · Accepted Answer · 2016-04-01 13:21:56Z

1

Function works fine, but refreshing should call function.

Function wbksize() As String
    myWbk = Application.ThisWorkbook.FullName
    wbksize = Str(FileLen(myWbk))
End Function
Sub Refresh()
    Worksheets("Sheet2").Range("K1") = wbksize
End Sub

answered Apr 1, 2016 at 13:21

Michał Woliński

3462 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Kris Kozlowski Over a year ago

Michał - it seems as very reasonable solution, but I get Subscript out of range. I solved it with

Sub Refresh()          Dim wsToRefresh As Worksheet     Set wsToRefresh = Sheets(2)     Set rngToRefresh = wsToRefresh.Range("K1")      rngToRefresh = wbksize      End Sub

The problem is that when I run Sub that's pinned to the trigger button and there's a line there Call Refresh there is no change in Sheet2.K1. No matter if the cell contains =wbksize() or is empty. I'm really dumb about VBA so please be explicit. Thanks!

Michał Woliński Over a year ago

I get it changed. Check if you saved it as macro embeded or if error is in another part of code. If you want to call it when workbook is calculated, name it Private Sub Worksheet_Calculate() as indicated above.

Paul S · Accepted Answer · 2016-04-01 13:19:44Z

0

This may or may not help you in your situation....LINK

I have never needed to use this on excel but it maybe what your looking for, you can set custom functions as 'VOLATILE' which forces excel to run them whenever ANYTHING get calculated, again i have never needed to use this so i cannot comment on any drawbacks or anything but it looks like it may work in your case.

answered Apr 1, 2016 at 13:19

Paul S

1901 gold badge1 silver badge11 bronze badges

Comments

Simon Wray · Accepted Answer · 2016-04-01 13:46:13Z

0

I've tested these, and they both work fine. It depends on what you want your trigger to be: Changing the worksheet, or performing a Calculate on the worksheet.

Put either of these in your Worksheet. The first will trigger on Calculate, the second on Change.

Private Sub Worksheet_Calculate()
Dim lFileLength As Long
    Application.EnableEvents = False 'to prevent endless loop
    lFileLength = FileLen("\\MyFile\Path\AndName.XLS.XLS")
    ThisWorkbook.Sheets("Sheet1").Range("A1").Value = CStr(lFileLength)
    MsgBox "You changed THE CELL!"
    Application.EnableEvents = True
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Dim lFileLength As Long
    Application.EnableEvents = False 'to prevent endless loop
    lFileLength = FileLen("\\MyFile\Path\AndName.XLS")
    ThisWorkbook.Sheets("Sheet1").Range("B1").Value = CStr(lFileLength)
    MsgBox "You changed THE CELL!"
    Application.EnableEvents = True
End Sub

answered Apr 1, 2016 at 13:46

Simon Wray

1924 silver badges12 bronze badges

1 Comment

Kris Kozlowski Over a year ago

Simon, does your Worksheet_Calculate() assumes I have a static filename and path? My spreadsheet is often updated and the filename chages, because it consists version ID. Additionally, new versions are sent to different recipients and the filepath has to be checked dynamically, in which case I know that CELL("filename") is helpful.

Collectives™ on Stack Overflow

Run VBA function with a button

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related