I have a spreadsheet that hasn't been changed since my work upgraded
my Office. I don't remember where they hid the part that shows what
version of Excel I have. But that's normal with this new Office
hardly anything I used to know how to find is hidden.

I need to change a VB macro, but the View/Macros did not show anything
and I know there is some VB in this spreadsheet. I saved this
spreadsheet from compatibility mode to a XSLM form, opened it again,
and still can't find the macros.

How do I get to the Visual Basic?

--
"In no part of the constitution is more wisdom to be found,
than in the clause which confides the question of war or peace
to the legislature, and not to the executive department."

- James Madison

On Mon, 04 Jan 2010 11:58:46 -0700, Howard Brazee
wrote:

I have a spreadsheet that hasn't been changed since my work upgraded
my Office. I don't remember where they hid the part that shows what
version of Excel I have. But that's normal with this new Office
hardly anything I used to know how to find is hidden.

I need to change a VB macro, but the View/Macros did not show anything
and I know there is some VB in this spreadsheet. I saved this
spreadsheet from compatibility mode to a XSLM form, opened it again,
and still can't find the macros.

How do I get to the Visual Basic?

Oh, my column has the formula:

=SumEvalString(G2:INDEX(G2:CN2,MATCH(Base_Year,$G$ 1:$CN$1,-1)))

I don't remember how this works (What is CN2?) - but it might be a
clue about where my macros are.

--
"In no part of the constitution is more wisdom to be found,
than in the clause which confides the question of war or peace
to the legislature, and not to the executive department."

- James Madison

In EXCEL 2007 take the following action:-

- open the Workbook

- you should get a button towards the top of the screen called:-

Option . .

(to the right of the words:-

Security Warning Macros have been disabled).

- Click this Options button / select Enable this content / hit OK

- View / Macros group / Macros / View Macros /

- select the Macro you wish to Edit (by clicking on its name) and hit the
Edit button

- Microsoft Visual Basic editor should open

Please hit Yes if my comments have helped.

Thanks.


"Howard Brazee" wrote:

I have a spreadsheet that hasn't been changed since my work upgraded
my Office. I don't remember where they hid the part that shows what
version of Excel I have. But that's normal with this new Office
hardly anything I used to know how to find is hidden.

I need to change a VB macro, but the View/Macros did not show anything
and I know there is some VB in this spreadsheet. I saved this
spreadsheet from compatibility mode to a XSLM form, opened it again,
and still can't find the macros.

How do I get to the Visual Basic?

--
"In no part of the constitution is more wisdom to be found,
than in the clause which confides the question of war or peace
to the legislature, and not to the executive department."

- James Madison
.

SumEvalString is a User Defined Function and will not show up in the list of
macros in ViewMacros.

You will find the UDF in a general module.

Developer TabVisual Basic to open the VBE.

Double-click on a module to open.............look for the UDF

If Developer Tab is not shown on the ribbon you must place it there.

ButtonExcel OptionsPopularShow Developer Tab in Ribbon.


Gord Dibben MS Excel MVP

On Mon, 04 Jan 2010 12:10:27 -0700, Howard Brazee wrote:

On Mon, 04 Jan 2010 11:58:46 -0700, Howard Brazee
wrote:

I have a spreadsheet that hasn't been changed since my work upgraded
my Office. I don't remember where they hid the part that shows what
version of Excel I have. But that's normal with this new Office
hardly anything I used to know how to find is hidden.

I need to change a VB macro, but the View/Macros did not show anything
and I know there is some VB in this spreadsheet. I saved this
spreadsheet from compatibility mode to a XSLM form, opened it again,
and still can't find the macros.

How do I get to the Visual Basic?

Oh, my column has the formula:

=SumEvalString(G2:INDEX(G2:CN2,MATCH(Base_Year,$G $1:$CN$1,-1)))

I don't remember how this works (What is CN2?) - but it might be a
clue about where my macros are.

BTW.............to see version of Excel

ButtonExcel OptionsResourcesAbout etc.


Gord

On Mon, 04 Jan 2010 11:59:08 -0800, Gord Dibben <gorddibbATshawDOTca wrote:

SumEvalString is a User Defined Function and will not show up in the list of
macros in ViewMacros.

You will find the UDF in a general module.

Developer TabVisual Basic to open the VBE.

Double-click on a module to open.............look for the UDF

If Developer Tab is not shown on the ribbon you must place it there.

ButtonExcel OptionsPopularShow Developer Tab in Ribbon.


Gord Dibben MS Excel MVP

On Mon, 04 Jan 2010 12:10:27 -0700, Howard Brazee wrote:

On Mon, 04 Jan 2010 11:58:46 -0700, Howard Brazee
wrote:

I have a spreadsheet that hasn't been changed since my work upgraded
my Office. I don't remember where they hid the part that shows what
version of Excel I have. But that's normal with this new Office
hardly anything I used to know how to find is hidden.

I need to change a VB macro, but the View/Macros did not show anything
and I know there is some VB in this spreadsheet. I saved this
spreadsheet from compatibility mode to a XSLM form, opened it again,
and still can't find the macros.

How do I get to the Visual Basic?

Oh, my column has the formula:

=SumEvalString(G2:INDEX(G2:CN2,MATCH(Base_Year,$ G$1:$CN$1,-1)))

I don't remember how this works (What is CN2?) - but it might be a
clue about where my macros are.

If you don't get the Options button (towards the top of the screen that I
mention in my earlier posting) it's because there are no macros, "attached"
to that Workbook: perhaps that's why you are not seeing anything in View
Macros.

You have to enable this Options button because if you dont the Edit button
(further down the process that I talk about) will be greyed out.

Please hit Yes if my comments have helped.

Thanks.

"Howard Brazee" wrote:

I have a spreadsheet that hasn't been changed since my work upgraded
my Office. I don't remember where they hid the part that shows what
version of Excel I have. But that's normal with this new Office
hardly anything I used to know how to find is hidden.

I need to change a VB macro, but the View/Macros did not show anything
and I know there is some VB in this spreadsheet. I saved this
spreadsheet from compatibility mode to a XSLM form, opened it again,
and still can't find the macros.

How do I get to the Visual Basic?

--
"In no part of the constitution is more wisdom to be found,
than in the clause which confides the question of war or peace
to the legislature, and not to the executive department."

- James Madison
.

On Mon, 04 Jan 2010 11:59:08 -0800, Gord Dibben <gorddibbATshawDOTca
wrote:

SumEvalString is a User Defined Function and will not show up in the list of
macros in ViewMacros.

ViewMacros is empty it's not finding any macros at all. Even in
"Open all workbooks", which scares me a bit - as other documents have
VB in them).

I'm trying to remember if I may have opened this document at home with
Office for Mac. If so, could macros get lost from a document?

I did a search of "all files and folders" in this folder containing
the string SumEvalString. It found the .xls document, but not the
..xlsm document. I found this odd, as I can see it in the .xlsm
cells. So I opened the .xls file (in compatibility mode) and tried
to read the macro. No dice. I am not finding it.

I'm assuming I need to find it before continuing.

You will find the UDF in a general module.

Developer TabVisual Basic to open the VBE.

Double-click on a module to open.............look for the UDF

If Developer Tab is not shown on the ribbon you must place it there.

ButtonExcel OptionsPopularShow Developer Tab in Ribbon.


Gord Dibben MS Excel MVP

On Mon, 04 Jan 2010 12:10:27 -0700, Howard Brazee wrote:

On Mon, 04 Jan 2010 11:58:46 -0700, Howard Brazee
wrote:

I have a spreadsheet that hasn't been changed since my work upgraded
my Office. I don't remember where they hid the part that shows what
version of Excel I have. But that's normal with this new Office
hardly anything I used to know how to find is hidden.

I need to change a VB macro, but the View/Macros did not show anything
and I know there is some VB in this spreadsheet. I saved this
spreadsheet from compatibility mode to a XSLM form, opened it again,
and still can't find the macros.

How do I get to the Visual Basic?

Oh, my column has the formula:

=SumEvalString(G2:INDEX(G2:CN2,MATCH(Base_Year,$ G$1:$CN$1,-1)))

