How to suppress all code in an Excel Workbook

[C2345]

We have a workbook which when opened, shows the user critical data on a given page and then erases it. Opening the VBA editor in the workbook shows no code in the form of modules nor coded behind the worksheets. There are no forms in the workbook. I have tried to open the workbook using code from another workbook so that any macros would be suppressed (there are none) and have used the setting: application.enablevents = False to try to suppress stop any code as well as putting the macro security setting to very high.

Nothing seems to stop the code. Is there a way to prevent this code from running?

Alternatively, is there a way to extract the data from the worksheets. I've tried to pull the data out with Access but Access said the file couldn't be read. Perhaps the data was encrypted?

 

[SkipVought]

Hi,

In the VB Editor, what happens if you Edit > Find for the word sub or function, using Search in Current Project?


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[C2345]

Searching for sub or function with the setting of current project yields "search text not found" for both. I suspect that the code that erases the data also erases itself.

 

[SkipVought]

What happens when you open the workbook with macros disabled?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[C2345]

At the highest security setting, the data are still erased. A friend provided a clue and by setting the system date back a year the macro doesn't run. Even so, on opening the workbook I was unable to find any macro's present. Apparently, the code erasing the worksheet is hidden elsewhere in the file. Any suggestions on where it might be? The code was written by a disgruntled employee who is no longer with the company and is set to trigger after a certain date. I'm not sure where to look. There are no standard modules, no classes, no forms and no code behind the worksheets.

 

[combo]

1. Is there any macro warning message?
2. Does it happens in all machines, do you have any other than ms excel add-ins installed?
3. Non-vba possibilities: What kind of data is it? Any automatic query to external data or pivot table refreshes when the file is opened (very hidden sheet with NOW or TODAY function).

combo

 

[C2345]

Thanks Combo but still no solution.

There is no warning macro message.

It happens on all machines I have tested. There are no addins running.

There is no external data source. I receieved the file from another source and it works whether I am connected to the internet or not. All that the code is doing is erasing the existing data and leaving the name of the employee in it's place. A parting gift from the disgruntled employee.


There are two hidden sheets that I do not have access to that are protected by a password I don't have. However, examination of the sheets in the visual basic editor shows no code associated with the sheets.


I would put the worksheet up for examination but it's 16 mB in size and has sensitive information that I can't remove because it's protected.

At this point I'm wondering if the employee knew how to modify the source code for Excel.

 

[AnotherHiggins]

What makes you think a macro doing this?

-> shows the user critical data on a given page and then erases it.
So they see the data for a second, then it is "erased"?? How long do they see the data before it disappears? What cell is active after the information disappears? Do/did the cells that formerly held critical data contain formulas?

-> Alternatively, is there a way to extract the data from the worksheets.
If the workbook is named WBName.xls and is located in C:\FolderPath\, then to get the data from Sheet1 you could put this formula into a brand new workbook in cell A1:
[tab][COLOR=blue white]='C:\FolderPath\[WBName.xls]Sheet1'!A1[/color]
Drag the formula right, then down to fill in the data.

NOTES:
[tab]* This will only bring over values, not formulas
[tab]* The formula will return zeros for cells that are empty on the source sheet

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[C2345]

Thanks AnotherHiggins for providing a method of getting the data. However, this isn't the main problem at the moment.

The data in the workbook consists of 7000+ rows of data that consist of names and addresses. The workbook was being used as a database of useful contacts. As such there were no formulas used. The 7000 rows of data appear for approximately 1 second. After that all of the rows are erased and a single cell ~ G8 is left with the name of the person who left the company.

The question at this point is how is data erased in that I can find no evidence of code that is doing this. Further clues that the actions are run by code is the observation that if the system date is set back one year the date are not erased. It seems the person who did this did so with the expectation that they would be asked to leave and wanted to leave behind a momento of their time there.

 

[combo]

It looks like there is no code that hides data. There can be possible some other reasons that cause it. There is why I asked for external data of pivot table.

First of all check if you have access to all sheets. To do this, count visible tabs in excel, go to visual basic editor and compare with sheets in the vba project. If you find a worksheet thast is not visible, try to set its Visible property ('properties' window) to '-1 - xlSheetVisible'.

If the sheet where data disappears is not protected, select a cell with this data, check if you can see anything in the formula bar, if so, conditional formatting is applied. To remove it, select all cells and go Format>Conditional Formatting, remove formatting (all conditions).
You can also chech if you have a name (insert>names) that contains NOW or TODAY, it can be a trigger to some calculations.

If you do not have access to specific sheet, you can try to get formulas. Create custom function:

Function GetFormula(r As Range) As String
GetFormula = r.Formula
End Function

Add new worksheet and starting from A1 refer with this function to cells in the protected sheet.

The last, I thought that there can be a pivot table report that refreshes at startup (source data in hidden sheet), you have not told us if the problem refers to pivot table.

combo

 

[xlbo]

If there really isn't any code then this can be done by conditional formatting but that would still leave the actual values in the cells - there could be a conditional format applied that sets the font colour to white if todays date is > a set date

This would be easy to find out as you would be able to "see" the data in the formula bar if you selected a cell

One other thing to look at is whether it is using a digital certificate - if this is applied, it is possible that code will run even if macro security is high - I notice you say there is no code on modules or in the sheets - did you check whether there is code in the workbook module?

The most likely thing the coe is doing is not deleting the data but simply hiding the sheet it is on and making a different sheet visible - if that is the case then the methods John or Combo have suggested may retrieve data from a hidden sheet

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[C2345]

Thanks combo/xlbo

