How do I convert a user defined selection into an array?
 

The small company I work for does geo-spatial mapping, and much of our
spatial data is very heavy in tabular attribute data. I have been elected
(forced is more like it!) to start some novice level explorations into
developing simple user forms for our Project Coordinators, and eventually
our clients.

Thanks to Tom, I have coded a simple form that selects an existing table
from a worksheet based on one user imput. It looks like this:

Private Sub cmd2_proj1_Click()

Dim StartCell As String
StartCell = txt1_proj1.Text

Range(StartCell).CurrentRegion.Select

End Sub

What I'd like to do next is "dump" this selection into an array. Since we
are constantly working with different and changing table sizes, I can't
necessarily say how big the array will be. I'm thinking there is a way to
extract the selection dimensions into cell reference form, but I haven't
found it yet.

So...is this a relatively simple procedure? Am I missing or not providing
some critical information?

From reading all the posts I'm realizing there is a wealth of commands
particular to VBA and Excel, and I'm savvy enough to guess at their
purpose - I just don't know how to use them. Any links or books
(specifically for Excel) you would recommend that list these commands and
functions and what they do would be greatly appreciated.

TIA!

-gk-

By the way, I did NOT find "VBA For Dummies" very helpful.

How do I convert a user defined selection into an array?
 

geo-spacial mapping sounds like LOTS of data.

u sure that excel's 256 column limit is not going
to be your bottleneck for efficient processing?

working with range objects MIGHT be more practical then working with
arrays, as they offer sorting and filtering.

to dump a range's values in an array.. easiest is following

dim vaData as variant
vaData = Range("a1:d2000").value

However if you'd need the Sum of that range or (standard deviation)
it would be faster and save memory to leave it a range and apply a
worksheetfunction on that range.

dim rngData as Range
dim dblSum as Double

SET rngData = Range("a1:d2000")
dblsum = application.worksheetfunction.sum(rngData)

you can resize/offset the range object quickly and efficiently.
set rgnData = Range("a1:az1")
set rgnData = rngData.Offset(1,0) '< points the var to a2:az2
(1 row down,0 cols right)
set rngData = rngData.Resize(10,2) '< changes it from a2:az2 to a12:b12

you could combine the lines
Set RgnData=Range("a1:az1").Offset(1,0).Resize(10,2)


it all depends... :)

I've heard very good things about
Power Programming Excel 2002 with VBA by John Walkenbach

For more theory and generic approach I suggest:
VBA Developer's handbook by Ken Getz


have fun!.. if you need help with the project drop me an email.
address below

keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"TBA" wrote:

The small company I work for does geo-spatial mapping, and much of our
spatial data is very heavy in tabular attribute data. I have been
elected (forced is more like it!) to start some novice level
explorations into developing simple user forms for our Project
Coordinators, and eventually our clients.

Thanks to Tom, I have coded a simple form that selects an existing
table from a worksheet based on one user imput. It looks like this:

Private Sub cmd2_proj1_Click()

Dim StartCell As String
StartCell = txt1_proj1.Text

Range(StartCell).CurrentRegion.Select

End Sub

What I'd like to do next is "dump" this selection into an array.
Since we are constantly working with different and changing table
sizes, I can't necessarily say how big the array will be. I'm
thinking there is a way to extract the selection dimensions into cell
reference form, but I haven't found it yet.

So...is this a relatively simple procedure? Am I missing or not
providing some critical information?

From reading all the posts I'm realizing there is a wealth of commands
particular to VBA and Excel, and I'm savvy enough to guess at their
purpose - I just don't know how to use them. Any links or books
(specifically for Excel) you would recommend that list these commands
and functions and what they do would be greatly appreciated.

TIA!

-gk-

By the way, I did NOT find "VBA For Dummies" very helpful.

How do I convert a user defined selection into an array?
 

Think you had a typo here

set rngData = rngData.Resize(10,2) '< changes it from a2:az2 to a12:b12



? range("a2:az2").Resize(10,2).Address
$A$2:$B$11


--
Regards,
Tom Ogilvy

keepitcool wrote in message
...


geo-spacial mapping sounds like LOTS of data.

u sure that excel's 256 column limit is not going
to be your bottleneck for efficient processing?

working with range objects MIGHT be more practical then working with
arrays, as they offer sorting and filtering.

to dump a range's values in an array.. easiest is following

dim vaData as variant
vaData = Range("a1:d2000").value

However if you'd need the Sum of that range or (standard deviation)
it would be faster and save memory to leave it a range and apply a
worksheetfunction on that range.

dim rngData as Range
dim dblSum as Double

SET rngData = Range("a1:d2000")
dblsum = application.worksheetfunction.sum(rngData)

you can resize/offset the range object quickly and efficiently.
set rgnData = Range("a1:az1")
set rgnData = rngData.Offset(1,0) '< points the var to a2:az2
(1 row down,0 cols right)
set rngData = rngData.Resize(10,2) '< changes it from a2:az2 to a12:b12

you could combine the lines
Set RgnData=Range("a1:az1").Offset(1,0).Resize(10,2)


it all depends... :)

I've heard very good things about
Power Programming Excel 2002 with VBA by John Walkenbach

For more theory and generic approach I suggest:
VBA Developer's handbook by Ken Getz


have fun!.. if you need help with the project drop me an email.
address below

keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"TBA" wrote:

The small company I work for does geo-spatial mapping, and much of our
spatial data is very heavy in tabular attribute data. I have been
elected (forced is more like it!) to start some novice level
explorations into developing simple user forms for our Project
Coordinators, and eventually our clients.

Thanks to Tom, I have coded a simple form that selects an existing
table from a worksheet based on one user imput. It looks like this:

Private Sub cmd2_proj1_Click()

Dim StartCell As String
StartCell = txt1_proj1.Text

Range(StartCell).CurrentRegion.Select

End Sub

What I'd like to do next is "dump" this selection into an array.
Since we are constantly working with different and changing table
sizes, I can't necessarily say how big the array will be. I'm
thinking there is a way to extract the selection dimensions into cell
reference form, but I haven't found it yet.

So...is this a relatively simple procedure? Am I missing or not
providing some critical information?

From reading all the posts I'm realizing there is a wealth of commands
particular to VBA and Excel, and I'm savvy enough to guess at their
purpose - I just don't know how to use them. Any links or books
(specifically for Excel) you would recommend that list these commands
and functions and what they do would be greatly appreciated.

TIA!

-gk-

By the way, I did NOT find "VBA For Dummies" very helpful.

How do I convert a user defined selection into an array?
 

Tom, yep. thx, rest ok? :)

keepITcool


"Tom Ogilvy" wrote:

Think you had a typo here

set rngData = rngData.Resize(10,2) '< changes it from a2:az2 to a12:b12



? range("a2:az2").Resize(10,2).Address
$A$2:$B$11