CODEWITHSUNDEEP
vbaexcelmacros
Kris Kozlowski
Kris Kozlowski

Reputation: 47

Run VBA function with a button

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 :/

Upvotes: 0

Views: 10905

Answers (3)

Simon Wray
Simon Wray

Reputation: 192

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

Upvotes: 0

Michał Woliński
Michał Woliński

Reputation: 346

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

Upvotes: 1

Paul S
Paul S

Reputation: 190

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.

Upvotes: 0

Related Questions