VBA to EXCEL Formula

C

Cameron

Hi all,

Have the following code in my Workbook:
Private Sub Worksheet_Activate()
Dim X As Integer
For X = 2 To ActiveWorkbook.Worksheets.Count
Sheets(1).Cells(X + 8, 2).Value = ""
Sheets(1).Cells(X + 8, 2).Value = ActiveWorkbook.Worksheets(X).Name
Next
End Sub

And currently this results in the following from B10:B24 ...
Jan04-Feb04
Dec03-Jan04
Nov03-Dec03
Oct03-Nov03
Sep03-Oct03
Aug03-Sep03
Jul03-Aug03
Jun03-Jul03
May03-Jun03
Apr03-May03
Mar03-Apr03
Feb03-Mar03
Jan03-Feb03
Dec02-Jan03
Nov02-Dec02


I'd like to know if there is any way I can get away from the VBA and stick
with a formula instead.
Why ???
When ever I leave the page and come back it is incredibly slow to
refresh/update.

Cheers,
Cameron
 
N

Norman Harker

Hi Cameron!

Manual entry to B10:
'Jan04-Feb04
Formula in B11:
=TEXT(DATE(MID(B10,4,2)+100,VLOOKUP(LEFT(B10,3),{"Jan",1;"Feb",2;"Mar"
,3;"Apr",4;"May",5;"Jun",6;"Jul",7;"Aug",8;"Sep",9;"Oct",10;"Nov",11;"
Dec",12},2,FALSE),1)-1,"mmmyy")&"-"&TEXT(DATE(RIGHT(B10,2)+100,VLOOKUP
(MID(B10,7,3),{"Jan",1;"Feb",2;"Mar",3;"Apr",4;"May",5;"Jun",6;"Jul",7
;"Aug",8;"Sep",9;"Oct",10;"Nov",11;"Dec",12},2,FALSE),1)-1,"mmmyy")

Copy down

Watch out for the line wrap above; I usually find it best to copy and
paste to the formula bar.


--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
L

Leo Heuser

Hi Cameron

Here's one way to do it, using an
Excel 4.0 macro:

1. Choose Insert > Name > Define
2. In "Names in workbook" enter "shname" (E.g)
without qoutes.
3. In "Refers to" (or similar) enter this formula

=GET.WORKBOOK(1)&TEXT(NOW(),"")
The formula creates a horizontal array containing
the sheet names.

4. Press "Add" and OK.

&TEXT(NOW(),"") concatenates nothing, but
is only used to trick Excel to update the formula,
each time you change the name on a sheet tab.

In B10 enter

=MID(INDEX(shname,ROW()-ROW($B$10)+2),FIND("]",
INDEX(shname,ROW()-ROW($B$10)+2))+1,1000)

The expression
ROW()-ROW($B$10)+2
is used as a counter, so if you start in a cell different
from B10, e.g. H12 change this expression to
ROW()-ROW($H$12)+2 etc.
To start with tab 1 use ROW()-ROW($B$10)+1

Copy B10 down to B24 with the fill handle (the little
square in the lower right corner of the cell).

If it's OK to display the name of the workbook as well,
you can use the formula

=INDEX(shname,ROW()-ROW($B$10)+2)

Please notice, that trying to copy the formula in B10
to another sheet will crash Excel, if you are using
a version prior to Excel 2002.
 
C

Cameron

Hi Norman,
Thanks for the reply, but not exactly what I'm hunting for.
Idealy, the user needs only `Create` and `Name` a new worksheet after the
`Control Page`(Sheet(1)) and the `Control Page` will automatically update
itself with the newly included worksheet and populate the required cells.
Hence the existing VBA.

The `Control Page` will eventually be protected and viewable only.

Regards,
Cameron
 
N

Norman Harker

Hi Cameron!

I don't think you'll get a formula to do that because formulas and
functions can't change the environment.

The details of capabilities of functions and formulas are based on:



170787 XL: Custom Functions Can't Change Microsoft Excel Environment

http://support.microsoft.com/default.aspx?scid=kb;en-us;170787



A Function or User Defined Function called by a formula in a worksheet
cell cannot change the environment of Microsoft Excel. This means that
such a function cannot do any of the following:



Ø Insert, delete, or format cells on the spreadsheet.

Ø Change another cell's value.

Ø Move, rename, delete, or add sheets to a workbook.

Ø Change any of the environment options, such as calculation
mode or screen views.

Ø Add names to a workbook.

Ø Set properties or execute most methods.


--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
C

Cameron

Hi Leo,
Many thanks for the reply and the explaination of what you provided -
greatly apreciated.
It works a treat for the worksheets that exist, but generates a #REF! error
(not surprisingly) in cells that don't yet corespond with a worksheet that
exists within the range B25:B33.
Being that the range on the worksheet(B10:B33) is larger than the number of
worksheets (excluding the first worksheet; or 'Control Page'(16pages)), the
remaining cells not yet assocated with a worksheet of it's own generate the
#REF! error. Well; until I add another worksheet in each month.
Any suggestions on how to overcome this??

Jan04-Feb04
Dec03-Jan04
Nov03-Dec03
Oct03-Nov03
Sep03-Oct03
Aug03-Sep03
Jul03-Aug03
Jun03-Jul03
May03-Jun03
Apr03-May03
Mar03-Apr03
Feb03-Mar03
Jan03-Feb03
Dec02-Jan03
Nov02-Dec02
#REF!#REF!
#REF!#REF!
#REF!#REF!
#REF!#REF!
#REF!#REF!
#REF!#REF!
#REF!#REF!
#REF!#REF!
#REF!#REF!

Cheers,
Cameron



Leo Heuser said:
Hi Cameron

Here's one way to do it, using an
Excel 4.0 macro:

1. Choose Insert > Name > Define
2. In "Names in workbook" enter "shname" (E.g)
without qoutes.
3. In "Refers to" (or similar) enter this formula

=GET.WORKBOOK(1)&TEXT(NOW(),"")
The formula creates a horizontal array containing
the sheet names.

4. Press "Add" and OK.

&TEXT(NOW(),"") concatenates nothing, but
is only used to trick Excel to update the formula,
each time you change the name on a sheet tab.

In B10 enter

=MID(INDEX(shname,ROW()-ROW($B$10)+2),FIND("]",
INDEX(shname,ROW()-ROW($B$10)+2))+1,1000)

The expression
ROW()-ROW($B$10)+2
is used as a counter, so if you start in a cell different
from B10, e.g. H12 change this expression to
ROW()-ROW($H$12)+2 etc.
To start with tab 1 use ROW()-ROW($B$10)+1

Copy B10 down to B24 with the fill handle (the little
square in the lower right corner of the cell).

If it's OK to display the name of the workbook as well,
you can use the formula

=INDEX(shname,ROW()-ROW($B$10)+2)

Please notice, that trying to copy the formula in B10
to another sheet will crash Excel, if you are using
a version prior to Excel 2002.

--
Best Regards
Leo Heuser

Followup to newsgroup only please.

Cameron said:
Hi all,

Have the following code in my Workbook:
Private Sub Worksheet_Activate()
Dim X As Integer
For X = 2 To ActiveWorkbook.Worksheets.Count
Sheets(1).Cells(X + 8, 2).Value = ""
Sheets(1).Cells(X + 8, 2).Value = ActiveWorkbook.Worksheets(X).Name
Next
End Sub

And currently this results in the following from B10:B24 ...
Jan04-Feb04
Dec03-Jan04
Nov03-Dec03
Oct03-Nov03
Sep03-Oct03
Aug03-Sep03
Jul03-Aug03
Jun03-Jul03
May03-Jun03
Apr03-May03
Mar03-Apr03
Feb03-Mar03
Jan03-Feb03
Dec02-Jan03
Nov02-Dec02


I'd like to know if there is any way I can get away from the VBA and stick
with a formula instead.
Why ???
When ever I leave the page and come back it is incredibly slow to
refresh/update.

Cheers,
Cameron
Click to expand...
Click to expand...
 
C

Cameron

Hi Norman,

I'm not wanting the XLS/or any function to change the environment.
Rather, have the user add a worksheet after the 'Control Page' and name the
Worksheet; that's all that's required by the user.

The 'Control Page' will pick up the details of the added Worksheet and show
details reflective of the worksheet on the Control Page.

Sorry, I may have been slightly ambiguous in my explaination.
Hope that clears the mud a little.

Cheers,
Cameron
 
L

Leo Heuser

You're welcome, Cameron.

Here's what you can do.

Enter this new formula in B10 instead of the old one:

=IF(ISERROR(INDEX(shname,ROW()-ROW($B$10)+2)),"",
MID(INDEX(shname,ROW()-ROW($B$10)+2),FIND("]",
INDEX(shname,ROW()-ROW($B$10)+2))+1,1000))

Copy it to B11:B33

The formula will leave a "blank" cell, if no sheet exists
for the corresponding cell.
Unfortunately adding a new sheet will not trigger the
calculate event, so the list will not be updated before
you press <F9>.
To overcome this, you can use the NewSheet event:

1. Right click a sheet tab and choose "View code"
2. For the project double click "ThisWorkbook"
in the project window (upper left of the screen)
3. Insert the code below in the righthand window.

Private Sub Workbook_NewSheet(ByVal Sh As Object)
Worksheets(1).Calculate
End Sub

Assuming the namelist B10:B33 in the *first* (1) sheet,
the code will recalc this sheet, each time a new sheet
is added , thereby updating the list.

--
Best Regards
Leo Heuser

Followup to newsgroup only please.


Cameron said:
Hi Leo,
Many thanks for the reply and the explaination of what you provided -
greatly apreciated.
It works a treat for the worksheets that exist, but generates a #REF! error
(not surprisingly) in cells that don't yet corespond with a worksheet that
exists within the range B25:B33.
Being that the range on the worksheet(B10:B33) is larger than the number of
worksheets (excluding the first worksheet; or 'Control Page'(16pages)), the
remaining cells not yet assocated with a worksheet of it's own generate the
#REF! error. Well; until I add another worksheet in each month.
Any suggestions on how to overcome this??

Jan04-Feb04
Dec03-Jan04
Nov03-Dec03
Oct03-Nov03
Sep03-Oct03
Aug03-Sep03
Jul03-Aug03
Jun03-Jul03
May03-Jun03
Apr03-May03
Mar03-Apr03
Feb03-Mar03
Jan03-Feb03
Dec02-Jan03
Nov02-Dec02
#REF!#REF!
#REF!#REF!
#REF!#REF!
#REF!#REF!
#REF!#REF!
#REF!#REF!
#REF!#REF!
#REF!#REF!
#REF!#REF!

Cheers,
Cameron



Leo Heuser said:
Hi Cameron

Here's one way to do it, using an
Excel 4.0 macro:

1. Choose Insert > Name > Define
2. In "Names in workbook" enter "shname" (E.g)
without qoutes.
3. In "Refers to" (or similar) enter this formula

=GET.WORKBOOK(1)&TEXT(NOW(),"")
The formula creates a horizontal array containing
the sheet names.

4. Press "Add" and OK.

&TEXT(NOW(),"") concatenates nothing, but
is only used to trick Excel to update the formula,
each time you change the name on a sheet tab.

In B10 enter

=MID(INDEX(shname,ROW()-ROW($B$10)+2),FIND("]",
INDEX(shname,ROW()-ROW($B$10)+2))+1,1000)

The expression
ROW()-ROW($B$10)+2
is used as a counter, so if you start in a cell different
from B10, e.g. H12 change this expression to
ROW()-ROW($H$12)+2 etc.
To start with tab 1 use ROW()-ROW($B$10)+1

Copy B10 down to B24 with the fill handle (the little
square in the lower right corner of the cell).

If it's OK to display the name of the workbook as well,
you can use the formula

=INDEX(shname,ROW()-ROW($B$10)+2)

Please notice, that trying to copy the formula in B10
to another sheet will crash Excel, if you are using
a version prior to Excel 2002.

--
Best Regards
Leo Heuser

Followup to newsgroup only please.

Cameron said:
Hi all,

Have the following code in my Workbook:
Private Sub Worksheet_Activate()
Dim X As Integer
For X = 2 To ActiveWorkbook.Worksheets.Count
Sheets(1).Cells(X + 8, 2).Value = ""
Sheets(1).Cells(X + 8, 2).Value = ActiveWorkbook.Worksheets(X).Name
Next
End Sub

And currently this results in the following from B10:B24 ...
Jan04-Feb04
Dec03-Jan04
Nov03-Dec03
Oct03-Nov03
Sep03-Oct03
Aug03-Sep03
Jul03-Aug03
Jun03-Jul03
May03-Jun03
Apr03-May03
Mar03-Apr03
Feb03-Mar03
Jan03-Feb03
Dec02-Jan03
Nov02-Dec02


I'd like to know if there is any way I can get away from the VBA and stick
with a formula instead.
Why ???
When ever I leave the page and come back it is incredibly slow to
refresh/update.

Cheers,
Cameron
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Count occurance of a string across numerous worksheets 3
Help with Select ... Into in VBA 4
Consolidate Sources 6
Sum the last 12 columns ? 2
Sum the last 12 columns ? 1

Top