How to Use Excel VBA Code - Real Example P2
How can we use Excel VBA code to speed up manual work in Excel? In this series we explore an example Excel VBA project sent in by Eric, a viewer of the channel. We aim to build an Excel VBA application to move data around a file at the 'click-of-a-button', saving hours of frustrating manual work. The application should be flexible and scaleable enough to handle changes in business circumstances - a challenge indeed! Let's get into it...
Download File Link:
https://tinyurl.com/y4d6ds62
---------------------------------------------------------
In part 2, we create a mechanism to loop through the data on each worksheet. Looping through 'static' data is simple enough - we have done that in other videos on the channel - but Eric's briefing presents an additional challenge: how to loop through a variable amount of data?
What is meant by 'variable' in this case? As you can see in the download file, each sheet contains a different number of rows; further, Eric wishes to be able to add new sheets with any number of rows. What is required is a solution that can handle these different situations without the need for additional coding; what is needed is a dynamic solution.
The video explores how to create such a solution. We learn about the powerful .end(xldown) construct which selects to the end of a dataset and drives the dynamic mechanism. We use the 'step into' facility in the VBA editor to work through the code whilst looking at the Excel window to understand how it works, and to test the new mechanism.
Finally, we look at how to integrate With .... End With to simplify the code and to avoid having to select sheets. Selecting objects such as sheets and cells is a 'direct referencing' approach; routines built in this way can take some time to execute, and there is a more efficient approach.
We would much rather have a fast routine built on 'remote' referencing; ie. a routine that does not select sheets but uses sophisticated referencing to get the job done faster.
See you in part 3. Don't forget to let us know in the comments how you get on, Chris will get back to you.
Series Playlist Link:
https://tinyurl.com/y4pmrkxn
🎁YOUR EXCEL CHEATSHEET
🎁1 Page Summary Of What You Need To Know
https://tinyurl.com/yc7jlmbs
/ tigerspreadsheetsolutions
/ tigspreadsheets
http://tigerspreadsheetsolutions.co.uk
Also on Insta :-)
Смотрите видео How to Use Excel VBA Code - Real Example P2 онлайн без регистрации, длительностью часов минут секунд в хорошем качестве. Это видео добавил пользователь Tiger Spreadsheet Solutions 24 Июнь 2019, не забудьте поделиться им ссылкой с друзьями и знакомыми, на нашем сайте его посмотрели 8,888 раз и оно понравилось 134 людям.