Code cleaning

My intention was to improve data collection but as I struggled along, I decided  there needed to be some housekeeping.

Ever since moving all the calculations to VBA I’ve been unable to view any sheets other than Sheet1  whilst running, as the code contained a series of sheet.select lines. This was done initially to overcome lazy referencing.

So where I had Cells(10, 1), I now have Worksheets(“Sheet1”).Cells(10, 1).

I’ve also got rid of some sheet.select-copy-sheet.select-paste-sheet.select routines by simply using full references.

Although this has taken a bit of time and testing, it will result in faster code execution.

 

Advertisements

3 thoughts on “Code cleaning”

  1. Maybe consider naming the range (of the cell) and apply the range name in the vba. Then if you change the sheet name or positioning of the cell you dont have to re-reference in the code 🙂

    Like

    1. Thanks Tony, I’ll look at that. Any idea how to reference a dynamic range in an array? I’d like to extract data without looping through array positions. I have a 2d array lifted from sheet and want a “row” extracted.

      Like

  2. hmm. i think in named ranges you still need to reference sheet name so i used to set this as a variable to just set once. You might name the first cell then use .select through a list of named ranges along with ActiveCell.EntireRow.Select or .value maybe?

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s