Next visible column


Since function nextVisibleColumn uses some non-built VBA functions, they also must be included in your code for the function to work properly.

Otherwise the following error will occur: Compile error: Sub or Function not defined.

Required functions are listed below. You can get to each function's source code by clicking its name:

When adding the functions above to your VBA project, make sure you haven't done it before. If there are two different public functions with the same name in a single VBA project, the following compilation error is thrown: Compile error: Ambiguous name detected: function_name.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
'**********************************************************************************************************
' Name:                 nextVisibleColumn
' Author:               mielk | 2013-03-24
'
' Comment:              Returns the index number of the next visible column to the left or right from the
'                       given column.
'
' Parameters:
'   wks                 Worksheet where the search is processed.
'   initialCol          Initial column, where the search for the next visible column starts.
'   direction           Direction of the search for the next visible column.
'                       * The only available values for this parameter are xlToLeft and xlToRight.
'                       * Technically, the other constant values defined in xlDirection enumeration
'                         (xlUp and xlDown) can also be passed to this function. However, the function will
'                         return 0 in such case, since columns cannot be searched vertically.
'
' Returns:
'   Long                The number index of the next visible column in a specific direction (determined by
'                       the value of direction parameter) from a given initial column.
'
'                       The function returns 0, if there is no visible column to the left or right from a
'                       given column or if the value of direction parameter is other than xlToLeft or
'                       xlToRight.
'
'                       The index number of the very first visible column in a given Excel worksheet can be
'                       obtained by passing parameters initialRow = 0 and direction = xlToRight to this
'                       function.
'
' Exceptions:
'   IllegalSheetException           Thrown if the worksheet given to the function as the [wks] parameter
'                                   is damaged or has been closed and it is not possible to refer to its
'                                   rows or columns.
'
'
'
' --- Changes log -----------------------------------------------------------------------------------------
' 2012-04-05        mielk           Function created.
'**********************************************************************************************************
Public Function nextVisibleColumn(wks As Excel.Worksheet, initialCol As Long, _
                                          direction As XlDirection) As Long
    Const METHOD_NAME As String = "nextVisibleColumn"
    '------------------------------------------------------------------------------------------------------
    Dim intOffset As Integer
    '------------------------------------------------------------------------------------------------------


    'Checks if the given worksheet is valid and can be referred to. -------------------------------------|
    'If not, the code jumps to the label IllegalSheetException, where you can define your own           '|
    'error handling rules for this exception.                                                           '|
    If Not isSheetValid(wks) Then GoTo IllegalSheetException                                            '|
    '----------------------------------------------------------------------------------------------------|


    '----------------------------------------------------------------------------------------------------|
    Select Case direction                                                                               '|
        Case Excel.xlToLeft:    intOffset = -1                                                          '|
        Case Excel.xlToRight:   intOffset = 1                                                           '|
        Case Else                                                                                       '|
            nextVisibleColumn = initialCol                                                              '|
            GoTo ExitPoint                                                                              '|
    End Select                                                                                          '|
    '----------------------------------------------------------------------------------------------------|


    'Iterate through the columns to the left or right from the initial column and check if --------------|
    'they are visible or hidden.                                                                        '|
    nextVisibleColumn = initialCol                                                                      '|
    Do                                                                                                  '|
        nextVisibleColumn = nextVisibleColumn + intOffset                                               '|
        If Not wks.columns(nextVisibleColumn).Hidden Then Exit Do                                       '|
    Loop                                                                                                '|
    '----------------------------------------------------------------------------------------------------|



'==========================================================================================================
ExitPoint:
    Exit Function

'----------------------------------------------------------------------------------------------------------
IllegalSheetException:
    '(...)
    'Put your own error handling here for a case if the given worksheet has been closed or removed.

    GoTo ExitPoint

End Function