INDIRECT and defined named ranges

[dusty]

G'Day,

I've succesfully created a defined named range using INDIRECT.

I've even created a defined named range using the union of two
INDIRECT expressions (=INDIRECT(FirstName),INDIRECT(SecondName)).

I've also created a defined named range (ShiftedData) with
=INDIRECT(FirstName)+100*Adjustment.

The aim here is to allow modifications to base data to perform
sensitivity analysis.

If I enter {=ShiftedData} into a range of cells, all is well; I get
the same effect as manually adding 100*Adjustment to each value in
INDIRECT(FirstName).

However, where I hit problems is when I try to pass ShifttedData to
any function (e.g. VLOOKUP).

All I get is #Value!

If I try a VBA subroutine and refer to a range via

set rngTest = Range("ShiftedData")

all I get is runtime error 1004, Method 'Range' of object '_Global'
failed.

Is anybody able to explain why adding the constant is fine in one
sense (the values are displayable in the worksheet) but not usable in
VBA?

Thanks for your time,

Clive

 

[Roger Govier]

Hi Clive

Can you post a small example of your data, and the formulae you have
used for your named ranges

 

[Bob Phillips]

When you create a name formula and use that it is automatically evaluated,
so =SUM(ShiftedData) works fine.

Your VLOOKUP probably does not work because ShiftedData returns a 1D array,
and if your VLOOKUP is looking toe return column 2 or more, there isn't one

The VBA won't work because the formula doesn't return a range, it returns an
array of values.

 

[dusty]

G’Day Roger, Bob,

I’m working in the field of financial markets, the data is a surface
of option volatilities (m x n range of data from data provider) but I
want to create an m x 2 dynamic range consisting of dates and 1 column
of volatilities.

The range is dynamic due to both changing valuation date and changing
stock.

Sample data:
Range of dates named Date12SepKOSPI (general form is
Dateddmmm<StockID>)

19-Sep-08
13-Oct-08
12-Nov-08
12-Dec-08
12-Mar-09

This is the first column from the m x n grid of data.

Range of volatilities named VOL12SepKOSPI (general form is
VOLddmmm<StockID>)

29.88
29.76
29.6
29.43
29.04

This is the middle column of data from the m x n grid.

In a cell for date I enter 12/09/2008, and in another cell I enter the
stock ID, KOSPI.

These two inputs are used, in a cell named VolatilityDate, to create
the string Date12SepKOSPI, while in a cell named VolatilityName they
are used to create the string VOL12SepKOSPI.

In another cell named VolAdj I can enter a number such as 1%.

Next I created a defined named range, VolatilityCurve, using the union
formula:

=INDIRECT(VolatilityDate),INDIRECT(VolatilityName)

If all has worked well, this should act like:

19/09/2008 29.88
13/10/2008 29.76
12/11/2008 29.60
12/12/2008 29.43
12/03/2009 29.04

At first, I couldn’t use the standard VLOOKUP to extract volatilities
(second column) because the union consisted of two areas. I wrote a
function that combined to two areas into a single variant array and
all was well.

Then a request came to permit sensitivity analysis based on parallel
shifts to the volatility. This is where the VolAdj comes in; I hope to
use the VolAdj value to create a new table of values by adding 1% to
each of the values in the table above. (Above 29.88 is interpreted as
29.88%, so to add 1% I need to add 1.)

My first attempt was to modify the formula for VolatilityCurve to
=INDIRECT(VolatilityDate),INDIRECT(VolatilityName)+100*VolAdj

This was accepted by Excel but the UDF failed. Believing the cause was
the INDIRECT(VolatilityName)+100*VolAdj, I started to experiment.

Create a defined name, VolTest via the formula
=INDIRECT(VolatilityDate)+100*VolAdj.

(Note I’m using date here not name; I thought it might be easier for
the VLOOKUP test to follow.)

In a 5 x 1 block of empty cells, type =VolTest, and enter as an array
formula.

You should see values appear and by changing the value in the VolAdj
cell, the values from {=VolTest} will change accordingly. A value of
1% causes an increment of 1 day.

So the defined name range has been created and appears as expected on
the worksheet.

Set the value in VolAdj to 0% and in an empty cell type

=VLOOKUP(SomeDate, VolTest,1)

Where SomeDate is a date value that would return a result (eg
25/9/2008); a value is returned.

Change VolAdj to 1% and a value is still returned by the lookup
function.

So here, VolTest is working as expected on the worksheet. It only
appears to fail when used by VBA.

Using this simple code,

Sub dummy()

Dim rTest As Range

Dim vNewTest As Variant

Set rTest = Range("VolTest")

MsgBox "Done"

End Sub

Step through. You should get the error message from my first post.

Change the formula for the VolTest range to

=INDIRECT(VolatilityDate)

Step through the dummy subroutine again. This time you should get
beyond the set rTest line.

I’m wondering why this is so.

Any thoughts appreciated and sincere apologies for the lengthy post.

Thanks for your time and patience,

Clive

 

[Charles Williams]

Works OK for me.

What does =SUM(ShiftedData) give you?

Charles

 

[dusty]

Charles,

May I ask what works OK for you? Did you manage to get the dummy
subroutine to get beyond set rTest = when VolTest was set to
=INDIRECT(VolatilityDate)+100*VolAdj?

I haven't tried =SUM(ShiftedData) because: (i) I don't need that
ability, and (ii) I suspect it may be similar to the VLOOKUP example.
It works on a worksheet but not when passed to a VBA routine.

Thanks for your input though.

Clive

 

[Charles Williams]

Hi Clive,

Fred is a Named Range defined as
=Sheet1!$A$1:$A$5
ShiftedData is a Named Formula defined as
=INDIRECT("fred")+100

I could use ShiftedData in a Vlookup and it worked fine.

Set rTest=Range("ShiftedData") won't work, as Bob Phillips explained,
because ShiftedData is a named formula, not a named range, and you
cannot Set a range object= a formula

If you are using VBA to do this work then I would avoid using INDIRECT
(and INDIRECT inside Names) as its unneccessary and IMHO problematic.

regards
Charles

 

[dusty]

Charles,

Thanks for your patience; I think I missed the subtlty of Bob's
reference to named formula rather than named range.

And I was thown off the track when =INDIRECT(VolatilityDate) worked
(because it's a named range?) but =INDIRECT(VolatilityDate)+100 didn't
(because it's a named formula?).

I'd like to discuss your last paragraph further (always keen to learn
more about Excel) but I guess this is not the correct forum for that.

Thanks again for your patient explanation.

Clive