Love the book EXCEL 2016

[KennethLeidner]

Found the book in the library. The part about cleaning up recorded code is great. I really like the how to find the last row, one line solution vs the recorder's code.

I also like that you talked about both sides of the "Force defining variables or not". I started out not defining variable, but have changed to defining them. Not for discovering using two name for the same variable (LastRow vs lastrow), but for not having everything being the default Variant.

I do have a question about defining variables. I recently heard to put the dim statement right before the first usage, rather than at the top of the code. Your thought? Mine is its a bad idea. Reason; when I am down in the code say at the nth usage of the variable I'll never be able to find the Dim statement. How do I know where the first usage is in the code. However it did get me to wonder if maybe I should alphabetize the Dim statements. Well not completely but maybe by first letter of the variable's name.

One last comment. I already new about using arrays rather than cells reference to speed up the code, it makes a phenomenal difference. Bold the text about that!!

 

[starl]

Thank you for the kind words

As for dimming... I've seen some people Dim in the middle of the code but I always do it at the top. For one thing, you never know when you may edit your code to use that variable sooner. Having it at the top saves the headache. I think some people who have them only right before they use them do it because many many moons ago, memory was limited and that was a way of reducing usage. It's similar to using Integer vs Long, I think. Long uses more memory than Integer, so if you knew you would only need Integer, you'd dim as that, only using Long if it was really needed. Nowadays, I know some users always use Long. Me, I still use Integer, but that's just me. Memory isn't as big an issue.

oh - as for alphabetizing, I don't. If anything, I group by type, though it's not perfect. I prefer having my objects at the top of the list. But again - personal preference. If you want to alphabetize, go for it.

 

[smozgur]

I agree with @starl about "personal preference" on this matter.

However, my own method:

1. Always use Option Explicit to enforce variable declaration. Avoids any kind of variable type and also typing variable name issues/mistakes.
2. Declare variables at the top of the module in a procedure, and single variable declaration on each line. Perfect readability.
3. Optionally declare variables in the order of their appearance in the code. Perfect continuity but also an artistic touch.
4. Bonus: Don't use late binding unless it is certainly necessary due to platform differences. Basically, don't use Dim fso As Object instead of Dim fso As FileSystemObject.

I consider each procedure in my application is a chapter and the declaration block is kind of a "Table of Contents".

 

[Joe4]

I agree with declaring the variables at the top - I don't want to go hunting through my code to find them all.
It also helps prevent me from using the same variable name twice - it is easy to see when they are all grouped together.

And the "Option Explicit" tip is a great one! Helps catch a lot of typos from bad typists like me and saves aggravation of not being able to figure out why things aren't working like I expect!

 

[starl]

Bonus: Don't use late binding unless it is certainly necessary due to platform differences. Basically, don't use Dim fso As Object instead of Dim fso As FileSystemObject.

Why? Other than readability. I originate the code with early binding, but when it's done, I switch it to late. I guess old habit from the days when so many different versions of Excel were being used.
I do have some clients where I know only certain users will have access to an object, so I have to late bind else it doesn't compile. Is it faster.. I mean, I know that if early binding, any issues will be caught at the start of the program vs when it gets to the specific code. What am I missing, @smozgur ?

 

[starl]

Fun fact: the section on whether to define or not came to be because I believe in Option Explicit and (at the time, at least) Bill does not. With the programs I've written, I'd be in so much trouble if I didn't define. And I also use capitalization to catch typos. At least one capital letter - if when you're done with the line of code the variable doesn't have a capital letter, then you know you have an invalid variable!

 

[smozgur]

different versions of Excel were being used.

No, you are not missing anything. I already mentioned the unknown platform requirements. However, even a method used in your code might not be compatible with another version and might require certain library.

The main reason I am using early binding generally - as much as possible - is making the code run faster and more importantly, intellisense. As you also mentioned, I always work on the project by using all early binding but if the project will work in an unknown version of the dependent application, then I switch the variable declarations by using Object type. And I mostly modify the code as shown below before publishing:

Dim wrdApp As Object 'Word.Application
Set wrdApp = CreateObject("Word.Application") 'New Word.Application

Note: The Word.Application is just a sample. I think Office VBA is clever enough to load the existing Office library for internal dependency.

Beyond that, adding the necessary reference during the design time gives you Object Browser access to the referenced library. I can't even imagine working with HTML Object Library without intellisense and object browser access.

 

[smozgur]

Fun fact: the section on whether to define or not came to be because I believe in Option Explicit and (at the time, at least) Bill does not. With the programs I've written, I'd be in so much trouble if I didn't define. And I also use capitalization to catch typos. At least one capital letter - if when you're done with the line of code the variable doesn't have a capital letter, then you know you have an invalid variable!

There is no exception - if Option Explicit is not used then the project certainly has design time mistakes. Guaranteed.