Named Range syntax when working abstracted from worksheet cells

**HRCalcNStuf** · 10-01-2012, 12:35 PM

Hi all,

Sorry if this is asked often, but I'm trying to do a lot of programmatic work with ranges, but with some level of abstraction from actual worksheets.

All documentation and instances of how to work with ranges and their various methods tends to center around working with known, actual cells in a worksheet: "Range("MyRange")("A1:B5").Select" or something along those lines.

But what if my range isn't anchored to a (known) specific cell in a worksheet? What kind of syntax is valid for selecting: "Range("MyRange")( [whatever cell is the 4th row,2nd column] : [whatever cell is the 5th row, 4th column ).Select" ?

I've tried using various forms of the syntax found at http://support.microsoft.com/kb/291308 , but those all just return "Type mismatch" errors.

Abstracted, programmatic ranges with Excel/VBA (what little I understand of it) has been the Holy Grail for me! I'd love to be able to build, add to, subtract lists without tying them down to worksheets, but I can't ever seem to find workable syntax. Every search for "dynamic" all still comes back to examples with things like A1 or $A$1 in them, they're all about selecting worksheet cells; no abstraction. Even when I do have cells, I don't necessarily know where they are in the sheet, I only know their relative position in the named range. Yes, things like OFFSET(blah, blah) might be useable but there's the trick of knowing how to use them syntactically, which I don't.

I'm hoping there are some folks here who can help me out!

Thanks
-H.R. Calcnstuf

**TMS** · 10-01-2012, 03:10 PM

I'm really not sure what it is you are trying to achieve, but the syntax would be something like:

Range("MyRange").Range("B1:C5").Select

If MyRange refers to cells C3:H16, then the above construct would select cells D3:E7.

Range("MyRange").Range("A1").Select would actually select cell C3.

Regards, TMS

**HRCalcNStuf** · 10-03-2012, 11:47 AM

Sorry, that didn't answer the question. That syntax isn't abstracted from the worksheet, it references cells in the worksheet directly.

I'm trying to figure out how to work with a Range that is created and exists entirely programmatically, rather than as cells on a worksheet. I need to do behind the scenes work with basically what amounts to a linked list, without it showing up on any worksheets. If this were Object Pascal I'd just use a TStringList for what I'm trying to do. I'd Create it, work with it, then when done Free & Destroy it.

As a VBA & Excel noOB, how would I do that in this world?

**TMS** · 10-03-2012, 12:30 PM

I'm sorry too. I've been using Excel and VBA for over fifteen years but I am unfamiliar with the terminology you are using. I have no idea what you mean by "abstracted" or "ranges not on the worksheet".

The only thing that I can think of is a two dimensional array defined in the code. But you wouldn't refer to that using cell references, just co-ordinates ... MyArray(3, 5), for example.

Regards, TMS

**HRCalcNStuf** · 10-03-2012, 12:40 PM

That's kinda it, I'm trying to work with Ranges just with relative coordinates rather than cells, but I can't seem to find proper syntax for doing that with a lot of the methods.

So how would I:
1. Create a range. No cells, just a range. (This is what I mean by abstracted... it's not tied directly to any cells, it's just objects in memory. I've heard of ObjectLists, but I don't know anything about how to use them yet. Is this where I should be looking?)

2. Add/subtract values to that range. Again, no cells. Just trying to dynamically put together a list of strings. (But not from a table on a worksheet, but in code using lots of if-thens and extracting data from numerous tables on numerous worksheets.) Ideally it would be something that I could in the end use for Data Validation.

I've come a long way in just a few weeks, having familiarity with object programming already (object pascal) I've been able to get a lot done with my spreadsheets, but I'm still tied down to cells all the time. So I have to have these "don't-look-behind-the-curtain" worksheets that I'd rather not have, and in some cases there are some lists I simply cannot yet figure out how to build.

**Kyle123** · 10-03-2012, 03:15 PM

I'm on my mac now so I can't test this but I'm pretty sure that For a Given Range:

Set MyRange = Sheet3.Range("G10:F100")

MyRange.Range("A1") will always refer to the top left cell, MyRange.Cells(1,1) would also by the same principle refer to G10

