macro 4 sheet Mysterious appearing Worksheets & Modules. Codes transferred to New Modules

Mysterious appearing Worksheets and Modules. Codes transferred to New Modules

(......Edit : around End of July 2016:
Wierdo 1 SOLVED

Wierdo 2 Not jet
http://www.excelforum.com/excel-prog...ml#post4440076 .....)


Hi,
This is weird - possibly a strange Bug?

Question:
Has anyone seen anything like this before?. Can anyone shed any light on what has happened?

So this happened:
A couple of weird things happened regarding mysterious Worksheet in two separate Files. I think it happened at about the same time. I did not notice exactly when it happened.

Weirdo 1
In this File
https://app.box.com/s/i0tfdc96xe2h2sacrpf1jb18nbz8wmdb
A new Worksheet suddenly appeared. It has the name Makro1 ( I am in German Excel: Makro is the name for Macro in English). This Worksheet does not appear in the VBAProject Window. Further if I right click on the tab, the option to show code does not appear ( Or rather it is there but is shown in a lighter shade than the other options and does not respond to a click on it. )
I appear to be able to use the Worksheet itself “normally” as an Excel Worksheet
If I run a short code to list out all Worksheets in this File, then this Worksheet “Makro1” does not appear in that list
I saved this file for prosperity and to share here, and then I deleted that rogue Worksheet, resaved under a different name and went on to do a large amount of work with the File with no noticeable ill effects, and the problem did not seem to re appear.

_......
Weirdo 2
In this File
https://app.box.com/s/b8duyzaw2bmokbezv1kwrbhngfuq9th3

I had originally 6 Worksheets with these Code Names and (Names)
Tabelle7(Head)
Tabelle8(DB)
Tabelle9(Leith2)
Tabelle10(Tabelle3MiscTidies)
Tabelle11(FoodsLookUpTable)
Tabelle12(Empty)
( The list above I got from a short code which lists out all worksheet Code names and (Names) in the Immediate ( Ctrl+G ) Window)
All Worksheet code Modules had working code in them

_2a) Suddenly 6 new “Modules” Appeared. ( I have no idea why !! ). They have the Code Names Tabelle1, Tabelle2, Tabelle3, Tabelle4, Tabelle5, Tabelle6. ( They do not appear in a list produced by the code I used above to list all Worksheet Code names and (names) ). You can see these mysterious Modules in the VBAProject Window:
MysteriousModules.JPG

Note also in the above screenshot there seems to be an extra “DieseArbeitsmappe1” Module , ( DieseArbeitsmappe1 is German for “ThisWorkbook1” ). As far as I knew, you typically have just one This Workbook Code Module )
_...
_2b) The codes that were in my original Worksheet Code Modules were transferred into these new Modules. ( Not by me !! – I have no idea how that happened! ). My original Worksheet Code modules were then left empty. ??

_2c) The code will not run from these new mysterious Modules. I get “Run-time error 429; ActiveX component can't create object.”

_2d) If I copy the codes back to where they were “stolen” from ( or to any of the original Worksheet Code modules or Normal Code Modules for that matter ) , then the codes work as before.

_2e) So I guess my way forward is simply to transfer all codes back to where they were “stolen” from.
Then I should maybe delete these weird new Modules. – The problem is I do not seem to be able to delete them ?? – They have the same symbol as a “This Workbook” Module. I do not know if that is significant

_.........................

Basically my main concern , and what I am asking in this Thread is
What the hell is going on??. Or what happened. Can anyone shed any light on what may have happened. Any advice on the best way forward? Are these new Modules / Worksheets harmless? Anyone ever occurred anything similar?. I am mostly concerned that some Bug is doing weird things. So far I have only seen this on a couple of files.

Alan

Hi AB33
The stuff you goggled sounds familiar with what I googled. But i only goggled a bit on that error_.....
_.....I did not think too much in the direction of getting the codes working again in the weird new modules that Excel or God had suddenly decided to put then in.. Lol.. ( That in my case would be a bit like trying to figure out why my car does not start when an Alien in another planet ( which God just made and put my car in ) phones me up to tell me that my car will not start... lol .... – I am more concerned with why my car is in another planet, and why I have another planet.... lol.... )

But thanks for letting me know you tried something like that, so I can probably spare myself such an attempt. ( As I mentioned, The codes work anyway when I put them back where they were before Excel or God decided to steal them from )
Nice to know a Profi also gets caught out by some weird or complicated inner workings of Excel

It is not a big problem for me just now. Just a bit of a curiosity. Hope it is not the start of my Excel cracking up. I only noticed it in two Files to date.

Often Excel crashes when I work on codes ( usually if I am inpatient and continue running straight after a I do a DeBug. It is better i find to stop the code after debugging something, saving the file then strating again. My Excel punishes me if i use too much haste, Lol.. )
My Excel usually repairs itself after it crashes. .... Perhaps on a repair it got its wiring a bit mixed up once or twice.. lol.. )


