CODEWITHSUNDEEP
vbaexcelexcel-formula
JFrizz
JFrizz

Reputation: 123

How can I make a VBA function work upon opening the workbook

I created a function in vba to determine if a row is visible. The function returns 1 if visible and 0 if not visible. The function looks like this:

Function IsRowVisible(MyRange As Range) As Integer

    If (MyRange.EntireRow.Hidden = False) Then
        IsRowVisible = 1
    Else
        IsRowVisible = 0
    End If

End Function

I then have a column in my workbook where I refer to this function in a formula such as: =IsRowVisible(A1)

The formula returns #VALUE! until I run a macro that runs some other code or until I click the formula and hit Enter. Then it works fine.

I would like it to work like a normal function in Excel where a value is returned when you open the workbook. Where am I going wrong?

Thanks for your help!

Upvotes: 0

Views: 53

Answers (1)

Gary's Student
Gary's Student

Reputation: 96753

Does this improve things?:

Function IsRowVisible(MyRange As Range) As Integer
    Application.Volatile
    If (MyRange.EntireRow.Hidden = False) Then
        IsRowVisible = 1
    Else
        IsRowVisible = 0
    End If

End Function

This is probably a volatility issue. Your function may only be re-calculating when the value in A1 changes.

You may need to combine your function with a SUBTOTAL() function because SUBTOTAL() does recalculate when rows are hidden/unhidden.

Upvotes: 2

Related Questions