VB macros in new Excel

[Howard Brazee]

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

 

[Howard Brazee]

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

 

[trip_to_tokyo]

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.

 

[Gord Dibben]

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

You will find the UDF in a general module.

Developer Tab>Visual 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.

Button>Excel Options>Popular>Show Developer Tab in Ribbon.


Gord Dibben MS Excel MVP

 

[Gord Dibben]

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

Button>Excel Options>Resources>About etc.


Gord

 

[trip_to_tokyo]

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.

Thanks.

 

[Howard Brazee]

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

View>Macros 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 Tab>Visual 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.

Button>Excel Options>Popular>Show Developer Tab in Ribbon.


Gord Dibben MS Excel MVP

--
"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

 

[Bob Umlas]

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

 

[Howard Brazee]

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.

--
"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

 

[Rick Rothstein]

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

 

[Howard Brazee]

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

 

[Howard Brazee]

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

Button>Excel Options>Resources>About 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

 

[Howard Brazee]

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

 

[Howard Brazee]

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

 

[Howard Brazee]

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

 

[Gord Dibben]

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

 

[Howard Brazee]

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

 

[Gord Dibben]

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

 

[Howard Brazee]

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

 

[Howard Brazee]

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