Number stored as text, alignment of numeric values in cells
Improved/ Extended Evaluate Range solution. Recap General Purpose solution
In a few posts time we will discusses extending the last solution to overcome a couple of problems that may occur with certain types of range data, specifically, if there may be empty cells or cells with real text in it. In this and the following 2 posts we will recap and come up with a general purpose solution to then go on and modify.
General all purpose version of our final Evaluate Range solution Range variable, Rng
Let us both standardise and recap on a good general purpose version of our Evaluate Range solution, with emphasis on learning and test and developing
Range variable, Rng
It’s probably more often than not a good idea to use a variable for our main data range. In technical terms we might re say that, as a variable for our main Range object. Using a variable for things is often a good idea, and particular a good idea for a range object, since Excel and Excel is all about boxes and manipulating them, and in VBA in particular, we have a lot of useful built in things like Methods, Properties, and the such, that we can use on them/ with them, in particular the range object since that is in particular to do with the box, and boxes structure that Excel is all about.
So for some test range, such as A1:F7,
https://i.postimg.cc/nctNH0WJ/Range-A1-F7.jpg
Range(A1 F7).JPG
, then somewhere towards the start of any macro we might have something like this
Dim Rng As Range
_Set Rng = Worksheets.Item("Sheet1").Range("A1:F7")
You can often get away without the Worksheets.Item("Sheet1"). , if you know what you are doing and are sure that Excel will guess correctly which worksheet you are wanting to refer to, but often in a final coding, Excel may guess wrong, so more fully referencing a range is a good habit to get into.
We may as well, from the start of our coding get fully into such good habits, and use a variable for the worksheet also
Dim Ws1 As Worksheet
_Set Ws1 = ThisWorkbook.Worksheets.Item("Sheet1")
Dim Rng As Range
_Set Rng = Ws1.Range("A1:F7")
This Rng is referring to the test data range……._
Reply With Quote
Bookmarks