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, Jan 31 '17 at 07:44
How do you call `SCPArgsShowOnly()` ? It does return true every time. — Han Soalone, Jan 31 '17 at 07: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, Jan 31 '17 at 08:08
As Shai says a standard UDF can't interact with the sheet. But here's a [workaround](http://stackoverflow.com/a/23437280/445425) — chris neilsen, Jan 31 '17 at 08:27

score 2 · Answer 1 · answered Jan 31 '17 at 08:45

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 '17 at 08:45

iDevlop

24,841
11
90
149

VBA function not hidding columns when used in an excel cell

1 Answers1