• Status: Solved
  • Priority: Low
  • Security: Public
  • Views: 47
  • Last Modified:

Run-time error 1004

Experts:

I came across a **working** dashboard example which was authored by "veekay".   Please see attached XLS ("Dashboard").   The demo works great using the following process:
1. Click on command button "Update Drop Downs"
2. Select values from the combo boxes (Products, Region, Customer Type)
3. Click on command button "Show Data"
... given the selected cmd button values, data is shown in row 13++

For testing purposes, I mimiced the dashboard example (i.e., ensuring I copy all require objects and VBA code) before attempting to develop my own process (with different data).
Unfortunaly, when executing the "Show Data" command button (after "Updating Drop Down" and selecting values from "Products", "Region", and "Customer Type", I am getting run-time error '1004' (Methods 'Range' of object' _ Worksheet failed).

What causes this problem in my replicate XLS?    Did I forget to update some VBA?

Thank you in advance,
EEH
Dashboard.xlsm
Excel-as-a-Database.xlsm
0
ExpExchHelp
Asked:
ExpExchHelp
  • 3
  • 3
1 Solution
 
Fabrice LambertFabrice LambertCommented:
You forgot to replicate named ranges (see the names manager in the formulas tab).

Plus your workbook suffer from various syndroms:
- Global variables.
- Explicit use of "active" objects (ActiveWorkbook, Selection ect ....)
- Implicit use of "active" objects.
- Lack of error handlers.
- Explicit references (AKA Early binding).
0
 
ExpExchHelpAuthor Commented:
Fabrice:

Thank you for the prompt response... great -- I added the missing named range and the replica XLS works now.

Wrt the other syndroms, did I forget to include them somewhere else (or did the original version also "suffer" from the same symptons)?
If I forgot them, where do I make those changes?   If not, how can the VBA code be improved to address your recommendations?

Thanks,
EEH
0
 
Fabrice LambertFabrice LambertCommented:
Same symptoms in the original workbook.

Not much to say beside Learning good practices with VBA.
Give meaningfull names to variables.

Avoid "active object" because they are subject to change upon any user interraction, thus are unreliable and chaotic, anthe fact that they exist doesn't mean you should use them.

Avoid also the generic "sheets", "Range", "Cells" object as they implicitly refer to ActiveWorkbook.Sheets, ActiveWorkbook.ActiveSheet.Range, ActiveWorkbook.ActiveSheet.cells (yuck ! Too many active objects in those statements).

Avoid global variables unless you have no other choices (remember that variables can always be given as parameters to functions).

Apply the Single Responsibility Principle (SRP):
- A function or class should do one thing, and do it right (instead of doing many things badly), it is far easyer to maintain.

Personnal notes:
You'll probably find articles about declaring all variables at top of functions and hungarian notation, I don't consider those good practices.
In my eyes, pre-declaring variables is a bad practices because:
- The variable declaration can be far away from its initialisation.
- It unnecessary extend the variable's lifetime.

Hungarian notation is bad because:
- Prefixing the variable name with its type reduce lisibility.
- It give no information about the variable's role.
- If a variable type change (int becomes double, or long or whataver), your whole code becomes a lie.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
ExpExchHelpAuthor Commented:
Fabrice - as indicated, I did not develop the VBA, so I can't take credit for it.

Thank you for providing the resolution to the run-time error... I appreciate it.

EEH
0
 
ExpExchHelpAuthor Commented:
Thank you... appreciate the feedback.
0
 
Fabrice LambertFabrice LambertCommented:
You're welcome.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now