Thanks for the reply.
Alan


P.s.

Actually it is quite interesting.
In one file I have a Worksheet I can use and see as a Worksheet in a .xlsm File. It is invisible in the VBA Project window and does not seem to have a Worksheet Code module.

And in the other File I have multiple Modules looking like a ThisWorksheet Code Module, that I can store my codes, or text in

Quite unique really. Maybe i should be happy and consider myself privileged, and thankful for my God given extra Worksheet and code Modules, Lol... !

Hi AB33
maybe someone in the know will pop by

Hi Jindon
Thanks for the reply.
If I right click on this mysterious Tab, it highlights “spreadsheet”, just as I get highlighted if I do the same on my of the other “normal” Tabs. It would still appear to me to be a Normal Worksheet. Just that it strangely has no code module and does not appear in the VBAProject Window.
_...

But I do see what you are saying. And I expect you are correct. Here your code again, slightly different
It shows that I have 7 Worksheets and 8 Sheets. And you are probably correct that the 8th Sheet is something else, such as possibly an Excel 4.0 Macro Sheet, or some similar „throw back” to Days gone by.

Please Login or Register  to view this content.

_..................................

What surprises me is that I did something similar here:
http://www.mrexcel.com/forum/lounge-...ml#post4387604
I thought with this code I was doing something similar, that is to say adding a Macro Code “sheet”.
The difference to the current situation is that the following code does not appear to give me something which “looks” like an extra Worksheet, that is to say it does not add a new Tab.
The following code Adds 2 “sheets”. The “sheet” count goes up by two. I see no new tabs. The worksheet count is not affected. But two extra Modules do appear in the VBAProject Window
The code then deletes these two modules and “sheets” with one command. The sheets count goes back down to its original. The worksheets count is not affected.

Applied to my File, I start with 7 Worksheets, and 8 Sheets
The Sheets count goes up to 10, then back to 8
The Worksheet count is unaffected

So clearly what
_ this code below adds
and
_ mysterious thing named “Makro1” I have in my uploaded file, “InsaneDikMembranes1Makro1Sh_t.xlsm” , are not the same.

Please Login or Register  to view this content.

_....................

Just an interesting curiosity. I expect what you suggest is the answer – some old no longer used thing. Strange how it suddenly “appeared by me”

Strange how it suddenly “appeared by me”

Hi Doc, as nilem states it is a Macro sheet, do you ever create worksheets by pressing Shift + F11 by any chance? if yes then you possibly pressed Ctrl + F11 by mistake at some stage.

Hi Was Wodge

Originally Posted by WasWodge

Hi Doc, as nilem states it is a Macro sheet, do you ever create worksheets by pressing Shift + F11 by any chance? if yes then you possibly pressed Ctrl + F11 by mistake at some stage.

No.. But I do Alt + F11 a lot.......

I just tried Ctrl + F11, .. Bingo !

Thanks


Alan

P.s. any idea what it has do with a macro ?
_ At first glance it just seems to work like a normal spreadsheet, just with no code module..

P.s. any idea what it has do with a macro ?
_ At first glance it just seems to work like a normal spreadsheet, just with no code module..

It isn't like a normal worksheet as for one formulas you put on one have a habit of not calculating.

As far as I know they are only there for backwards compatibility (and way, way back, talking early 1990's) but if you want to try it then have a look at the link below to see how they should work as it has instructions (I say should because I can't be bothered to test if they still work ).

https://support.microsoft.com/en-us/kb/149998

Hi WasWodge,
Thanks for that info. Good to know about the formula Problem as
_a) I was thinking of trying to use the ***“mystery” Sheet so that was worth noting ( ***It is not quite such a mystery now , - thanks WasWodge and Jindon )
_b) At about the time I got this weirdo 1) and weirdo 2), ...I had a nother strange problem: all my ( fairly simple ) formulas in one sheet broke and I lost a lot of info ( Luckily had a back up ). So maybe my weirdo 2) problem is also down to somewhere along the line either Excel of me did something that caused a strange conversion to Sheets from time gone by...
_.................

Originally Posted by WasWodge

As far as I know they are only there for backwards compatibility.... but if you want to try it then have a look at the link below to see how they should work as it has instructions (I say should because I can't be bothered .....

I am not too bothered myself, as you and jindon have cleared up this part for me.
Having said that, I slept through computers totally in the last 25-30 years, and only started a couple of years back to help with a personal project. So I may have a quick look just now, to help fill in the gap a bit. So thanks for that link

Alan

Oops.... I see my wife was on my computer late last night and did not log out... So I am in her account!!..
I wish she would stop doing that.

Thanks again WasWodge
Alan

Macro 4 sheets. Running Excel 4 macros. Excel 4.0 Macro Sheet.

I just had a quick play for fun, and I changed the Thread title to include “macro 4”, so here a last concluding part for Problem “Weirdo 1)”

