Questions triggered by a macro that trims spaces

Question

Found this macro online and while studying it, I came up with two questions that I haven't been able to find an answer for:

First, is it necessary to declare the object variables MyRange and MyCell? What advantage(s) does this provide by doing it?

Second, based on a previous question I posted, is it redundant to check whether the cell is empty or not before its value is trimmed. What is the advantage of this check? Is it merely to save compiling effort/time? Or are there other technical reasons?

Sub TrimSpaces()
Dim MyRange As Range
Dim MyCell As Range
Select Case MsgBox(“Workbook First?”, vbYesNoCancel)
    Case Is = vbYes
    ThisWorkbook.Save
    Case Is = vbCancel
    Exit Sub
End Select
Set MyRange = Selection
For Each MyCell in MyRange
    If Not IsEmpty(MyCell) Then
        MyCell = Trim(MyCell)
    End If
Next MyCell
End Sub

Note that you used these smart quotes `“ ”` but VBA only accepts the simple quotes `" "`. — Pᴇʜ, Oct 31 '18 at 13:18

score 3 · Accepted Answer · answered Oct 31 '18 at 13:25

3

First:
It is a very good practice to use Option Explicit and declare all variables. Also declaring ranges and worksheets etc. is a good technique to avoid using .Select or .Activate. How to avoid using Select in Excel VBA

Second:
MyCell = Trim(MyCell) will cause a write action (takes time) to that cell even if it was empty. Checking if it was empty could avoid an unnecessary write action, and therefore could make it a little bit more efficient.

answered Oct 31 '18 at 13:25

Pᴇʜ

56,719
10
49
73

Thanks for this answer! To really crystallize this in my mind, can you show me what the loop code would be if I didn't declare the range variables? – GeekyFreaky Oct 31 '18 at 13:38
1

You would have to use `Selection` directly in `For Each MyCell in Selection` that's all. This code is no good example to illustrate the good practice of declaring variables. But anyway all you need to remember is: Avoid using `.Select` and use `Option Explicit` to force variable declaration. • Not declaring variables to the correct type *can* cause errors and make your code inefficient (because VBA automatically assumes `Variant` which is the most inefficient type). – Pᴇʜ Oct 31 '18 at 13:44
What about MyCell? What can that be replaced with if it wasn't declared as a range variable? I'm just trying to figure out what is absolutely necessary to declare vs what isn't. Thanks so much!!! – GeekyFreaky Oct 31 '18 at 14:15
@GeekyFreaky if you remove `Dim MyCell As Range` and write nothing instead, Excel will automatically assume it as `Variant`. Means it is the same as it was declared `Dim MyCell As Variant`. • An additional advantage of declaring it as `Range` is that you can use IntelliSense in the VB Editor (if you start writing `MyCell.` Excel pops up a list of available properties and methods of a range object) which is not available for `Variant`. – Pᴇʜ Oct 31 '18 at 14:23

Error 1004 · Answer 2 · 2018-10-31T13:36:13.363

2

In my opinion i think is better to declare a range to avoid repetition and to be more clear especial if you have a long code.In your occasion you use a loop to test every "MyCell" (address to one part (range)) of the whole "MyRange" (which is also a range).

Second, by checking cell value will help to save time by prevent executing the code after if condition does not meet.

Lastly, Trim & Clean function will help you to "Clean" cell value from invisible character, unnecessary spaces etc.

edited Oct 31 '18 at 13:36

answered Oct 31 '18 at 13:01

Error 1004

7,877
3
23
46

`Left` or `Right` will not error if you pass them `Empty`. It gets implicitly cast to `vbNullString`. You can verify this in the immediate window: `?Left(Empty, 42)` – Comintern Oct 31 '18 at 13:03
@Comintern for some reason sometimes i receive an error on this.could you please more info to investigate? – Error 1004 Oct 31 '18 at 13:09
Are you sure the cell is empty when you get your errors? It can fail if a cell contains an *error*, but `IsEmpty` isn't the appropriate test for that. As demonstrated above, an empty cell will always get coerced. – Comintern Oct 31 '18 at 13:13
You are absolutely right!I check cell value and it was not empty but was look like empty (no visible characters but a special character was in).The data was scraped from website!i fix my code when scrap value to trim and clean!that was very helpful! – Error 1004 Oct 31 '18 at 13:18
Are you sure that this: *"You may need to use Left or Right function and you dont check cell value.If cell is empty you will receive an error."* is correct? Because `Debug.Print Left("", 100)` doesn't throw an error at all. – Pᴇʜ Oct 31 '18 at 13:28
1

@Pᴇʜ i was doing and mistake with my own coding and Comintern help me to find and understand where was my error.You can see our discussion above! – Error 1004 Oct 31 '18 at 13:30

Questions triggered by a macro that trims spaces

2 Answers2