ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)
-   -   Need some comments on my Utility_Move class module. (https://www.excelbanter.com/excel-worksheet-functions/106244-need-some-comments-my-utility_move-class-module.html)

jchen

Need some comments on my Utility_Move class module.
 
Hello guys, I wrote a class module called Utility_Move. It allows me to move
the activecell around as if I am pressing up,down,left,right arrows. Please
give some comments on it. I hope there is a better one out there, if so,
please tell me where I can get it. Thank you.

About the code, Steps allows you to repeat the same action, negative Step
means step back. SkipHidden = True will skip hidden row or column.

And here is my code.
Public Enum emMove
eUp
eDown
eRight
eLeft
eUpEnd
eDownEnd
eRightEnd
eLeftEnd
eUpMost
eDownMost
eRightMost
eLeftMost
End Enum

' =============================Move Cell============================
Function Move(direction As emMove, Optional times As Long = 1, Optional
SkipHidden As Boolean = False) As Boolean
Select Case direction
Case emMove.eUp
Move = Up(times, SkipHidden)
Case emMove.eDown
Move = Down(times, SkipHidden)
Case emMove.eRight
Move = Right(times, SkipHidden)
Case emMove.eLeft
Move = Left(times, SkipHidden)
Case emMove.eUpEnd
Move = UpEnd(times, SkipHidden)
Case emMove.eDownEnd
Move = DownEnd(times, SkipHidden)
Case emMove.eRightEnd
Move = RightEnd(times, SkipHidden)
Case emMove.eLeftEnd
Move = LeftEnd(times, SkipHidden)
Case emMove.eUpMost
Move = UpMost(SkipHidden)
Case emMove.eDownMost
Move = DownMost(SkipHidden)
Case emMove.eRightMost
Move = RightMost(SkipHidden)
Case emMove.eLeftMost
Move = LeftMost(SkipHidden)
End Select
End Function

Private Function HasMovedProperly(direction As emMove, Column_or_Row As
Long) As Boolean
Select Case direction
Case emMove.eUp, emMove.eUpEnd, emMove.eLeft, emMove.eLeftEnd
HasMovedProperly = (ActiveCell.Row < Column_or_Row)
Case emMove.eUpMost, emMove.eLeftMost
HasMovedProperly = (ActiveCell.Row <= Column_or_Row)

Case emMove.eDown, emMove.eDownEnd, emMove.eRight, emMove.eRightEnd
HasMovedProperly = (ActiveCell.Row Column_or_Row)
Case emMove.eDownMost, emMove.eRightMost
HasMovedProperly = (ActiveCell.Row = Column_or_Row)
End Select
End Function

'================================================= ============================
' Move To Direction
Private Function OneStep(direction As emMove, Optional SkipHidden As Boolean
= False) As Boolean
OneStep = False
On Error GoTo Error
Do
Select Case direction
Case emMove.eUp
ActiveCell.Offset(-1, 0).Range("A1").Select
Case emMove.eDown
ActiveCell.Offset(1, 0).Range("A1").Select
Case Else
Exit Do
End Select
Loop Until SkipHidden = False Or ActiveCell.EntireRow.Hidden = False

Do
Select Case direction
Case emMove.eRight
ActiveCell.Offset(0, 1).Range("A1").Select
Case emMove.eLeft
ActiveCell.Offset(0, -1).Range("A1").Select
Case Else
Exit Do
End Select
Loop Until SkipHidden = False Or ActiveCell.EntireColumn.Hidden = False

OneStep = True
Exit Function
Error:
OneStep = False
End Function

Function Up(Optional Steps As Long = 1, Optional SkipHidden As Boolean =
False) As Boolean
If Steps < 0 Then Up = Down(Steps * -1): Exit Function
For i = 1 To Steps
If OneStep(eUp, SkipHidden) = False Then Up = False: Exit Function
Next
Up = True
End Function
Function Down(Optional Steps As Long = 1, Optional SkipHidden As Boolean =
False) As Boolean
If Steps < 0 Then Down = Up(Steps * -1): Exit Function
For i = 1 To Steps
If OneStep(eDown, SkipHidden) = False Then Down = False: Exit Function
Next
Down = True
End Function
Function Right(Optional Steps As Long = 1, Optional SkipHidden As Boolean =
False) As Boolean
If Steps < 0 Then Right = Left(Steps * -1): Exit Function
For i = 1 To Steps
If OneStep(eRight, SkipHidden) = False Then Right = False: Exit
Function
Next
Right = True
End Function
Function Left(Optional Steps As Long = 1, Optional SkipHidden As Boolean =
False) As Boolean
If Steps < 0 Then Left = Right(Steps * -1): Exit Function
For i = 1 To Steps
If OneStep(eLeft, SkipHidden) = False Then Left = False: Exit Function
Next
Left = True
End Function

'================================================= ============================
' Move To Direction End
Private Function OneStepEnd(direction As emMove, Optional SkipHidden As
Boolean = False) As Boolean
OneStepEnd = False
Do
Select Case direction
Case emMove.eUpEnd
tRow = ActiveCell.Row
Selection.End(xlUp).Select
OneStepEnd = HasMovedProperly(eUpEnd, CLng(tRow))
Case emMove.eDownEnd
tRow = ActiveCell.Row
Selection.End(xlDown).Select
OneStepEnd = HasMovedProperly(eDownEnd, CLng(tRow))
Case Else
Exit Do
End Select
Loop Until SkipHidden = False Or ActiveCell.EntireRow.Hidden = False

Do
Select Case direction
Case emMove.eRightEnd
tColumn = ActiveCell.Column
Selection.End(xlToRight).Select
OneStepEnd = HasMovedProperly(eRightEnd, CLng(tColumn))
Case emMove.eLeftEnd
tColumn = ActiveCell.Column
Selection.End(xlToLeft).Select
OneStepEnd = HasMovedProperly(eLeftEnd, CLng(tColumn))
Case Else
Exit Do
End Select
Loop Until SkipHidden = False Or ActiveCell.EntireColumn.Hidden = False
End Function

Function UpEnd(Optional Steps As Long = 1, Optional SkipHidden As Boolean =
False) As Boolean
If Steps < 0 Then UpEnd = DownEnd(Steps * -1): Exit Function
For i = 1 To Steps
If OneStepEnd(eUpEnd, SkipHidden) = False Then UpEnd = False: Exit
Function
Next
UpEnd = True
End Function
Function DownEnd(Optional Steps As Long = 1, Optional SkipHidden As Boolean
= False) As Boolean
If Steps < 0 Then DownEnd = UpEnd(Steps * -1): Exit Function
For i = 1 To Steps
If OneStepEnd(eDownEnd, SkipHidden) = False Then DownEnd = False:
Exit Function
Next
DownEnd = True
End Function
Function RightEnd(Optional Steps As Long = 1, Optional SkipHidden As Boolean
= False) As Boolean
If Steps < 0 Then RightEnd = LeftEnd(Steps * -1): Exit Function
For i = 1 To Steps
If OneStepEnd(eRightEnd, SkipHidden) = False Then RightEnd = False:
Exit Function
Next
RightEnd = True
End Function
Function LeftEnd(Optional Steps As Long = 1, Optional SkipHidden As Boolean
= False) As Boolean
If Steps < 0 Then LeftEnd = RightEnd(Steps * -1): Exit Function
For i = 1 To Steps
If OneStepEnd(eLeftEnd, SkipHidden) = False Then LeftEnd = False:
Exit Function
Next
LeftEnd = True
End Function

'================================================= ============================
' Move To Direction Most
Function UpMost(Optional SkipHidden As Boolean = False) As Boolean
tRow = ActiveCell.Row
Cells(1, ActiveCell.Column).Select
Do Until Trim(ActiveCell.FormulaR1C1) < "" And _
(SkipHidden = False Or ActiveCell.EntireRow.Hidden = False)
If Not DownEnd Then Cells(1, ActiveCell.Column).Select: UpMost =
False: Exit Function
Loop
UpMost = HasMovedProperly(eUpMost, CLng(tRow))
End Function
Function DownMost(Optional SkipHidden As Boolean = False) As Boolean
tRow = ActiveCell.Row
Cells(65536, ActiveCell.Column).Select
Do Until Trim(ActiveCell.FormulaR1C1) < "" And _
(SkipHidden = False Or ActiveCell.EntireRow.Hidden = False)
If Not UpEnd Then DownMost = False: Exit Function
Loop
DownMost = HasMovedProperly(eDownMost, CLng(tRow))
End Function
Function RightMost(Optional SkipHidden As Boolean = False) As Boolean
tColumn = ActiveCell.Column
Range("IV" + CStr(ActiveCell.Row)).Select
Do Until Trim(ActiveCell.FormulaR1C1) < "" And _
(SkipHidden = False Or ActiveCell.EntireColumn.Hidden = False)
If Not LeftEnd Then RightMost = False: Exit Function
Loop
RightMost = HasMovedProperly(eRightMost, CLng(tColumn))
End Function
Function LeftMost(Optional SkipHidden As Boolean = False) As Boolean
tColumn = ActiveCell.Column
Range("A" + CStr(ActiveCell.Row)).Select
Do Until Trim(ActiveCell.FormulaR1C1) < "" And _
(SkipHidden = False Or ActiveCell.EntireColumn.Hidden = False)
If Not RightEnd Then Range("A" + CStr(ActiveCell.Row)).Select:
LeftMost = False: Exit Function
Loop
LeftMost = HasMovedProperly(eLeftMost, CLng(tColumn))
End Function



All times are GMT +1. The time now is 07:50 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com