I don't remember how this works (What is CN2?) - but it might be a
clue about where my macros are.

--
"In no part of the constitution is more wisdom to be found,
than in the clause which confides the question of war or peace
to the legislature, and not to the executive department."

- James Madison

You can also get to the code by pressing F5 and entering SumEvalString
Bob Umlas
Excel MVP

"Howard Brazee" wrote in message
...
On Mon, 04 Jan 2010 11:58:46 -0700, Howard Brazee
wrote:

I have a spreadsheet that hasn't been changed since my work upgraded
my Office. I don't remember where they hid the part that shows what
version of Excel I have. But that's normal with this new Office
hardly anything I used to know how to find is hidden.

I need to change a VB macro, but the View/Macros did not show anything
and I know there is some VB in this spreadsheet. I saved this
spreadsheet from compatibility mode to a XSLM form, opened it again,
and still can't find the macros.

How do I get to the Visual Basic?

Oh, my column has the formula:

=SumEvalString(G2:INDEX(G2:CN2,MATCH(Base_Year,$G$ 1:$CN$1,-1)))

I don't remember how this works (What is CN2?) - but it might be a
clue about where my macros are.

--
"In no part of the constitution is more wisdom to be found,
than in the clause which confides the question of war or peace
to the legislature, and not to the executive department."

- James Madison

On Mon, 4 Jan 2010 11:54:01 -0800, trip_to_tokyo
wrote:

In EXCEL 2007 take the following action:-

- open the Workbook

- you should get a button towards the top of the screen called:-

Option . .

(to the right of the words:-

Security Warning Macros have been disabled).

I don't see any of the above. I do have SELFCERT.EXE in this
directory and believe I ran it back when.

- Click this Options button / select Enable this content / hit OK

- View / Macros group / Macros / View Macros /

- select the Macro you wish to Edit (by clicking on its name) and hit the
Edit button

I don't see any Macros. Could it have disappeared somehow?

- Microsoft Visual Basic editor should open

Please hit Yes if my comments have helped.

Thanks.


"Howard Brazee" wrote:

I have a spreadsheet that hasn't been changed since my work upgraded
my Office. I don't remember where they hid the part that shows what
version of Excel I have. But that's normal with this new Office
hardly anything I used to know how to find is hidden.

I need to change a VB macro, but the View/Macros did not show anything
and I know there is some VB in this spreadsheet. I saved this
spreadsheet from compatibility mode to a XSLM form, opened it again,
and still can't find the macros.

How do I get to the Visual Basic?

--
"In no part of the constitution is more wisdom to be found,
than in the clause which confides the question of war or peace
to the legislature, and not to the executive department."

- James Madison
.


--
"In no part of the constitution is more wisdom to be found,
than in the clause which confides the question of war or peace
to the legislature, and not to the executive department."

- James Madison

Or, alternately (assuming the OP is only interested in the main version
number and not the sub-version number parts), execute this line in the
Immediate Window of the VB editor...

? Application.Version

--
Rick (MVP - Excel)


"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
BTW.............to see version of Excel

ButtonExcel OptionsResourcesAbout etc.


Gord

On Mon, 04 Jan 2010 11:59:08 -0800, Gord Dibben <gorddibbATshawDOTca
wrote:

SumEvalString is a User Defined Function and will not show up in the list
of
macros in ViewMacros.

You will find the UDF in a general module.

Developer TabVisual Basic to open the VBE.

Double-click on a module to open.............look for the UDF

If Developer Tab is not shown on the ribbon you must place it there.

ButtonExcel OptionsPopularShow Developer Tab in Ribbon.


Gord Dibben MS Excel MVP

On Mon, 04 Jan 2010 12:10:27 -0700, Howard Brazee
wrote:

On Mon, 04 Jan 2010 11:58:46 -0700, Howard Brazee
wrote:

I have a spreadsheet that hasn't been changed since my work upgraded
my Office. I don't remember where they hid the part that shows what
version of Excel I have. But that's normal with this new Office
hardly anything I used to know how to find is hidden.

I need to change a VB macro, but the View/Macros did not show anything
and I know there is some VB in this spreadsheet. I saved this
spreadsheet from compatibility mode to a XSLM form, opened it again,
and still can't find the macros.

