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