Your suggestions about viewing the formulas helped. The workbook has 3 worksheets CData (visible), D1 (hidden), D2 (hidden), all are password protected. The used ranges on the three pages are:

CData = $A$1:$M$7651
D1 = $A$1:$M$7652
D2 = $A$1:$N$7651

There is only 1 Name in the workbook and it's refersto property = 'D1'!$A$1:$M$7651

Row 1 in sheets CData and D1 holds the column headers:
ID, LastName, FirstName ...

Rows 2 on hold M7651 hold data

Row 7652 holds the following information

A7652 = =Now()
B7652 = =DATE(2008,4,1)
C7652 = ""

Worksheet D2 holds the following formulas

A1 = =IF('D1'!$A$7652<'D1'!$B$7652,IF('D1'!A1="","",'D1'!A1),"")
B1 = =IF('D1'!$A$7652<'D1'!$B$7652,IF('D1'!B1="","",'D1'!B1),"")
A2 = =IF('D1'!$A$7652<'D1'!$B$7652,IF('D1'!A2="","",'D1'!A2),"")
C3 = =IF('D1'!$A$7652<'D1'!$B$7652,IF('D1'!C3="","",'D1'!C3),"")

So what is happening is that after 4/1/2008 Worksheet D2 will display a series of blanks rather than the information on D1 which is apparently a copy of the worksheet CData.

What I'm not sure of now is how the data is made to appear on worksheet CData as there are no references to worksheet D1 in any of the cells (in terms of a formula). All cells are blank (save for the one in cell C3)

 

[combo]

Depending on protection you can:
1. there is an access to cell B7652:
change formula to =NOW()

2. all worksheets are protected, but you can add new ones (test it on a copy):
- add worksheet,
- add code in standard module (I changed to local names):

Function GetFormula(r As Range) As Variant
GetFormula = r.FormulaLocal
End Function

Sub EnterFormulas()
Dim c As Range
For Each c In Selection
    c.FormulaLocal = c.Text
Next c
End Sub

- use function GetFormula to get formulas from protected sheet to unprotected one, sheet by sheet,
- copy results and replace as text (paste as values), do not edit them,
- delete protected sheets, rename added sheets as original ones,
- select region with text, execute sthe procedure (EnterFormulas).
Now you can change date and brush up the workbook - remove the IFs.

3. You can't add sheets:
Either try (2) in another workbook or search for free tools to unprotect sheets - the protection is weak so unprotection is fast.

combo

 

[xlbo]

What sheet is visible when you open the workbook (before the data is "deleted" ?

What sheet is visible after the data has been "deleted" ?

Seems like CData is the important worksheet - you should be able to get all the data from it by using the methods that John and Combo have mentioned - you should also be able to break the password on it - search google for sheet password cracking in VBA. We don't tend to discuss it in these forums for obvious reasons but google has no such limitations!

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[C2345]

Thanks combo/xlbo:

I've recreated the workbook in a new workbook by copying the value & or formula in each cell of the used range using the approach suggested by combo. CData is where the data are stored. D1 appears to be a copy of CData. Each cell in D2 has a formula that returns either the value of the same cell in D1 or a blank value if the date is latter than 4/1/2008.

What is not clear to me is how D1 is displayed after this date and made to appear as if it is CData. (xlbo - the workbook opens up on page CData and ends with the same worksheet showing after the data have been erased).

I don't believe you can hide code in the VB Editor without putting a password on the code?

Is it possible to hide a worksheet without code via conditional formatting?

Looking at cells on CData I couldn't find any conditional formatting.

 

[combo]

You can't add executable code without security warning.

There are a lot of ways you can hide data without code, what is actually your case:
- the whole workbook: window>hide/unhide toggles workbook's visibility, this is the way personal.xls works. More 'hidden' workbook can be sreated by saving it as the add-in, in this case it is not visible in excel interface,
- worksheet: format>worksheet>hide/unhide, the same for worksheet. In the VB editor or by code the visibility can be set to very hidden, not visible for the user,
- rows/columns: format>row/column>hide/unhide: a part of worksheet, sometimes difficult to notice when headings are not visible,
- formulas: format>cells, protection tab, tick 'hide', protect worksheet. Cell's text is visible, the formula in the formula bar is not displayed.

combo

 

[HughLerwill]

>You can't add executable code without security warning.

Unless you go Tools - Macros - Security - Trusted Publishers - and tick the Trust access to VB Project box

 

[C2345]

Combo:

You're right there are many ways to hide data without using code but with the exception of the conditional formatting don't they all involve manually changing a property of the workbook or worksheet?

What intrigues me about this workbook is that there is apparently no code running, no conditional formatting, no external data, no pivot-tables and still when the workbook is opened data is shown and then is erased. Examination of the worksheet where the data was shows no information in the cells which would eliminate conditional formatting. similarly no rows are hidden. Can you propose a mechanism for this?

 

[combo]

Your formulas depend on current date. The workbook was saved when the condition in IF funstion dispayed some data. When you open the workbook, formulas are recalculated and values replaced by empty string. It can take some time in sace of big set of data, so you can see as the contents of worksheet disappears.
You can try to open the workbook with calculation set to manual (Tools>Options).

combo

 

[C2345]

The formulas are on the D2 worksheet but the changes are observed on the CData page which has only raw data - no formulas.

Unless something like code is run(and I don't know how it could be without seeing it)I'm not sure how the appearance of change could be made. To do this:

The values for D2 would have to be transferred to CData

or

CData is hidden, renamed D2 is made visible and renamed to CData so it appears CData is changed

Any ideas?