Help me to learn...

[Adrian]

Dear all,
Please be gentle with me! Having spent many years
programming in XLM, I am belatedly learning VBA. Progress
is slow because I'm used to the old ways.

How do I make a variable whose type is a range? I've tried
various things but I keep getting typename String, hence
later lines of code that need a Range will fail.
Two reasons for wanting to do this...
1. So that I can use variables to abbreviate my code.
2. So that I can change the value of a variable and then
make the same macro manipulate lots of different regions
of workbook/sheet.

Sub test()
src = Workbooks("Data.xls").Names("Mdatabase").RefersTo
MsgBox src & vbCrLf & TypeName(src)
'the next line fails
toprow = src.Row
End Sub

 

[Frank Kabel]

Hi Adrian
try something like
sub foo()
dim rng as range
dim cell as range
set rng = Workbooks("Data.xls").Range("A1:A5")
for each cell in rng
msgbox cell.value
next
end sub

 

[Jan Karel Pieterse]

Hi Adrian,

Sub test()
src = Workbooks("Data.xls").Names("Mdatabase").RefersTo
MsgBox src & vbCrLf & TypeName(src)
'the next line fails
toprow = src.Row
End Sub

Make that:

Sub test()
Dim src as Range
Set src = Workbooks("Data.xls").Names("Mdatabase").RefersToRange
MsgBox src.Address & vbCrLf & TypeName(src)
'the next line fails (not anymore!)
toprow = src.Row
End Sub

Regards,

Jan Karel Pieterse
Excel MVP
www.jkp-ads.com

 

[Guest]

Sadly, that doesn't work either.
I get runtime error 91 on the src=Workbooks... line

In my original, the string that is printed out is
=modules!$A$1:$K$125 which is an accurate description.

(The workbook data.xls contains three sheets, and each
sheet has a differently named region on it. )

I'm using Excel2000 and Windows2000.

 

[Jan Karel Pieterse]

Sadly, that doesn't work either.

I get runtime error 91 on the src=Workbooks... line

Strange, because the code I gave works fine for me. Please check it
again.

Regards,

Jan Karel Pieterse
Excel MVP
www.jkp-ads.com

 

[Dick Kusleika]

Adrian

Make sure you are using

Set src = Workbooks(...

not just

src = Workbooks(...

when assigning object variables, like Range, you need the Set statement.