Range naming

  • Thread starter Thread starter BigJim
  • Start date Start date
B

BigJim

Can anyone point me to help topics on named ranges. I
cannot find the syntax to set the rSource and rDest
variables below.

My puzzle has two arrays, an array of scores and an array
of results seperated by several rows of blank rows.
Each array has a name in the top-left cell (NS_Scores,
NS_Percentages) and each array is approx 8 rows * 32
columns. Both the widh and height will be variable once I
have better control of the code.

I want to apply a user-defined function to populate the
results array (one column at a time) from the Scores
array.

How do I pass references to individual columns of the two
arrays to the user defined function?

Public CalcPercentage (rSource as range, rDest as range)
as boolean
' some logic to produce the Dest array from the
Source.
exit function


....
dim rSource as range
dim i as integer
dim iNoRows as integer
iNoRows = 8
for i=1 to 24
rSource = range(range("MyScores").offset(i, col),
range("MyScores").offset(iNoRows, col))
rDest = range(rSource).offset("MyPercentage"))
dim bResult as boolean
bResult = CalcPercentage (rSource, rDest)
next i
....
 
BigJim said:
I want to apply a user-defined function to populate the
results array (one column at a time) from the Scores
array.
Click to expand...

A user-defined function can only alter cells by returning a value into
the cell(s) from which it was called.

So you could, for example, have a function that returns an array of
values to the destination range and array-enter the function call into
that range using Ctrl+Shift+Enter.

The function might look like:

Function CalcPercentage(rSource As Range)
Dim rResults()
Dim iRow As Integer
ReDim rResults(1 To rSource.Rows.Count, 1 To 1)
For iRow = 1 To rSource.Rows.Count
rResults(iRow, 1) = something or other
Next
CalcPercentage = rResults
End Function

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - reply in newsgroup
 
Thanks. My problem is with the syntax of the
"rSource = " line below.

dim iNoRows as integer
dim rSrc as range
iNoRows = 8
dim iColNo as integer
for iColNo=1 to 24
rSrc = range(range("MyScores").offset(iColNo, 1),
range("MyScores").offset(iColNo, iNoRows))
 
BigJim said:
Thanks. My problem is with the syntax of the
"rSource = " line below.
Click to expand...

Putting Set in front of rSrc would be helpful.
That's why it will be saying "Object variable not Set" to you.
You always have to use Set when making an object variable refer to a
particular object, as here.

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - reply in newsgroup
 
Back
Top