PDA

View Full Version : How to check the code of a macro which calculates the value of a certain cell?



bernard96
08-01-2016, 05:37 AM
Hello,

I would like to take a look at the code of an macro which is used to calculate the value in particular cell of the Excel 2007 .xlsm file.
How can this be done?

When I click on that particular cell, it shows the following function:


=IF(D8<>"Rooftop Only (See Ch. 4 of Guidelines)",'D) Subsurface Depth Sizing'!E19,"NA")

Does that mean that the macro which is calculating the value of that particular cell is: 'D) Subsurface Depth Sizing'?

The problem is that when I open the Microsoft Visual Basic and take a look at the objects inside of the VBA project, the 'D) Subsurface Depth Sizing' contains only this code:


Option Explicit
Private Sub Worksheet_Activate()
Range("A1:U1").Select
ActiveWindow.Zoom = True
If ActiveWindow.Zoom > 100 Then ActiveWindow.Zoom = 100
Range("A1:A1").Select
End Sub

And to make it more strange, all other objects contain the same code.

Why is that so?

Does this mean that the 'D) Subsurface Depth Sizing'? is actually not the Macro which calculates the value for particular cell?

Here is the .xlsm file: app.box.com/s/5soz2008frzqkg6zmoca5rz8katpz3h4
I am interested in cell F110 on "Inputs and Results" worksheet.

I apologize for my lack of VBA Excel knowledge.

Regards,
Bernard

p45cal
08-01-2016, 06:47 AM
Without looking at the file, the formula:
=IF(D8<>"Rooftop Only (See Ch. 4 of Guidelines)", 'D) Subsurface Depth Sizing'!E19,"NA")
is not related to macros at all. It simply says:
if cell D8 doesn't contain Rooftop Only (See Ch. 4 of Guidelines), then show what's in cell E19 of the sheet called D) Subsurface Depth Sizing, otherwise show N/A.
cell E19 of the sheet called D) Subsurface Depth Sizing contains a longish formula.
Taking a look at your file, there is such a hidden sheet which you can view by right-clicking a sheet's tab, choosing Unhide… and selecting that sheet. Those sheets don't appear to be protected so don't change anything!

The macros behind each sheet only reset the zoom to 100 and bring the view back to the top-left of the sheet when that sheet is switched-to from another sheet.

bernard96
08-01-2016, 07:48 AM
Dear Pascal,

You helped me a lot! A was not aware of the "Unhide" command.

Can you just a bit more precise on this please:

Those sheets don't appear to be protected so don't change anything!


Thank you.

p45cal
08-01-2016, 08:00 AM
Well, the whole workbook has clearly been constructed with some thought and planning. Many cells are dependent on values in yet other cells and I wouldn't like to be given the job of adjusting or redesigning it. One little wrong value somewhere is very likely indeed to give wrong results somewhere else (worse, in multiple places). Clearly the sheets have been hidden, so the designer didn't want or expect them to be seen by the user, and for that reason he may not have bothered to protect them, like he's protected some other sheets. Coming to this forum and finding out how to 'get behind the scenes' of a workbook empowers you to make changes, but if you do so, the ramifications could be small, or they could be huge. If you make a change, you HAVE to know everywhere that that change impinges - as I say, I wouldn't like to be given that job!

bernard96
08-01-2016, 08:06 AM
I understand now. Thank you for the detailed clarification and for solving my problem.

Greetings to the Guildford and wonderful Surrey Hills!

bernard96
08-01-2016, 02:35 PM
Do you mind if I ask one more question please?

When I "Unhide" some of the worksheets, or even when I unhide all of the worksheets, and then Save - the next time the .xlsm file is opened, the worksheets are hidden again. I tried saving to a new .xlsm file (Macro-Enabled-Workbook), but again the worksheets are hidden.

Why is this happening?
Why can't the worksheets stay unhidden once I save the changes made to the file?

p45cal
08-01-2016, 04:25 PM
By design. It's in the Workbook Open event and the Workbook before close event.

bernard96
08-01-2016, 11:36 PM
So that means that I can not Unhide the worksheet, Save, and expect it to be unhidden next time I open the .xlsm file?

Aussiebear
08-02-2016, 12:55 AM
Not unless you change the workbook open event code. In this case the workbook open code sets which sheets are visible to the user

p45cal
08-02-2016, 01:00 AM
Correct.

Unless you change/disable the code.

bernard96
08-02-2016, 01:02 AM
Not unless you change the workbook open event code.


Correct.

Unless you change/disable the code.

Thank you. Both of you. That is beyond my knowledge.

Aussiebear
08-02-2016, 02:10 PM
Show us the code and we can walk you through it.

bernard96
08-02-2016, 03:03 PM
Thank you Aussiebear.
I do not know which code to shear? Code from which Sheet? from Visual Basic Editor?