Return Tracking, VBA

  • Thread starter Thread starter C Brandt
  • Start date Start date
C

C Brandt

How do I capture the name of the active sheet (so I can come back) when I
select and activate another sheet under macros?

Craig
 
First, don't worry about the name. Try:

Dim wsOldActiveSheet As Worksheet

Set wsOldActiveSheet = ActiveSheet

'do your stuff

wsOldActiveSheet.Activate
 
Dim ws as Worksheet
Set ws = ActiveSheet
'your code here
ws.Activate 'to return to the original sheet
 
Thanks Guys:

This solution solves half the problem, but the name of the active worksheet
is necessary as it carrys some of the data that I need.

Is it possible to get access to the name?

Craig
 
One way:

MsgBox wsOldActiveSheet.Name

C Brandt said:
Thanks Guys:

This solution solves half the problem, but the name of the active worksheet
is necessary as it carrys some of the data that I need.

Is it possible to get access to the name?

Craig
Click to expand...
 
My bet is that you don't need the name.

Instead of:
worksheets(wsOldActiveSheet.name).range("a1").clearcontents
you could use:
wsOldActiveSheet.range("a1").clearcontents
 
JE:



You nailed it. Exactly what I was looking for.



One of the tools used to predict the future in financial market is the
comparison of two different moving averages. The length of the two averages
used defines the relative success of the tool for any given year.

This workbook is an analysis of NASDAQ prices from 1972 to 2007.

I am doing this analysis just to see how accurate this predictor can be.
First I calculate the returns of a simple buy & hold then compare it to the
returns of going Long & Short using the relative results of the moving
averages, determining when I go Long or Short. I range the short period from
50 days to 200 days and the long period from 201 days to 400 days.

I divide the resulting returns into years, one sheet each, and then a 30 000
cell array on each sheet. I analyze each cell and apply an interior color
depending on its relative success, compared to the Buy & Hold strategy
(Light to dark red on those cells that do worst then the B&H and light to
dark green for those that out produce the B&H strategy). The name of the
sheets for the years are: 1972, 1972, 1973, etc. When I select a cell in one
of these large arrays, I want to recalculate the returns, then place a chart
next to the array so that I can get a better idea of why the results change.
Being able to identify the worksheet, gives me the year that I need to
chart.



This whole effort is an exercise, and a spare time effort, designed to help
me understand the financial markets and more importantly learn Excel/VBA.
Without the help of this venue, it would have been a losing effort, so thank
you, all that help me out, I truly appreciate it.



One must keep learning as they get older or they just fade away.
Thanks again,

Craig
 
Back
Top