'********************************************************************************************************** ' 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 '********************************************************************************************************** ' Name: isSheetValid ' Author: mielk | 2012-11-16 ' ' Comment: Function to check if the given Excel worksheet is valid and you can refer to its ' properties and methods without errors. ' ' Using this function is very helpful since it lets you avoid ' Run-time error '-2147221080 (800401a8)': Automation error. ' This error is generated, when the code tries to refer to a property or a method ' of a Sheet type variable, but the sheet pointed by this variable had been deleted ' before or the Excel workbook containing this sheet had been closed. ' ' Parameters: ' wks The Excel worksheet to be checked. ' ' Returns: ' Boolean True - if the given worksheet is valid and you can refer to it without any errors. ' False - if the given worksheet is invalid, that means it is corrupted or had been ' closed or deleted before this method has been called and referring to it ' will raise an error. ' ' ' --- Changes log ----------------------------------------------------------------------------------------- ' 2012-11-16 mielk Function created. '********************************************************************************************************** Public Function isSheetValid(wks As Excel.Worksheet) As Boolean Const METHOD_NAME As String = "isSheetValid" '------------------------------------------------------------------------------------------------------ Dim strSheetName As String '------------------------------------------------------------------------------------------------------ On Error Resume Next strSheetName = wks.name 'Check method is very easy - if the name of the given worksheet has been assigned to the variable '[strSheetName], this worksheet is valid and you can refer to it. Otherwise, error would be generated 'and step [strSheetName = wks.name] would be skipped because of [On Error Resume Next] statement above. isSheetValid = VBA.Len(strSheetName) End Function