Hi,
Can you provide an example (if possible) of usage of Private Type of the
following structu

CarModel as String
CarRego as String
CarYearOfMfg as Integer
CarMilage as Double
CarAlarm As Boolean

In particular, I need to get a grasp how using of Private Type can help
populating UserForm and then sheet cells. Will it be possible with them to
eliminate the need for named ranges? Or the opposite is true?

Hope it's easy to answer :)

Paul

I assume you mean User-defined type:

Type Car
CarModel As String
CarRego As String
CarYearOfMfg As Integer
CarMilage As Double
CarAlarm As Boolean
End Type

Sub Tester1()
Dim mycar As Car
mycar.CarModel = "Ford"
mycar.CarRego = "1234ABC5678"
mycar.CarYearOfMfg = "2002"
mycar.CarMilage = 8350.2
mycar.CarAlarm = True
End Sub

Once the values are populated, you can retrieve their values using the same
construct (observing lifetime and scoping rules).

msgbox mycar.CarMilage

--
Regards,
Tom Ogilvy

"count" wrote in message
...
Hi,
Can you provide an example (if possible) of usage of Private Type of the
following structu

CarModel as String
CarRego as String
CarYearOfMfg as Integer
CarMilage as Double
CarAlarm As Boolean

In particular, I need to get a grasp how using of Private Type can help
populating UserForm and then sheet cells. Will it be possible with them to
eliminate the need for named ranges? Or the opposite is true?

Hope it's easy to answer :)

Paul

Thanks Tom,
So it looks that User type is tidier but not shorter way to disseminate
data.
And looping iterations are still required, using or not With ...End With
construct.

........If only named ranges were possible I could then say:

MyCar = CarDetailsRange
.... statements
CarDetailRange=MyCar ' hope cells get populated ok

Ehhh...

Dim MyCar As Car
Dim rng as Range

Type Car
CarModel As String
CarRego As String
CarYearOfMfg As Integer
CarMilage As Double
CarAlarm As Boolean
End Type

Sub Tester1()
Dim mycar As Car
mycar.CarModel = "Ford"
mycar.CarRego = "1234ABC5678"
mycar.CarYearOfMfg = "2002"
mycar.CarMilage = 8350.2
mycar.CarAlarm = True
End Sub

Once the values are populated, you can retrieve their values using the
same
construct (observing lifetime and scoping rules).

msgbox mycar.CarMilage

--
Regards,
Tom Ogilvy

"count" wrote in message
...
Hi,
Can you provide an example (if possible) of usage of Private Type of
the
following structu

CarModel as String
CarRego as String
CarYearOfMfg as Integer
CarMilage as Double
CarAlarm As Boolean

In particular, I need to get a grasp how using of Private Type can help
populating UserForm and then sheet cells. Will it be possible with them
to
eliminate the need for named ranges? Or the opposite is true?

Hope it's easy to answer :)

Paul

Hi Mr or Miss Count

If CarDetailsRange is a single row or single column maybe
one of these possibilities. First put a mixture of data
types in A1:C1 and A5:A8

Sub Test()
Dim v1 As Variant, v2 As Variant, v3 As Variant
Dim r1 As Range, r2 As Range

Set r1 = Range("A1:C1")
Set r2 = Range("A5:A8")
v1 = r1.Value
v2 = r2.Value
v3 = WorksheetFunction.Transpose(r2.Value)

For i = 1 To 3
Debug.Print v1(1, i), v2(i, 1), v3(i)
Next
Range("A2:C2") = v1
Range("B5:B8") = v2
Range("A3:C3") = v3
Range("C5:C8") = WorksheetFunction.Transpose(v3)
End Sub

Could you not work directly with the range object?

Regards,
Peter



-----Original Message-----
Thanks Tom,
So it looks that User type is tidier but not shorter way
to disseminate
data.
And looping iterations are still required, using or not
With ...End With
construct.

........If only named ranges were possible I could then
say:

MyCar = CarDetailsRange
.... statements
CarDetailRange=MyCar ' hope cells get populated ok

Ehhh...

Dim MyCar As Car
Dim rng as Range

Type Car
CarModel As String
CarRego As String
CarYearOfMfg As Integer
CarMilage As Double
CarAlarm As Boolean
End Type

Sub Tester1()
Dim mycar As Car
mycar.CarModel = "Ford"
mycar.CarRego = "1234ABC5678"
mycar.CarYearOfMfg = "2002"
mycar.CarMilage = 8350.2
mycar.CarAlarm = True
End Sub

Once the values are populated, you can retrieve their
values using the
same
construct (observing lifetime and scoping rules).

msgbox mycar.CarMilage

--
Regards,
Tom Ogilvy

