My mini Utility_Move class, please have some suggestions.

Hello guys, I made a class called Utility_Move. Please give me some suggestions. If there is a better, cleaner, more performance way than mine, please tell me, thank you.

'Motive: Sometimes it is eaiser to debug when you use ActiveCell as current data record or parameter.
' When the macro stopped by exception or stop sign, you can determine the running progress by ActiveCell.
' And it is easier to say Up(5) instead of offset the row index, for me at least.

'Summary: The Utility_Move class introduce 3 sets of functions.
'First set, Up-Down-Right-Left simulate the key stroke of arrow keys.
'   Additionally, Steps parameter allows you to repeat number of seps to that direction.
'   Negative steps also means that you are stepping backward.
'   The SkipHidden parameter skips hidden cells. When true, it acts like arrow keys that
'   skips hidden rows by filter. When false, it will not skip hidden rows and select hidden cells.
'Second set, UpEnd-DownEnd-RightEnd-LeftEnd simulate arrow keys pressed after the End key.
'   It offers same parameters as the first set, and behave the same.
'Third set, UpMost-DownMost-RightMost-LeftMost will locate the
'   very last non-empty cell in that direction. Spaces are treated as non-empty cell.
'   Features SkipHidden feature, when true, only select the visible last cell.

Public Enum emMove
    eUp = -1
    eDown = 1
    eLeft = -2
    eRight = 2

    eUpEnd = -4
    eDownEnd = 4
    eLeftEnd = -8
    eRightEnd = 8

    eUpMost = -16
    eDownMost = 16
    eLeftMost = -32
    eRightMost = 32
End Enum

' =============================Move Cell============================
Function Move(Direction As emMove, Optional Steps As Long = 1, Optional SkipHidden As Boolean = False) As Boolean
    Select Case Direction
        Case emMove.eUp
            Move = Up(Steps, SkipHidden)
        Case emMove.eDown
            Move = Down(Steps, SkipHidden)
        Case emMove.eRight
            Move = Right(Steps, SkipHidden)
        Case emMove.eLeft
            Move = Left(Steps, SkipHidden)
        Case emMove.eUpEnd
            Move = UpEnd(Steps, SkipHidden)
        Case emMove.eDownEnd
            Move = DownEnd(Steps, SkipHidden)
        Case emMove.eRightEnd
            Move = RightEnd(Steps, SkipHidden)
        Case emMove.eLeftEnd
            Move = LeftEnd(Steps, 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

'=============================================================================
' 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

Private Function MoreSteps(Direction As emMove, Optional Steps As Long = 1, Optional SkipHidden As Boolean = False) As Boolean
    If Steps < 0 Then Direction = Direction * -1: Steps = Steps * -1
    For i = 1 To Steps
        If OneStep(Direction, SkipHidden) = False Then MoreSteps = False: Exit Function
    Next
    MoreSteps = True
End Function

Function Up(Optional Steps As Long = 1, Optional SkipHidden As Boolean = False) As Boolean
    Up = MoreSteps(eUp, Steps:=Steps, SkipHidden:=SkipHidden)
End Function
Function Down(Optional Steps As Long = 1, Optional SkipHidden As Boolean = False) As Boolean
    Down = MoreSteps(eDown, Steps:=Steps, SkipHidden:=SkipHidden)
End Function
Function Left(Optional Steps As Long = 1, Optional SkipHidden As Boolean = False) As Boolean
    Left = MoreSteps(eLeft, Steps:=Steps, SkipHidden:=SkipHidden)
End Function
Function Right(Optional Steps As Long = 1, Optional SkipHidden As Boolean = False) As Boolean
    Right = MoreSteps(eRight, Steps:=Steps, SkipHidden:=SkipHidden)
End Function

'=============================================================================
' Move To Direction End
Private Function OneStepEnd(Direction As emMove, Optional SkipHidden As Boolean = False) As Boolean
    OneStepEnd = True
    Do
        Select Case Direction
            Case emMove.eUpEnd
                If ActiveCell.Row = 1 Then OneStepEnd = False: Exit Function
                Selection.End(xlUp).Select
            Case emMove.eDownEnd
                If ActiveCell.Row = Rows.Count Then OneStepEnd = False: Exit Function
                Selection.End(xlDown).Select
            Case Else
                Exit Do
        End Select
    Loop Until SkipHidden = False Or ActiveCell.EntireRow.Hidden = False

    Do
        Select Case Direction
            Case emMove.eLeftEnd
                If ActiveCell.Column = 1 Then OneStepEnd = False: Exit Function
                Selection.End(xlToLeft).Select
            Case emMove.eRightEnd
                If ActiveCell.Column = Columns.Count Then OneStepEnd = False: Exit Function
                Selection.End(xlToRight).Select
            Case Else
                Exit Do
        End Select
    Loop Until SkipHidden = False Or ActiveCell.EntireColumn.Hidden = False
End Function

Private Function MoreStepsEnd(Direction As emMove, Optional Steps As Long = 1, Optional SkipHidden As Boolean = False) As Boolean
    If Steps < 0 Then Direction = Direction * -1: Steps = Steps * -1
    For i = 1 To Steps
        If OneStep(Direction, SkipHidden) = False Then MoreStepsEnd = False: Exit Function
    Next
    MoreStepsEnd = True
End Function

Function UpEnd(Optional Steps As Long = 1, Optional SkipHidden As Boolean = False) As Boolean
    UpEnd = MoreStepsEnd(eUpEnd, Steps:=Steps, SkipHidden:=SkipHidden)
End Function
Function DownEnd(Optional Steps As Long = 1, Optional SkipHidden As Boolean = False) As Boolean
    DownEnd = MoreStepsEnd(eDownEnd, Steps:=Steps, SkipHidden:=SkipHidden)
End Function
Function LeftEnd(Optional Steps As Long = 1, Optional SkipHidden As Boolean = False) As Boolean
    LeftEnd = MoreStepsEnd(eLeftEnd, Steps:=Steps, SkipHidden:=SkipHidden)
End Function
Function RightEnd(Optional Steps As Long = 1, Optional SkipHidden As Boolean = False) As Boolean
    RightEnd = MoreStepsEnd(eRightEnd, Steps:=Steps, SkipHidden:=SkipHidden)
End Function

'=============================================================================
' Move To Direction Most
Private Function OneStepMost(Direction As emMove, Optional SkipHidden As Boolean = False) As Boolean
    OneStepMost = False
    Select Case Direction
        Case emMove.eUpMost
            Cells(1, ActiveCell.Column).Select
        Case emMove.eDownMost
            Cells(Rows.Count, ActiveCell.Column).Select
        Case emMove.eLeftMost
            Cells(ActiveCell.Row, 1).Select
        Case emMove.eRightMost
            Cells(ActiveCell.Row, Columns.Count).Select
        Case Else
            Exit Function
    End Select

    If ActiveCell.FormulaR1C1 = "" Then OneStepEnd Direction, SkipHidden:=SkipHidden
    If ActiveCell.FormulaR1C1 = "" Then
        If Direction = eUpMost Then Cells(1, ActiveCell.Column).Select
        If Direction = eLeftMost Then Cells(ActiveCell.Row, 1).Select
        OneStepMost = False
    Else
        OneStepMost = True
    End If
End Function

Function UpMost(Optional SkipHidden As Boolean = False) As Boolean
    UpMost = OneStepMost(eUpMost, SkipHidden:=SkipHidden)
End Function
Function DownMost(Optional SkipHidden As Boolean = False) As Boolean
    DownMost = OneStepMost(eDownMost, SkipHidden:=SkipHidden)
End Function
Function LeftMost(Optional SkipHidden As Boolean = False) As Boolean
    LeftMost = OneStepMost(eLeftMost, SkipHidden:=SkipHidden)
End Function
Function RightMost(Optional SkipHidden As Boolean = False) As Boolean
    RightMost = OneStepMost(eRightMost, SkipHidden:=SkipHidden)
End Function

Microsoft ISV Community Center Forums1

Sorry man, I don't have a clue what you're trying to do with this code.

You say it's helpful when you're debugging VBA code to know what cell the user has just exited; if they've edited that cell's contents, it's as simple as grabbing the Target parameter from the Change event handler. If they haven't, you can grab the new address from the SelectionChange event.

It looks like you're duplicating functions that Excel already implements.

Hello, nvm about the debuging part. It is just about the code convention. If I use Range(Address).FormulaC1R1 as a parameter of a function, I won't be able to know the value of that parameter unless I use extra msgbox command. But if I use Range(Address).Select first, then, ActivelCell..FormulaC1R1, I will be able to refer the value right from the worksheet. It is eaiser to debug since I don't have to write extra msgbox. But as you can see, I use Utility_Move instead of Range(Address). I am just lazy to wrte offset.

You said mine are duplicate functions of Excel functions. Can you point out the equivelent functions I have trouble finding them, that's why I write my own. It will be the best to use Excel functions because I am not really satisfied with my own.

Thank you.

HI,

I'll have a look at this over the weekend.

Thank you.

Hi it's me,

I've had a look but time has been against me this weekend so I haven't had the time I would have liked. Your code does look slighty lengthy for the tasks involved. Think it could be reduced although that was just my first impression and that could be proved wrong at a closer inspection.

It will be Wedensday before I can have a better look.

It's me again,

Your coding is pretty good. It's very consistant and Iike that but there are a few improvements you could make. Your code looks to have groups of 4 similar functions. I'll take leftend and rightend as an example. Basically these functions have the same logic or at least there are some similarities between the functions, it's just the direction that changes.

These show what I'm getting at, they are very similar.....

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

It would be good if you could reduce them to one function which you would do by passing the direction into the method removeing the need to specify each direction as a seperate function.

Function MoveEnd(Direction as emMove, Optional Steps As Long = 1, Optional SkipHidden As Boolean = False) As Boolean
    If Steps < 0 Then MoveEnd = MoveEnd(OppositeDirection(Direction), Steps * -1): Exit Function
    For i = 1 To Steps
        If OneStepEnd(Direction , SkipHidden) = False Then MoveEnd= False: Exit Function
    Next
    MoveEnd= True
End Function

This uses a programming technique called recursion where a method calls itself. You could end up in an infinite loop though, which would happen in your original code too, where RightEnd calls LeftEnd which calls RightEnd that calls LeftEnd and so on, you'll need to test that this doesn't happen, I'm sure it won't but you never know, for example what happens if Steps = 0.

You will need another function that returns the opposite direction of a move to make this work.

Function OppositeDirection(direction As emMove) As emMove
    Select Case direction
        Case emMove.eUp
            OppositeDirection = emMove.eDown
        Case emMove.eDown
            OppositeDirection = emMove.eUp
        Case emMove.eRight

End Select
End Function

These changes are completely optional... it would reduce 12 functions down to 4 but would change the overall way you called the module. hope that some help anyway.

Thank you very much. I am going to re-organize my code and update the thread.

About the recursion stuff, yeah, it will never happen because it only call method when steps is negative and I convert the steps to positive during the method call. And then Steps = 0, it does nothing and return true. But, yeah, you are right, it has the potential to have a loop in there. And that makes modification really error prone. Thanks for point that out, I will fix that also.

Derek Smyth , thanks for the comment. I made the code a lot more compact based on your suggestions, and I updated it to the top. So, here are the updates:

Trim down code using your suggestions, and turn all public functions to driver only.
The spaces will be treated as non-empty for DirectionMost functions instead of empty string. User can use loop on their end to treat spaces as empty cell.
Assign emMove values for flexibility. It is flag based. And can turn 180 degress by simply * -1.

Thank you .