_ Following the instructions in the Link given by WasWodge......
_..... So it tells you to add a Module and a 4.0 Macro sheet in a Microsoft Excel version 5.0 or 7.0 workbook... ******
Hmm.. Well I think I know now how to add a Macro sheet ( Ctrl+F11 ) ... and my code in post #7 shows one way to add a Code Module ( which may be important rather than using a Normal Code Module – my guess is that my code Adds the “old” type of Module... ##EDIT: I tried later using a Normal Code Module instead and it did not seem to make any difference ) )

_ At about instruction 5. In the Link things do not make any sense to me. I cannot follow or find what they are talking about.. (****** This is probably as they are referring to pre XL 2007 Workbooks )
_ But I played around and got it working:
_ So just as a bit of idle Fun. This is a way to get it to “work”..

_ In any File.....
_ Hit Ctrl+F11 and you should get a “Macro” Sheet. I just did and I got ( In my German Excel ) a new tab with the name "Makro3" ( you should get the first time probably "macro1" )
The sheet was Empty.
_ I put this in it
Using Excel 2007 32 bit

Row\Col	C
2
3	Alan3
4	=RUN(VBA_XLM.xls!Alanstests3())
5	=RETURN()

Makro3

_ Then either Add a new Normal Code Module ( or a “Macro” / "Code" Module – Step through my code in Post # 7 to get the idea about that. )

_ Put this code in that Module:

Please Login or Register  to view this content.

_ From the Formula Ribbon ( XL 2007+) select the Names Manager.
_ From the Names Manager dialogue box, that should come up, select New

_ The next bit is difficult to explain as I have German Excel. So I give a Picture. Briefly in words: ( I think ) I am:
(i) assigning a name “Alan3”. ( This does two things: Making a Name Object, Alan3 with that Name Property “Alan3” ) to Cell C3 in the Sheet “Makro3”
(ii) in the macro section bottom left I am defining a “Command” ( Befehl ).....
( I suppose that makes some vague sort of sense )
(iii) the Category list I chose ( or is default ) User Defined ( Kategorie: Benutzerdefiniert ) !!! Maybe this is significant later

4 Macro sheet.JPG




_ Save the File as VBA_XLM.xls ( Select “Xl 97 to 2003 Workbook” or similar in the File type dropdown options)

_ from the Worksheet evoke the Macro Dialogue box ( Alt+F8 )

_ you should see you have both
the usual Alanstests3 to chose, ( which if you do should run normally and gives the message "Alans Tests 3"
and
_ you can chose Alan3. If I do this, I get the message and then it errors. But never mind, at least that indicates that it is doing something a bit different.... “running a macro 4 thing”

And note also:
_ you can also chose to run .. C3 ( Select the ( reserved by Bill Gates ) Cell name )! - which is another interesting alternative ! – make sense as that C3 is sernominous ( same as ) Alan3 . It is just the name Property of the Name Object. This Name Object is assigned to the Range Object Range("C3") in the macro 4 sheet with Sheet name of "Makro3". Somehow Excel distiguishes this Range Object to somehow know it is the start of a mascro 4 thing !!!

Alantests3Alan3C3.JPG





_....
Some last notes:
A) Do not be confused with “Alan3” written in Cell C3. That is just the Cell value. If you change that to_......_....

Row\Col	C
3	CellValue
4	=RUN(VBA_XLM.xls!Alanstests3())
5	=RETURN()

Makro3

_..._.... then everything works exactly as before . – You will not find any “CellValue” macro to choose from. Cellvalue is not a name or Name Object. It is just text in a Cell that has a Name Object with the Name properties of both Alan3 and C3 ( Alan3 I think has Workbook Scope, and C3 probably Worksheet Scope )
_.........................

B) ## So it appears that you can use in this case a Normal Code Module or a Macro (4 ) Code Module for the main code that is Called by this sort of thing:
=RUN(VBA_XLM.xls!Alanstests())

But you will find if you put this sort of thing
=RUN(VBA_XLM.xls!Alanstests())
In a cell in a normal Worksheet then that errors. So clearly there is a bit of a difference with this macro (4) sheet
_.....................................

The File I was playing around with is uploaded here:. ( Note that Modul1 and Modul2 are Code Modules ( probably macro 4 code Modules ) and not Normal Code Modules. – Play around with my code from Post #7 to get the idea about that. )
https://app.box.com/s/fothjv55q6n70juq6ij6sl3i7lptqgam

_..


Wierdo 1 SOLVED



Wierdo 2 Not jet
Alan