How do I get to the Visual Basic?

Oh, my column has the formula:

=SumEvalString(G2:INDEX(G2:CN2,MATCH(Base_Year, $G$1:$CN$1,-1)))

I don't remember how this works (What is CN2?) - but it might be a
clue about where my macros are.

I know what the formula was supposed to do:

=SumEvalString($C3:INDEX($C3:CN3,MATCH(Base_Year,$ C$1:$CN$1,-1)))

This looks at all cells between $C3 and Base_year (which happens to be
cell $B$35).

Those cells would have a format of '12-5
or '12-5:1:2
or '12-5:1:2:C

The calculation for each cell would be
(5 - 12)
or (12 - 5 + ($b$35 * 1) + ($b$36 * 2)
or (12 - 5 + ($b$35 * 1) + ($b$36 * 2) + $b$37


I suspect my macro is gone, so I would need to:
1. Recreate it.
2. Find out how it disappeared and make sure that doesn't happen
again.

--
"In no part of the constitution is more wisdom to be found,
than in the clause which confides the question of war or peace
to the legislature, and not to the executive department."

- James Madison

On Mon, 04 Jan 2010 12:01:53 -0800, Gord Dibben <gorddibbATshawDOTca
wrote:

BTW.............to see version of Excel

ButtonExcel OptionsResourcesAbout etc.

That's right. I wonder why it is so very different from the Windows
standard.

--
"In no part of the constitution is more wisdom to be found,
than in the clause which confides the question of war or peace
to the legislature, and not to the executive department."

- James Madison

On Mon, 04 Jan 2010 13:35:52 -0700, Howard Brazee
wrote:

I know what the formula was supposed to do:

=SumEvalString($C3:INDEX($C3:CN3,MATCH(Base_Year, $C$1:$CN$1,-1)))

This looks at all cells between $C3 and Base_year (which happens to be
cell $B$35).

Those cells would have a format of '12-5
or '12-5:1:2
or '12-5:1:2:C

The calculation for each cell would be
(5 - 12)
or (12 - 5 + ($b$35 * 1) + ($b$36 * 2)
or (12 - 5 + ($b$35 * 1) + ($b$36 * 2) + $b$37


I suspect my macro is gone, so I would need to:
1. Recreate it.
2. Find out how it disappeared and make sure that doesn't happen
again.

--
"In no part of the constitution is more wisdom to be found,
than in the clause which confides the question of war or peace
to the legislature, and not to the executive department."

- James Madison

On Mon, 04 Jan 2010 13:35:52 -0700, Howard Brazee
wrote:

Those cells would have a format of '12-5
or '12-5:1:2
or '12-5:1:2:C


Odd, this seems to work if column C doesn't have a colon. If the 2nd
option is in column d it works, but if it's in column c, I get an
error.

The following are my results in columns B, C, & D

179 9-7 12-4:3:0:C
M 13:3:0:1 11-5:1:0 9-7

This implies that there is a broken macro somewhere. I just need to
find it.

--
"In no part of the constitution is more wisdom to be found,
than in the clause which confides the question of war or peace
to the legislature, and not to the executive department."

- James Madison

On Mon, 4 Jan 2010 15:21:07 -0500, "Bob Umlas"
wrote:

You can also get to the code by pressing F5 and entering SumEvalString
Bob Umlas

Reference is not valid. I see the aliases of various cells there,
but not my macro.

--
"In no part of the constitution is more wisdom to be found,
than in the clause which confides the question of war or peace
to the legislature, and not to the executive department."

- James Madison

With your workbook open hit Alr +F11 to open the VBE

Select your workbook/project and expand it.

Do you see any modules?

Open them to see what's in them.

Also check in sheet modules just in case someone stored the UDF in one of
those.

Also see Bob's reply about F5 and type the name for a shortcut method to the
UDF

That would be done from the Excel window.


Gord


On Mon, 04 Jan 2010 13:20:23 -0700, Howard Brazee wrote:

On Mon, 04 Jan 2010 11:59:08 -0800, Gord Dibben <gorddibbATshawDOTca
wrote:

SumEvalString is a User Defined Function and will not show up in the list of
macros in ViewMacros.

ViewMacros is empty it's not finding any macros at all. Even in
"Open all workbooks", which scares me a bit - as other documents have
VB in them).

I'm trying to remember if I may have opened this document at home with
Office for Mac. If so, could macros get lost from a document?

I did a search of "all files and folders" in this folder containing
the string SumEvalString. It found the .xls document, but not the
.xlsm document. I found this odd, as I can see it in the .xlsm
cells. So I opened the .xls file (in compatibility mode) and tried
to read the macro. No dice. I am not finding it.

I'm assuming I need to find it before continuing.

You will find the UDF in a general module.

Developer TabVisual Basic to open the VBE.

Double-click on a module to open.............look for the UDF

If Developer Tab is not shown on the ribbon you must place it there.

ButtonExcel OptionsPopularShow Developer Tab in Ribbon.


Gord Dibben MS Excel MVP

On Mon, 04 Jan 2010 12:10:27 -0700, Howard Brazee wrote:

On Mon, 04 Jan 2010 11:58:46 -0700, Howard Brazee
wrote:

I have a spreadsheet that hasn't been changed since my work upgraded
my Office. I don't remember where they hid the part that shows what
version of Excel I have. But that's normal with this new Office
hardly anything I used to know how to find is hidden.

I need to change a VB macro, but the View/Macros did not show anything
and I know there is some VB in this spreadsheet. I saved this
spreadsheet from compatibility mode to a XSLM form, opened it again,
and still can't find the macros.

How do I get to the Visual Basic?

Oh, my column has the formula:

=SumEvalString(G2:INDEX(G2:CN2,MATCH(Base_Year, $G$1:$CN$1,-1)))

I don't remember how this works (What is CN2?) - but it might be a
clue about where my macros are.

On Mon, 4 Jan 2010 12:07:01 -0800, trip_to_tokyo
wrote:


If you don't get the Options button (towards the top of the screen that I
mention in my earlier posting) it's because there are no macros, "attached"
to that Workbook: perhaps that's why you are not seeing anything in View
Macros.

You have to enable this Options button because if you don’t the Edit button
(further down the process that I talk about) will be greyed out.

Please hit Yes if my comments have helped.

I can't hit "yes", from here.

So my SumEvalString works partly, and the macro that defines what it
does is missing. I wonder why it works partly, how I lost the
macro, and what I must be careful about in the future to make sure I
don't lose it again.

Next I will need to figure out how to re-write that macro.

--
"In no part of the constitution is more wisdom to be found,
than in the clause which confides the question of war or peace
to the legislature, and not to the executive department."

- James Madison

You keep calling it "a macro"

It is not a macro...............it is a Function

If it did not exist in your workbook you would get #NAME? in the cells with
that formula.


Gord

On Mon, 04 Jan 2010 13:44:45 -0700, Howard Brazee wrote:

On Mon, 04 Jan 2010 13:35:52 -0700, Howard Brazee
wrote:

Those cells would have a format of '12-5
or '12-5:1:2
or '12-5:1:2:C


Odd, this seems to work if column C doesn't have a colon. If the 2nd
option is in column d it works, but if it's in column c, I get an
error.

The following are my results in columns B, C, & D

179 9-7 12-4:3:0:C
M 13:3:0:1 11-5:1:0 9-7

This implies that there is a broken macro somewhere. I just need to
find it.

On Mon, 04 Jan 2010 13:06:43 -0800, Gord Dibben <gorddibbATshawDOTca
wrote:

You keep calling it "a macro"

It is not a macro...............it is a Function

Sorry.

If it did not exist in your workbook you would get #NAME? in the cells with
that formula.

I'm not getting #NAME? in those cells now.

--
"In no part of the constitution is more wisdom to be found,
than in the clause which confides the question of war or peace
to the legislature, and not to the executive department."

- James Madison

On Mon, 04 Jan 2010 12:48:00 -0800, Gord Dibben <gorddibbATshawDOTca
wrote:

With your workbook open hit Alr +F11 to open the VBE

That's what I needed! Thanks.

(It seems that there would be a menu item that I could eventually find
to get me here. Trying a bunch of random keystroke combinations
really isn't practical - fortunately I have you guys).

Select your workbook/project and expand it.

Do you see any modules?

Open them to see what's in them.

I found a bunch of Excel Objects sheets, Forms, Modules, & Class
modules, as well as a VBAPROJECT that wants a password.

module1 has my code.


Also check in sheet modules just in case someone stored the UDF in one of
those.

Sheet 1 contains:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub

Also see Bob's reply about F5 and type the name for a shortcut method to the
UDF

That would be done from the Excel window.

I replied to him.

Now I should be able to figure out what is wrong with the code.


Again, thanks.

--
"In no part of the constitution is more wisdom to be found,
than in the clause which confides the question of war or peace
to the legislature, and not to the executive department."

- James Madison

On Mon, 04 Jan 2010 12:48:00 -0800, Gord Dibben <gorddibbATshawDOTca
wrote:

With your workbook open hit Alr +F11 to open the VBE

Everything's solved. I will copy the above line into my spreadsheet
so that I can find it next year.

By looking at the code, I was able to trace down my data errors.


Thanks everybody for your help.

--
"In no part of the constitution is more wisdom to be found,
than in the clause which confides the question of war or peace
to the legislature, and not to the executive department."

- James Madison

You could gotten there using Developer TabVisual Basic as I posted earlier.


Gord

On Mon, 04 Jan 2010 14:19:06 -0700, Howard Brazee wrote:

On Mon, 04 Jan 2010 12:48:00 -0800, Gord Dibben <gorddibbATshawDOTca
wrote:

With your workbook open hit Alr +F11 to open the VBE

That's what I needed! Thanks.

(It seems that there would be a menu item that I could eventually find
to get me here. Trying a bunch of random keystroke combinations
really isn't practical - fortunately I have you guys).

Select your workbook/project and expand it.

Do you see any modules?

Open them to see what's in them.

I found a bunch of Excel Objects sheets, Forms, Modules, & Class
modules, as well as a VBAPROJECT that wants a password.

module1 has my code.


Also check in sheet modules just in case someone stored the UDF in one of
those.

Sheet 1 contains:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub

Also see Bob's reply about F5 and type the name for a shortcut method to the
UDF

That would be done from the Excel window.

I replied to him.

Now I should be able to figure out what is wrong with the code.


Again, thanks.

Happy to hear you deciphered Alr as Alt


Gord

On Mon, 04 Jan 2010 14:26:46 -0700, Howard Brazee wrote:

On Mon, 04 Jan 2010 12:48:00 -0800, Gord Dibben <gorddibbATshawDOTca
wrote:

With your workbook open hit Alr +F11 to open the VBE

Everything's solved. I will copy the above line into my spreadsheet
so that I can find it next year.

By looking at the code, I was able to trace down my data errors.


Thanks everybody for your help.

On Mon, 04 Jan 2010 13:32:55 -0800, Gord Dibben <gorddibbATshawDOTca
wrote:

You could gotten there using Developer TabVisual Basic as I posted earlier.

Ahh, I found the setting to turn this tab on. Apparently its
default was unchecked, which is why I didn't see it when you mentioned
it.

I guess the assumption is that a developer will know about this.

--
"In no part of the constitution is more wisdom to be found,
than in the clause which confides the question of war or peace
to the legislature, and not to the executive department."

- James Madison

But I did explain how to place the Developer Tab on the Ribbon when I
mentioned it in my very first post.

If Developer Tab is not shown on the ribbon you must place it there.

ButtonExcel OptionsPopularShow Developer Tab in Ribbon.

If you read all of the content of a reply you will generally get what you
need faster.


Gord

On Mon, 04 Jan 2010 14:44:43 -0700, Howard Brazee wrote:

On Mon, 04 Jan 2010 13:32:55 -0800, Gord Dibben <gorddibbATshawDOTca
wrote:

You could gotten there using Developer TabVisual Basic as I posted earlier.

Ahh, I found the setting to turn this tab on. Apparently its
default was unchecked, which is why I didn't see it when you mentioned
it.

I guess the assumption is that a developer will know about this.