Miranic
asked on
VBA Convert Array to Range Array
Hi Experts,
I have a bit of code that references a range in excel. What I am trying to do is rewrite the code so that it does not have any cell references. Eg so rather than reference A1:A4 I want to load the values from A1 to A4 into an array using a "Loader" then pass the ByRef array to my main applications. The trouble with this approach is that the imbeded functionality available when using a range is lost. For example, "X.Rows.Count" where X is my array fails when I pass in the array, but works fine when I pass it in as a range array. The debugger error is 'Run-time error '424': Object Required.
Am I able to convert an array to a range array? Remember I do not want to have any cell references so pasting it into excel and referencing it as a range is not an option.
I am open to suggestions.
Thanks,
SM
I have a bit of code that references a range in excel. What I am trying to do is rewrite the code so that it does not have any cell references. Eg so rather than reference A1:A4 I want to load the values from A1 to A4 into an array using a "Loader" then pass the ByRef array to my main applications. The trouble with this approach is that the imbeded functionality available when using a range is lost. For example, "X.Rows.Count" where X is my array fails when I pass in the array, but works fine when I pass it in as a range array. The debugger error is 'Run-time error '424': Object Required.
Am I able to convert an array to a range array? Remember I do not want to have any cell references so pasting it into excel and referencing it as a range is not an option.
I am open to suggestions.
Thanks,
SM
Why not just create an array of range objects?
Something like this perhaps, or am I missing the whole point.
[code]
Dim rng As Range
Dim arrRng() As Range
Dim cl As Range
Dim I As Long
Set rng = Range("A1:A10")
ReDim arrRng(rng.Count - 1)
For Each cl In rng.Cells
Set arrRng(I) = cl
I = I + 1
Next cl
[/code]
PS You might want to look into Collections, really depends what you are actually trying to achieve.
Something like this perhaps, or am I missing the whole point.
[code]
Dim rng As Range
Dim arrRng() As Range
Dim cl As Range
Dim I As Long
Set rng = Range("A1:A10")
ReDim arrRng(rng.Count - 1)
For Each cl In rng.Cells
Set arrRng(I) = cl
I = I + 1
Next cl
[/code]
PS You might want to look into Collections, really depends what you are actually trying to achieve.
ASKER
Thanks imnorie,
I am doing something similar in my code, so the example you provided is great.
Using your example,
When I request ? rng.rows.count, I get the value 10 returned.
When I request ? arrRng(Unbound).Rows.Count
What am I missing?
Thanks,
SM
I am doing something similar in my code, so the example you provided is great.
Using your example,
When I request ? rng.rows.count, I get the value 10 returned.
When I request ? arrRng(Unbound).Rows.Count
What am I missing?
Thanks,
SM
What is Unbound?
If it's undefined then VBA will treat it as an empty variable and return null/zero/empty string or something.
So arrRng(Unbound) essentially means arrRng(0), which is the first element in the array.
Since each element of the array is a single cell range the count of cells/columns/rows is 1.
That how I see it anyway - I've not had a chance to test it, I'll do that once Excel has completed the last 160,000+ iterations it appears to be doing.
If it's undefined then VBA will treat it as an empty variable and return null/zero/empty string or something.
So arrRng(Unbound) essentially means arrRng(0), which is the first element in the array.
Since each element of the array is a single cell range the count of cells/columns/rows is 1.
That how I see it anyway - I've not had a chance to test it, I'll do that once Excel has completed the last 160,000+ iterations it appears to be doing.
ASKER
imnorie,
I think we are nearly their. What I should have said, is that I want to know the number or records in arrRng. I understand your Unbound comment, but was hoping that was a way to capture all, clearly not (or I'm using incorrectly).
SM
I think we are nearly their. What I should have said, is that I want to know the number or records in arrRng. I understand your Unbound comment, but was hoping that was a way to capture all, clearly not (or I'm using incorrectly).
SM
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
No
I presume you are passing it to an array as you want to manipulate the data before writing back. Else you would use a range variable instead
I note that you could use UBound to return the row count, but the other properties (say parent name) won't be available. Perhaps you could hold the range info in a range variable, and the range data in the array
Cheers
Dave