General Question regarding passing cells, ranges, cell values through subs & functions

[marston.gould]

This is related to another question I had - but I think - in
considering what I'm trying to do, I must be missing something about
passing arguments, particularly those that I mentioned.

What I'm trying to do is
1) Check to see if a user has entered a value into a cell range
2) Look at all the values within the same row as the cell entered
3) Then, based on the values in particular columns of that
intersection, perform some analysis.

I think I'm confusing how to pass the range vs it address vs its value
- both in the case when a range is a
single cell and when its multiple.

In Sheet1:

Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
Dim WatchRange as Range
Dim Cell As Range
Dim CheckRange As Range
Dim Cell2 As Range
Set WatchRange = Range("A:F")
For Each TargetCell in Target
If Union(Cell,WatchRange).Address = WatchRange.Address Then
' Only check if value is entered, not cleared
If Range(Cell.Address).Value <> "" Then
Set CheckRange = EntryIsValid(Cell,WatchRange)
Call AnalyzeIt(CheckRange)
End If
End If
Next Cell
End Sub

In ThisWorkbook
' I want this to load on opening of worksheet by user....
ValidRange1-4 are on a different worksheet than the one they are
entering data in as will be predefined.
The contain data that says what values are okay in col n1,n2,etc. if
col m has a particular value.


Private Sub Workbook_Open()
' Pick range on other worksheet that has definitions of what pairs of
values are good.
Dim rng1 As Range
rng1 = Range("ValidRange1") <---- should I explicitly say on what
worksheet this is, or if there is
rng2 = Range("ValidRange2") only one range
of cells named "ValidRange" am I okay.
rng3 = Range("ValidRange3")
rng4 = Range("ValidRange4")
arr1 = rng1.Value
arr2 = rng2.Value
arr3 = rng3.Value
arr4 = rng4.Value
End Sub

In Module

Public arr1() , arr2(), arr3(), arr4() As Variant
Function EntryIsValid(TestRange,FullRange) As Range
Dim TheRange As Range
Set The Range = Intersect(TestRange.EntireRow,FullRange)
EntryIsValid = TheRange
End Function

Sub AnalyzeIt(CheckRange As Range)
Dim arr(6) <---- arr(1) holds Col A , arr(2) holds Col B , etc.
Dim res(2000,6)
' Probably need a routine here to load res(x,y) with all the valid
unique entries and then
' create a routine that checks, when making comparisons below,
that the number being
' loaded into res(x,y) each subsequent time is already there -
otherwise, ignore.
If Not arr(1) Is Nothing Then
For i = 1 to UBound (arr1,1)
Count = 1
If arr(1).Value = arr1(i,1) Then
res(Count,2) = arr1(i,2)
Count = Count + 1
End If
Count = 1
If arr(1).value = arr2(i,1) Then
res(Count,3) = arr2(i,2)
res(Count,4) = arr2(i,3)
Count = Count +1
End If
Count = 1
If arr(1).value = arr3(i,1) Then
res(Count,5) = arr3(i,2)
Count = Count +1
End If
Next i
For i = 1 to UBound(arr2,1)
..
..
..
similar as above
Next i

 

[Tom Ogilvy]

in Worksheet_Change you never set cell to anything, so when you use it, it
is nothing.

you probably meant

For Each Cell in Target
rather than
For Each TargetCell in Target

In the thisworkbook module or in a sheet module, you should qualify a named
range with it worksheet

Dim rng1 As Range
rng1 = Range("ValidRange1")

raises an error because you must use Set rng1 = to initialize rng1. Since
you don't declare rng2, etc, they will be variant arrays. No sense using the
rng variable, then assigning their values to arrays. Do it directly.

Worksheets("Data").Range("ValidRange1")

you have a space in TheRange

Set The Range = Intersect(TestRange.EntireRow,FullRange)

so that will cause an error. Also, you already checked that TestRange is
interior to FullRange, so you don't need EntireRow on the end.

Dim arr(6) <---- arr(1) holds Col A , arr(2) holds Col B , etc.

not if you don't initialize it with those values and I don't see anywhere
that you do.

If Not arr(1) Is Nothing Then

if arr(1) is not a reference to an object this could raise an error. In any
event, it probably isn't the test you want.

Your passing a reference to a range, so that should be OK.

Public arr1() , arr2(), arr3(), arr4() As Variant

someone already told you that this is not the proper way to declare
variables. In this case, it makes no difference since if you don't specify
a type for each variable it will be variant by default. I also told you
that use Arr1() gains you nothing and makes your code incompatible with
xl97, but that is your choice. To make it compatible leave off the ().