Dear Michael
This post is possibly the 25thd in this thread. I have added back some of the replies to make this post easier to follow.
You seem to be confused about ranges and values. Paul's post may have helped because I had assumed that you would have understood the way I use self-documenting variable names. This is a common methodology to make code easier to read. Essentially I try to describe the object I am naming in InitialCapitals case (capitalize first character of each word) and prefix the name with a short lower case string to denote Type. ('s' for String, 'n' for Integer/Long, 'r' for Range,' clx' for Collection and so on). Using this methodology requires that you do not abbreviate. There is loads of stuff on the web about this. Look up 'VBA coding conventions'.
It is not strange that the 'Cells' is plural because it refers to the Cells collection of which any individual item is a Cell. This is in the same way that Workbooks, Worksheets, Controls, Fonts etc. refer to collections. 'Cells' is not a function, it is a property of the Range object. This explains quite well :- http://www.anthony-vba.kefra.com/vba/vbabasic2.htm
You said :-You mention that processing could be speeded up if arrays were used, so that all the processing was done within VBA.
I could not find my post about this, maybe it was in another forum. As you are wishing to process 100,000 rows this will take a long time if you do it by stepping through rows in the spreadsheet. Instead you copy the range of cells into a variant array in VBA like this :-
Dim rColumnsAandB As Range, vColumnsAandB As Variant
Set rColumnsAandB = Range("a1:b100000")
vColumnsAandB = rColumnsAandB.Value
The above populates variant array vColumnsAandB (1 to 100000, 1 to 2) with the values in range a1:b100000.
To test this I filled 100,000 row of column A with the months of the year all in Lower case.
I wanted column B to be the Upper case equivalent of column A :-
Dim nRow As Long
For nRow = 1 To 100000
vColumnsAandB(nRow, 2) = UCase(vColumnsAandB(nRow, 1))
Next nRow
rColumnsAandB = vColumnsAandB
This took less than one second.
So instead of UCase etc you do :- vColumnsAandB(nRow, 2) = clxFruit.Item("key" & vColumnsAandB(nRow, 1))
Notice in the above code that 'r' is the prefix for Range and 'v' is the prefix for Variant. When you Dim the variant you do not use brackets.
This is a very simple example. These are several issues. You cannot work with a single column. Hence in the example column A is re-written. It fails if the range is a single cell and gets quite complicated if the destination range has to be calculated. The most difficult issue is that numbers get written to Excel as text and you have to convert them back to numbers afterwards.
I have code which addresses all these issues which I will post here if anybody wants it.
You said :-
At the end of my last post I asked this question: With Fruit a defined name on a sheet, and nF defined by Dim nF as Range, what value does the statement Set nF = Range("Fruit") assign to nF? does anyone know the answer to that question?
The answer can be grasped by reference to my code above. nF.Value will be the values of the cells in the named range and nF.Address will be the named range address. As .Value is the default property of the range object you can get confused with assignment statements that leave out .Value.
I hope that this has clarified something. I cannot repeat this often enough. Do not abbreviate variable names. This is part of the confusion. There is no tax on keystrokes.
Regards
Derek Turner
+++
From: Michael Trombetta <mickey11030@yahoo.com>
Thanks for being so patient with my slow learning process.
When I wrote
nF = Range("Fruit")
nA = Range("Abb")
nrow = 3
Cells(nrow, nF).Value = clxFruit.Item("key" & Cells(nrow, nA).Value)
I was not trying to fill all of the named range nF. I had done some experiments that convinced me that Cells(nrow, nF) referred to a particular cell. That is, if nF refers to Col P, and nrow is 3, the reference is to Cell P3.
Scratch That! I just realized that in my experiment, the named ranges I used were single cells. I created a named range nPay that referred to a cell with a value of 9 and nHours to a cell with a value of 30. The statement Cells(13,3) = nPay * nHours worked, and that convinced me (incorrectly) that Cells(nrow, nA) would also work. Enlightenment. Better late than never.
You mention that processing could be speeded up if arrays were used, so that all the processing was done within VBA. You said you had discussed that in an earlier post. I'd appreciate your pointing me to that post.
Thank you for showing me the 4 ways of using Cells. That will be very useful.
Regards,
Michael Trombetta
From: Michael Trombetta <mickey11030@yahoo.com>
To: "ExcelVBA@yahoogroups.com" <ExcelVBA@yahoogroups.com>
Sent: Monday, 15 October 2012, 16:14
Subject: Re: [ExcelVBA] Abbreviations to Words
David,
I'm not trying to work on multiple cells; I thought Cells(4, E2:E7) was a way to select E4, but I see that does not work.
Early in this thread, Derek posted the following
Cells(nRow, nFullNameColumn).Value = clxFruit.Item("key" & nAbbreviatedNameColumn)
nRow is an index in a For loop, and I assumed nFullNameColumn was a variable associated with a named range on the sheet. How does nFullNameColumn have to be defined and assigned a value to make this statement work?
At the end of my last post I asked this question: With Fruit a defined name on a sheet, and nF defined by Dim nF as Range, what value does the statement Set nF = Range("Fruit") assign to nF? does anyone know the answer to that question?
Thanks for all the help,
Michael Trombetta
>________________________________
> From: paulschreinerindy <schreiner_paul@att.net>
>To: ExcelVBA@yahoogroups.com
>Sent: Monday, 15 October 2012, 17:38
>Subject: [ExcelVBA] Re: Abbreviations to Words
>
>
>
>Michael,
>In programming, it is common to create variables with
>names that help to identify the type of value that
>they are expected to contain.
>
>"nRow" doesn't stand for "number-Row",
>it means that this is a variable for the row,
>being stored in a "Numeric" data type (usually, integer)
>
>In this case, the nFullNameColumn variable is a numeric
>variable for the column containing the full name.
>In the case of column "E", it would have a value if 5.
>
>the Cells() object requires numeric parameters (row,column)
>You can use variables for either parameter,
>or even
>Cells(nRow,"E") would work.
>
>Yes, it is somewhat odd that "cells" is plural,
>yet Cells(r,c) represents a single cell.
>
>A range of cells can be expressed as:
>Range(Cells(nRow,nCol),Cells(nRow+10,nCol))
>But that's not what you were looking for.
>
>Hope this helps explain your issue.
>
>Paul
>
>--- In ExcelVBA@yahoogroups.com, Michael Trombetta <mickey11030@...> wrote:
>>
>> David,
>>
>> I'm not trying to work on multiple cells; I thought Cells(4, E2:E7) was a way to select E4, but I see that does not work.
>>
>> Early in this thread, Derek posted the following
>>
>> Cells(nRow, nFullNameColumn).Value = clxFruit.Item("key" & nAbbreviatedNameColumn)
>>
>> nRow is an index in a For loop, and I assumed nFullNameColumn was a variable associated with a named range on the sheet. How does nFullNameColumn have to be defined and assigned a value to make this statement work?
>>
>> At the end of my last post I asked this question: With Fruit a defined name on a sheet, and nF defined by Dim nF as Range, what value does the statement Set nF = Range("Fruit") assign to nF? does anyone know the answer to that question?
>>
>> Thanks for all the help,
>> Michael Trombetta
>>
>> [Non-text portions of this message have been removed]
>>
>
>From: Michael Trombetta <mickey11030@yahoo.com>
>To: "ExcelVBA@yahoogroups.com" <ExcelVBA@yahoogroups.com>
>Sent: Saturday, 13 October 2012, 19:39
>Subject: Re: [ExcelVBA] Abbreviations to Words
>
>
>Hi Derek,
>
>Thanks for being so patient with my slow learning process.
>
>When I wrote
>
>nF = Range("Fruit")
>nA = Range("Abb")
>nrow = 3
>Cells(nrow, nF).Value = clxFruit.Item("key" & Cells(nrow, nA).Value)
>
>I was not trying to fill all of the named range nF. I had done some experiments that convinced me that Cells(nrow, nF) referred to a particular cell. That is, if nF refers to Col P, and nrow is 3, the reference is to Cell P3.
>
>Scratch That! I just realized that in my experiment, the named ranges I used were single cells. I created a named range nPay that referred to a cell with a value of 9 and nHours to a cell with a value of 30. The statement Cells(13,3) = nPay * nHours worked, and that convinced me (incorrectly) that Cells(nrow, nA) would also work. Enlightenment. Better late than never.
>
>You mentions that processing could be speeded up if arrays were used, so that all the processing was done within VBA. You said you had discussed that in an earlier post. I'd appreciate your pointing me to that post.
>
>Thank you for showing me the 4 ways of using Cells. That will be very useful.
>
>Regards,
>Michael Trombetta
>From: Derek Turner <g4swy@yahoo.com>
>To: "ExcelVBA@yahoogroups.com" <ExcelVBA@yahoogroups.com>
>Sent: Tuesday, 9 October 2012, 19:39
>Subject: Re: [ExcelVBA] Abbreviations to Words
>
>
>Dear Mickey
>
>You have to get the collection items out one by one, you cannot use range methods.
>
>So :-
>
>Sub Main()
>Dim clxFruit As New Collection
> clxFruit.Add Range("B2").Value, "key" & Range("A2").Value
> clxFruit.Add Range("B3"), "key" & Range("A3").Value
> clxFruit.Add Range("B4"), "key" & Range("A4").Value
> Debug.Print clxFruit.Count
> Cells(2, 3).Value = clxFruit.Item(("key" & Range("A2").Value))
> Cells(3, 3).Value = clxFruit.Item(("key" & Range("A3").Value))
> Cells(4, 3).Value = clxFruit.Item(("key" & Range("A4").Value))
>' or what you really wanted was :-
>Dim nRow As Long
> For nRow = 2 To 4
> Cells(nRow, "c").Value = clxFruit.Item(("key" & Cells(nRow, "a").Value))
> Next nRow
>End Sub
>
>By the way I use .Value just in case the default property is not .Value and don't bother with Set shtSht1 = Sheets("Sheet1") if that is the active workbook. (sorry Yahoo format not working)
>
>Don't use Integer as there is no such thing in Win32 .
>
>Lastly . Do not abbreviate object names. There is no tax on keystrokes. - "Abb", shtSht1, nA etc
>
>Regards
>_From: Derek Turner <g4swy@yahoo.com>
>To: "ExcelVBA@yahoogroups.com" <ExcelVBA@yahoogroups.com>
>Sent: Thursday, 11 October 2012, 20:11
>Subject: Re: [ExcelVBA] Abbreviations to Words
>
>
>Dear Michael
>
>My comment referred to your code :-
>
>nF = Range("Fruit")
>nA = Range("Abb")
>nrow = 3
>Cells(nrow, nF).Value = clxFruit.Item("key" & Cells(nrow, nA).Value)
>
>You say that "Abb is the name I gave to a column of abbreviation, and Fruit the name of the cells which I want to contain the names"
>This looks like you are attempting to fill the named range nF all at once from the named range nA. ? VBA does not support this.
>
>You have to do the look-ups one by one, in a loop.
>
>In :-
>
> Cells(2, 3).Value = clxFruit.Item(("key" & Range("A2").Value))
>
>"A2" is one single value combining the row and column indices.
>In the loop you want to increment the row index but keep the columns constant, so :-
>Cells(nRow, "c").Value = clxFruit.Item(("key" & Cells(nRow, "a").Value))
>
>Notice that the following expressions are equivalent :-
>Debug.Print Cells(2, 1)
>Debug.Print Cells(2, "a")
>Debug.Print Range("a2")
>Debug.Print Range("a" & 2))
>
>The reason for using Cells is that you can define rows and columns independently.
>
>I hope this is more clear ?
>
>Regards
>
>
>Derek +++_._,_.___
>Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (19)
>Recent Activity: * New Members 8
>Visit Your Group
>----------------------------------
>Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.
>
>----------------------------------
>Visit our ExcelVBA group home page for more info and support files:
>http://groups.yahoo.com/group/ExcelVBA
>
>----------------------------------
>More free tutorials and resources available at:
>http://www.mousetrax.com
>
>----------------------------------
>
>Switch to: Text-Only, Daily Digest • Unsubscribe • Terms of Use • Send us Feedback
>.
>
>
>
>
[Non-text portions of this message have been removed]
| Reply via web post | Reply to sender | Reply to group | Start a New Topic | Messages in this topic (31) |
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.
----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA
----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com
----------------------------------
Tidak ada komentar:
Posting Komentar