"count" wrote in message
...
Hi,
Can you provide an example (if possible) of usage of
Private Type of
the
following structu

CarModel as String
CarRego as String
CarYearOfMfg as Integer
CarMilage as Double
CarAlarm As Boolean

In particular, I need to get a grasp how using of
Private Type can help
populating UserForm and then sheet cells. Will it be
possible with them
to
eliminate the need for named ranges? Or the opposite
is true?

Hope it's easy to answer :)

Paul


.

Thanks Peter, Tom
The reason I asked is that I'am a purist I suppose - old Cobol coding school
:)
So I like code tight, efficent and tidy.
My example was simplified version of what I needed.

In reality I have this shoe manufacturer, whom I writing application to take
orders, accumulate them to a point and then generate Production Order Sheet
when they feel like it. This sheet, you guessed it, is aggregated sum of
identical orders, telling production people what model to make and what
components to use and whom to send goods.
What is "identical" in shoe industry? Easy -13 basic components must be the
same: soles, tops, heels and 10 more...
So I find myself over and over moving these 13 adjacent / related pieces of
data:
1. From input form to Spreadsheet cells
2. From cells to Viewing form (same as Input but no change allowed)
3. From cells to comparison routines
4. ..... to sorting routines
5. ..... to listboxes on some quick glance forms (sortable, of course, by
columns :)
6........to another sheet where Production sheets details are kept
there's more of course...
So I believe it would be nice to avoid doing these:
For n = 1 to 13 over and over
(in fact I protected my bum in the future, allowing for more elements - yes!
maybe they'll start fitting digital watches or microprocessors in them.. who
knows... and I declared a named range called ShoeElements, where 13 names
are listed and, I use them extensively.
So I really do:
For n = 1 to Range("ShoeElements").Rows.Count - which I consider clever :)
But: I'd still prefer to use a construct - if it was available....

ShoeWholeThing = Range("ThisShoeDetails") 'ShoeWholeThing being User Type
with 13 sub guys listed

.....do some operations on selected ShoeWholeThing.Heel maybe .SoleColor etc
then post them all back with

Range("ThisShoeDetails") = ShoeWholeThing

I'm glad I told you about shoes - off my chest !
:)
Best regards
Paul

Uzytkownik "Peter T" napisal w
wiadomosci ...
Hi Mr or Miss Count

If CarDetailsRange is a single row or single column maybe
one of these possibilities. First put a mixture of data
types in A1:C1 and A5:A8

Sub Test()
Dim v1 As Variant, v2 As Variant, v3 As Variant
Dim r1 As Range, r2 As Range

Set r1 = Range("A1:C1")
Set r2 = Range("A5:A8")
v1 = r1.Value
v2 = r2.Value
v3 = WorksheetFunction.Transpose(r2.Value)

For i = 1 To 3
Debug.Print v1(1, i), v2(i, 1), v3(i)
Next
Range("A2:C2") = v1
Range("B5:B8") = v2
Range("A3:C3") = v3
Range("C5:C8") = WorksheetFunction.Transpose(v3)
End Sub

Could you not work directly with the range object?

Regards,
Peter



-----Original Message-----
Thanks Tom,
So it looks that User type is tidier but not shorter way
to disseminate
data.
And looping iterations are still required, using or not
With ...End With
construct.

........If only named ranges were possible I could then
say:

MyCar = CarDetailsRange
.... statements
CarDetailRange=MyCar ' hope cells get populated ok

Ehhh...

Dim MyCar As Car
Dim rng as Range

Type Car
CarModel As String
CarRego As String
CarYearOfMfg As Integer
CarMilage As Double
CarAlarm As Boolean
End Type

Sub Tester1()
Dim mycar As Car
mycar.CarModel = "Ford"
mycar.CarRego = "1234ABC5678"
mycar.CarYearOfMfg = "2002"
mycar.CarMilage = 8350.2
mycar.CarAlarm = True
End Sub

Once the values are populated, you can retrieve their
values using the
same
construct (observing lifetime and scoping rules).

msgbox mycar.CarMilage

--
Regards,
Tom Ogilvy

"count" wrote in message
...
Hi,
Can you provide an example (if possible) of usage of
Private Type of
the
following structu

CarModel as String
CarRego as String
CarYearOfMfg as Integer
CarMilage as Double
CarAlarm As Boolean

In particular, I need to get a grasp how using of
Private Type can help
populating UserForm and then sheet cells. Will it be
possible with them
to
eliminate the need for named ranges? Or the opposite
is true?

Hope it's easy to answer :)

Paul


.

Hi Paul

I'm glad I told you about shoes - off my chest !

Always a good idea to get shoes off your chest, but might
depend who's wearing them!

Afraid I don't know any other way to get range data into
an array without looping, unless it's in a single column
or row. Perhaps you could still use that approach for
one "directions" of your data, would save the "for n = 1
to 13" just leaving you with the "over and over".

Is that 1-13 loop over & over noticeable - if not take the
pragmatic approach and ignore the purist. However I would
of thought you could accomplish most of your tasks
directly with the range, or one copied to a hidden sheet
maybe.

Another possibility, what you described sounds like a
candidate for a Class. Look at Chip Pearson and Stephen
Bullen on the subject:
http://tinyurl.com/ytxo5

Regards,
Peter



-----Original Message-----
Thanks Peter, Tom
The reason I asked is that I'am a purist I suppose - old
Cobol coding school
:)
So I like code tight, efficent and tidy.
My example was simplified version of what I needed.

In reality I have this shoe manufacturer, whom I writing
application to take
orders, accumulate them to a point and then generate
Production Order Sheet
when they feel like it. This sheet, you guessed it, is
aggregated sum of
identical orders, telling production people what model to
make and what
components to use and whom to send goods.
What is "identical" in shoe industry? Easy -13 basic
components must be the
same: soles, tops, heels and 10 more...
So I find myself over and over moving these 13 adjacent /
related pieces of
data:
1. From input form to Spreadsheet cells
2. From cells to Viewing form (same as Input but no
change allowed)
3. From cells to comparison routines
4. ..... to sorting routines
5. ..... to listboxes on some quick glance forms
(sortable, of course, by
columns :)
6........to another sheet where Production sheets details
are kept
there's more of course...
So I believe it would be nice to avoid doing these:
For n = 1 to 13 over and over
(in fact I protected my bum in the future, allowing for
more elements - yes!
maybe they'll start fitting digital watches or
microprocessors in them.. who
knows... and I declared a named range called
ShoeElements, where 13 names
are listed and, I use them extensively.
So I really do:
For n = 1 to Range("ShoeElements").Rows.Count - which I
consider clever :)
But: I'd still prefer to use a construct - if it was
available....

ShoeWholeThing = Range
("ThisShoeDetails") 'ShoeWholeThing being User Type
with 13 sub guys listed

.....do some operations on selected ShoeWholeThing.Heel
maybe .SoleColor etc
then post them all back with

Range("ThisShoeDetails") = ShoeWholeThing

I'm glad I told you about shoes - off my chest !
:)
Best regards
Paul

Uzytkownik "Peter T"
napisal w
wiadomosci ...
Hi Mr or Miss Count

If CarDetailsRange is a single row or single column
maybe
one of these possibilities. First put a mixture of data
types in A1:C1 and A5:A8

Sub Test()
Dim v1 As Variant, v2 As Variant, v3 As Variant
Dim r1 As Range, r2 As Range

Set r1 = Range("A1:C1")
Set r2 = Range("A5:A8")
v1 = r1.Value
v2 = r2.Value
v3 = WorksheetFunction.Transpose(r2.Value)

For i = 1 To 3
Debug.Print v1(1, i), v2(i, 1), v3(i)
Next
Range("A2:C2") = v1
Range("B5:B8") = v2
Range("A3:C3") = v3
Range("C5:C8") = WorksheetFunction.Transpose(v3)
End Sub

Could you not work directly with the range object?

Regards,
Peter



-----Original Message-----
Thanks Tom,
So it looks that User type is tidier but not shorter
way
to disseminate
data.
And looping iterations are still required, using or not
With ...End With
construct.

........If only named ranges were possible I could then
say:

MyCar = CarDetailsRange
.... statements
CarDetailRange=MyCar ' hope cells get populated ok

Ehhh...

Dim MyCar As Car
Dim rng as Range

Type Car
CarModel As String
CarRego As String
CarYearOfMfg As Integer
CarMilage As Double
CarAlarm As Boolean
End Type

Sub Tester1()
Dim mycar As Car
mycar.CarModel = "Ford"
mycar.CarRego = "1234ABC5678"
mycar.CarYearOfMfg = "2002"
mycar.CarMilage = 8350.2
mycar.CarAlarm = True
End Sub

Once the values are populated, you can retrieve their
values using the
same
construct (observing lifetime and scoping rules).

msgbox mycar.CarMilage

--
Regards,
Tom Ogilvy

"count" wrote in message
...
Hi,
Can you provide an example (if possible) of usage
of
Private Type of
the
following structu

CarModel as String
CarRego as String
CarYearOfMfg as Integer
CarMilage as Double
CarAlarm As Boolean

In particular, I need to get a grasp how using of
Private Type can help
populating UserForm and then sheet cells. Will it
be
possible with them
to
eliminate the need for named ranges? Or the
opposite
is true?

Hope it's easy to answer :)

Paul


.



.

You can avoid looping through the worksheet cells by loading their
values into VBA in one shot:

' get data from sheet
Dim vArray as Variant
vArray = ActiveSheet.Range("A1:M20").Value
' vArray is a variant containing a 20 row x 13 column array
' matching the size of the range

You can now treat vArray as a VBA array, option base 1:

iMax = UBound(vArray, 1) ' # rows
jMax = UBound(vArray, 2) ' # columns

Looping through the VBA array is much faster than looping through the cells.

You need to define the size of the output range when putting data back
into the range:

' put data into sheet
With ActiveSheet.Cells(1, 1).Resize(20,13)
.Value = vArray
End with

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

count wrote:

Thanks Peter, Tom
The reason I asked is that I'am a purist I suppose - old Cobol coding school
:)
So I like code tight, efficent and tidy.
My example was simplified version of what I needed.

In reality I have this shoe manufacturer, whom I writing application to take
orders, accumulate them to a point and then generate Production Order Sheet
when they feel like it. This sheet, you guessed it, is aggregated sum of
identical orders, telling production people what model to make and what
components to use and whom to send goods.
What is "identical" in shoe industry? Easy -13 basic components must be the
same: soles, tops, heels and 10 more...
So I find myself over and over moving these 13 adjacent / related pieces of
data:
1. From input form to Spreadsheet cells
2. From cells to Viewing form (same as Input but no change allowed)
3. From cells to comparison routines
4. ..... to sorting routines
5. ..... to listboxes on some quick glance forms (sortable, of course, by
columns :)
6........to another sheet where Production sheets details are kept
there's more of course...
So I believe it would be nice to avoid doing these:
For n = 1 to 13 over and over
(in fact I protected my bum in the future, allowing for more elements - yes!
maybe they'll start fitting digital watches or microprocessors in them.. who
knows... and I declared a named range called ShoeElements, where 13 names
are listed and, I use them extensively.
So I really do:
For n = 1 to Range("ShoeElements").Rows.Count - which I consider clever :)
But: I'd still prefer to use a construct - if it was available....

ShoeWholeThing = Range("ThisShoeDetails") 'ShoeWholeThing being User Type
with 13 sub guys listed

.....do some operations on selected ShoeWholeThing.Heel maybe .SoleColor etc
then post them all back with

Range("ThisShoeDetails") = ShoeWholeThing

I'm glad I told you about shoes - off my chest !
:)
Best regards
Paul

Uzytkownik "Peter T" napisal w
wiadomosci ...

Hi Mr or Miss Count

If CarDetailsRange is a single row or single column maybe
one of these possibilities. First put a mixture of data
types in A1:C1 and A5:A8

Sub Test()
Dim v1 As Variant, v2 As Variant, v3 As Variant
Dim r1 As Range, r2 As Range

Set r1 = Range("A1:C1")
Set r2 = Range("A5:A8")
v1 = r1.Value
v2 = r2.Value
v3 = WorksheetFunction.Transpose(r2.Value)

For i = 1 To 3
Debug.Print v1(1, i), v2(i, 1), v3(i)
Next
Range("A2:C2") = v1
Range("B5:B8") = v2
Range("A3:C3") = v3
Range("C5:C8") = WorksheetFunction.Transpose(v3)
End Sub

Could you not work directly with the range object?

Regards,
Peter




-----Original Message-----
Thanks Tom,
So it looks that User type is tidier but not shorter way

to disseminate

data.
And looping iterations are still required, using or not

With ...End With

construct.

........If only named ranges were possible I could then

say:

MyCar = CarDetailsRange
.... statements
CarDetailRange=MyCar ' hope cells get populated ok

Ehhh...

Dim MyCar As Car
Dim rng as Range


Type Car
CarModel As String
CarRego As String
CarYearOfMfg As Integer
CarMilage As Double
CarAlarm As Boolean
End Type

Sub Tester1()
Dim mycar As Car
mycar.CarModel = "Ford"
mycar.CarRego = "1234ABC5678"
mycar.CarYearOfMfg = "2002"
mycar.CarMilage = 8350.2
mycar.CarAlarm = True
End Sub

Once the values are populated, you can retrieve their

values using the

same

construct (observing lifetime and scoping rules).

msgbox mycar.CarMilage

--
Regards,
Tom Ogilvy

"count" wrote in message
.. .

Hi,
Can you provide an example (if possible) of usage of

Private Type of

the

following structu

CarModel as String
CarRego as String
CarYearOfMfg as Integer
CarMilage as Double
CarAlarm As Boolean

In particular, I need to get a grasp how using of

Private Type can help

populating UserForm and then sheet cells. Will it be

possible with them

to

eliminate the need for named ranges? Or the opposite

is true?

Hope it's easy to answer :)

Paul


.