VBA function not hidding columns when used in an excel cell

Question

I am trying to use the functions below to hide columns

Function SCPArgsShowOnly() As Boolean
    Dim sColsToHide As String
    sColsToHide = "E:I,M:N"
    hideCols sColsToHide
    SCPArgsShowOnly = True
End Function

'======================================================
Sub hideCols(sCols As String)
    Dim sTemp() As String, allCols As String
    sTemp = Split(sCols, ",")
    allCols = "A:N"

    With Sheets("Functions")
        .Columns(allCols).Hidden = False

        For i = LBound(sTemp) To UBound(sTemp)
            .Columns(sTemp(i)).Hidden = True
        Next
    End With
End Sub

It works fine when I run it through the debugger window. But it only returns true when I use it in a cell like this = SCPArgsShowOnly()

What am I missing?

how do you call it if not from debugger window? do you have an autostart macro for your excel file? — nabuchodonossor
– nabuchodonossor, Commented Jan 31, 2017 at 7:44
How do you call SCPArgsShowOnly() ? It does return true every time. — SickDimension
– SickDimension, Commented Jan 31, 2017 at 7:50
Shai is right it can't be done with a UDF embedded in the worksheet - but maybe when it's enabled by a button? — Amorpheuses
– Amorpheuses, Commented Jan 31, 2017 at 8:08
As Shai says a standard UDF can't interact with the sheet. But here's a workaround — chris neilsen
– chris neilsen, Commented Jan 31, 2017 at 8:27

Patrick Honorez · Accepted Answer · 2017-01-31 08:45:43Z

2

Excel does not allow calling functions that DO something from a cell.
Only functions that just return a value can be used, and that seems quite logical.
Imagine your columns appearing or disappearing whenever an entry in the sheet triggers a recalc ?

answered Jan 31, 2017 at 8:45

Patrick Honorez

25.4k12 gold badges97 silver badges163 bronze badges

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

Collectives™ on Stack Overflow

VBA function not hidding columns when used in an excel cell

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related