Where MyRange.Cells(10,1) would refer to G20

Does that help any?

Otherwise, you can use multi-dimensional arrays as TMS suggests.

**shg** · 10-03-2012, 03:17 PM

So how would I:
1. Create a range. No cells, just a range.

You can't. A range is, by definition, a set of cells on a single worksheet.

You could have an array of strings that look like addresses, and manipulate those as you wished, and use those to define at range at whatever point is convenient.

A concrete example of what you're trying to do might help someone make a suggestion.

**HRCalcNStuf** · 10-03-2012, 05:12 PM

What I'm trying to do is store and manipulate data without using cells. Haha, I could have said that the first time. Looks like maybe I can use the "Long" type to declare an array, or Collection, or even Dictionary (from the scripting library apparently). Still learning how to speak the language. :-)

**mike7952** · 10-03-2012, 05:27 PM

Theres pleanty of sites for Arrays but heres a small example

Sub abc()
 Dim aMyStoredData(4) As Variant
 Dim indx As Long
 
 aMyStoredData(0) = "first stored data"
 aMyStoredData(1) = "second stored data"
 aMyStoredData(2) = "third stored data"
 aMyStoredData(3) = "fourth stored data"
 aMyStoredData(4) = "fith stored data"
 
 For indx = LBound(aMyStoredData) To UBound(aMyStoredData)
    MsgBox aMyStoredData(indx)
 Next
 
End Sub
Sub abcd()
 Dim aMyStoredData() As Variant
 Dim indx As Long
 
 ReDim aMyStoredData(1 To 5)
 For indx = LBound(aMyStoredData) To UBound(aMyStoredData)
     aMyStoredData(indx) = indx & " stored data" '< add values
 Next
 For indx = LBound(aMyStoredData) To UBound(aMyStoredData)
    MsgBox aMyStoredData(indx)    '<read values
 Next
End Sub

**HRCalcNStuf** · 10-03-2012, 05:33 PM

Yes, thank you! The trick with finding out about how to use arrays is to formulate my searches specifically to avoid "array formula," otherwise I get nothing but results for "array formulas" and those aren't really what I'm looking for.

**Kyle123** · 10-03-2012, 05:39 PM

I don't think collections or dictionaries (better collections) will really do what you're after, they're just untyped key value pairs - with a string for a key - so fast for extracting data, but slow for adding.

You could however build dictionaries of dictionaries etc, though if you're using a large amount of data they have a tendency to get quite slow when adding to them since they re-index for every addition. Performance wise, you'd probably be better off with multi-dimensional arrays - probably 2d since they're a bit difficult to visualise when you have more than 2 dimensions.

So a 2 dimensional array would look like the rows and columns of a table, note that these are structurally different from jagged arrays that you'd find in languages like Java/PHP/Javascript which are arrays of arrays.

If you're doing calculations on arrays, note that you can use the in-built Excel functions, Sum etc. Have a read through the Tips and Tutorials on this site for a thread by Donkey Ote about the Index function, this allows you to perform calculations of blocks of data within a 2d array. You can't use these functions of collection type objects.

Hope that helps

**HRCalcNStuf** · 10-03-2012, 05:48 PM

Appreciate all the responses and all the help, folks, you've pointed me down some paths I can follow! I was having an impossible time finding via search engine ways to manipulate data that wasn't using cells, so thank you.

Named Range syntax when working abstracted from worksheet cells

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Named Range syntax when working abstracted from worksheet cells

Re: Named Range syntax when working abstracted from worksheet cells

Re: Named Range syntax when working abstracted from worksheet cells

Re: Named Range syntax when working abstracted from worksheet cells

Re: Named Range syntax when working abstracted from worksheet cells

Re: Named Range syntax when working abstracted from worksheet cells

Re: Named Range syntax when working abstracted from worksheet cells

Re: Named Range syntax when working abstracted from worksheet cells

Re: Named Range syntax when working abstracted from worksheet cells

Re: Named Range syntax when working abstracted from worksheet cells

Re: Named Range syntax when working abstracted from worksheet cells

Re: Named Range syntax when working abstracted from worksheet cells

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions