Submit My Question - Recent Questions - Recent Answer
Msdn Tech >> Microsoft ISV Community Center Forums >> Visual Basic for Applications (VBA)

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

[8358 byte] By [magicalclick] at [2007-12-23]
«« opening LPT port
»» how to add the outlook express addin into the project file?
# 1

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.

duckthing at 2007-8-30 > top of Msdn Tech,Microsoft ISV Community Center Forums,Visual Basic for Applications (VBA)...
# 2

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.

magicalclick at 2007-8-30 > top of Msdn Tech,Microsoft ISV Community Center Forums,Visual Basic for Applications (VBA)...
# 3

HI,

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

DerekSmyth at 2007-8-30 > top of Msdn Tech,Microsoft ISV Community Center Forums,Visual Basic for Applications (VBA)...
# 4
Thank you.
magicalclick at 2007-8-30 > top of Msdn Tech,Microsoft ISV Community Center Forums,Visual Basic for Applications (VBA)...
# 5

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.

DerekSmyth at 2007-8-30 > top of Msdn Tech,Microsoft ISV Community Center Forums,Visual Basic for Applications (VBA)...
# 6

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.

DerekSmyth at 2007-8-30 > top of Msdn Tech,Microsoft ISV Community Center Forums,Visual Basic for Applications (VBA)...
# 7

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.

magicalclick at 2007-8-30 > top of Msdn Tech,Microsoft ISV Community Center Forums,Visual Basic for Applications (VBA)...
# 8

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:

  1. Trim down code using your suggestions, and turn all public functions to driver only.

  2. 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.

  3. Assign emMove values for flexibility. It is flag based. And can turn 180 degress by simply * -1.

Thank you .

magicalclick at 2007-8-30 > top of Msdn Tech,Microsoft ISV Community Center Forums,Visual Basic for Applications (VBA)...
Microsoft ISV Community Center Forums
Visual Basic for Applications (VBA)

Microsoft ISV Community Center Forums Hot Topic

Microsoft ISV Community Center Forums New Topic

Microsoft ISV Community Center Forums

Java / MSDN / Linux Tech / PHP Tech / MSDN DotNet / MSDN TECH / Health Library / Programmers Tech / visual basic Tech / Developer Tech / Ipod faqs / Msdn China Tech

Site Classified