?
Solved

Compile Error, in hidden module1-Excel-2003

Posted on 2009-12-28
20
Medium Priority
?
821 Views
Last Modified: 2012-05-08
Hi,

I cant open the Excel workbook and it shows Compile Error, in hidden module1. Ive attached the script that was used in module1 and I hope the Expert could debug this

Private Sub UserForm_Activate()
Sheet2.Activate
End Sub

Private Sub UserForm1_Activate()
UserForm1.SetFocus
End Sub


Function SetTime(Info As Range) As Double
    Dim This As String, Ans As Double
    This = LCase(Info.Value)
    Select Case This
    Case "haq"
        Ans = 3
    Case "hpg"
        Ans = 3
    Case "hin"
        Ans = 3
    Case "hmd"
        Ans = 3
    Case "hcm"
        Ans = 8
    Case "hmf"
        Ans = 2
    Case "hav"
        Ans = 2
    Case "wan"
        Ans = 2
    Case "wrt"
        Ans = 5
    Case "wqa"
        Ans = 3
    Case "wlf"
        Ans = 3
    Case "wcp"
        Ans = 3
    Case "spr"
        Ans = 0.58
    Case "spl"
        Ans = 1
    Case "sep"
        Ans = 2
    Case "sml"
        Ans = 60
    Case "sck"
        Ans = 30
    Case "sel"
        Ans = 45
    Case "sat"
        Ans = 20
    Case "std"
        Ans = 45
    Case "stw"
        Ans = 90
    Case "llr"
        Ans = 1
    Case "lst"
        Ans = 15
    Case "lid"
        Ans = 30
    Case "lcs"
        Ans = 30
    Case "cec"
        Ans = 45
    Case "ccc"
        Ans = 15
    Case "cmr"
        Ans = 60
    Case "cct"
        Ans = 30
    Case "clh"
        Ans = 60
    Case "ctb"
        Ans = 30
    Case "cir"
        Ans = 60
    Case "cdr"
        Ans = 30
    Case "cwr"
        Ans = 135
    Case "crl"
        Ans = 45
    Case "cds"
        Ans = 45
    Case "xlh"
        Ans = 60
    Case "xtb"
        Ans = 30
    Case Else
        Ans = 0
    End Select
    SetTime = Ans
End Function

Open in new window

0
Comment
Question by:Theva
  • 9
  • 6
  • 4
  • +1
20 Comments
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 26135566
This may not have anything to do with your code. Here are some possible causes.

To fix the error "Compile error in hidden module: DistMon" download and install the latest Adobe Acrobat PDFMaker at http://www.adobe.com/products/acrobat/update.html. This is a known problem with Adobe Acrobat version 6. Switch to version 5 or 7, or remove the Excel add-in from the Excel startup folder while Excel is not running.

The error can also appear as "Compile error in hidden module: AutoExec" or "Compile error in hidden module: AutoExecNew".

The problem also occurs when the Norton AntiVirus Office plug-in attempts to scan the objects in the Startup folders when launching Word. To work around the problem, disable the Norton AntiVirus Office plug-in before launching Word.

For more information see http://support.microsoft.com/kb/307410

Kevin
0
 
LVL 8

Expert Comment

by:VipulKadia
ID: 26135808
There is no any method like 'SetFocus' for userform.

So remove "UserForm1.SetFocus" line from "Private Sub UserForm1_Activate()"


0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 26135840
VipulKadia,

That's true, but that will not generate a "Compile Error, in hidden module" error.

Kevin
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 85

Expert Comment

by:Rory Archibald
ID: 26136234
Kevin,
Why wouldn't it?
Rory
0
 

Author Comment

by:Theva
ID: 26136304
HI VipulKadia,

Please advice what code I need to replace it, I'm not to sure with your suggestion.
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 26136310
Excel doesn't compile code when the workbook opens unless it is executed, i.e. called by an event handler.

If that code was called by an event handler the error would manifest as a VBA debug dialog stating that the variable UserForm1 was not defined or, if it was defined, that SetFocus was not an event defined by the UserForm class.

The code is defined within an event handler which is defined in a general code module which means it will never run on its own.

I can reproduce the exact error by calling that code from the Workbook_Open event handler after password protecting the VBA project. But that's a lot of ifs.

I've been wrong before. Could be wrong here too.

I suppose the correct questions/statements back to the Asker would be:

Have you defined a workbook open event handler? If so, does that code or any subordinate code call "UserForm1_Activate"? If so then unlock the VBA project and open the workbook to see the real error.
0
 
LVL 8

Expert Comment

by:VipulKadia
ID: 26136313
Hi Theva,
Delete all these lines of code :

Private Sub UserForm1_Activate()
UserForm1.SetFocus
End Sub
 
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 26136314
Oh hell, I'm going to give it a shot :-)

Theva: Permanently unlock the workbook's VBA project, close, and open the workbook to see the real error.

Kevin
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 26136316
No! Don't delete the code! The only way that error will occur is if the code is actually executed. If it IS executed all you are doing is shifting the error around.

Kevin
0
 
LVL 8

Expert Comment

by:VipulKadia
ID: 26136323
Hi Theva,
It will be helpful if you provide sample file.
0
 

Author Comment

by:Theva
ID: 26136333
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 26136358
The VBA project is protected...sort of. But there is no event handling code that will run the bad code. I cannot reproduce the error.

So deleting that code will probably not affect the problem.

Kevin
0
 
LVL 8

Expert Comment

by:VipulKadia
ID: 26136359
Hi Theva,
I checked your file. I don't get any error message when opened your file.
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 26136373
I fully protected the VBA project and still could not reproduce the error. I suspect a mild form of corruption.

Attached is a fully rebuilt version of your workbook. I removed the VBA password. Re-lock the VBA project and see if the error persists.

Kevin
RBS-Productivity-measurement--Ve.xls
0
 

Author Comment

by:Theva
ID: 26136483
Hi Kevin,

Hi Kevin,

Ive checked the list and I noticed the matrix column with #Name? instead of time value. How to rectify this error? The value for tasks are predefined in module 1.

Here's the image of the error message
Matrix--time-error-.png
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 26136536
The program I used to rebuild the workbook created an extra copy of one of the code modules...curious.

See the attached workbook.

Kevin
RBS-Productivity-measurement--Ve.xls
0
 

Author Comment

by:Theva
ID: 26136603
Now I have 3 types of problems,

1.      When I select the task code, the time value in Matrix column doesnt change
2.      The Calendar not visible (please refer the attached image)
3.      Object not loaded  Could not load the object  (please refer the attached image)

Need your advice how to overcome this.

error-1.png
Error-2.png
0
 
LVL 81

Accepted Solution

by:
zorvek (Kevin Jones) earned 2000 total points
ID: 26136661
I ran a series of tests and could not reproduce any of the problems. I re-saved the workbook (attached).

Kevin
RBS-Productivity-measurement--Ve.xls
0
 

Author Comment

by:Theva
ID: 26136813
Hi

Now it works for me, thanks. However, few of my working colleagues also facing the same problem Object not found. I think I need to close this question and open a new question with simplified version. Hope Experts will help me.
0
 

Author Closing Comment

by:Theva
ID: 31670643
Thanks a lot
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question