Hello guys ginawa ko lang tong thread para sa mga kasymb natin na need ng tulong about MS excel. mga reporting staff, encoders, data analyst, secrectary, or Ms excel systems. pasok po kayo dito. share tayo ng knowledge. i will provide tutorials too. believe me after learning this things after a month i got my promotion to go up another level from our company. so try it now. made your bosses like their jaws drop.
on my first example. you will see chart with controls like the picture below.
here you will see conditional formatting with toggle.like the picture below.
now you will see a picture album using excel. but in this tutorial i'll give you an example of animal album.
in this picture you will see a chart that looks like a temperature. it would be helpful to a presentations with a sales and target sales.
now in this picture you will see a speedo meter chart with control. the tutorial was provided from the attachment.
digital clock by dhok si...
Originally Posted by
dhok_si 
hi mga tol share ko lng nala2man ko sa excel 2010..ngaun araw na to tu2ro ko sa inyo kung paano gumawa ng digital clock sa excel 2010
e2 un code sa vba
Dim running As Boolean
Sub test()
Const QRT As Integer = 4, Year As Integer = 12
Cells(1, 1) = 12 / QRT
Cells(1, 2) = 12 / Year
End Sub
Sub test2()
Dim rng As Range
Dim lr As Long
lr = Cells(Rows.Count, 1).End(xlUp).Row
Set rng = Range(Cells(1, 1), Cells(lr, 1))
rng.Font.ColorIndex = 4
End Sub
Sub Clock()
running = Not (running)
Do While running = True
DoEvents
Range("A1") = Now
Loop
End Sub
- - - Updated - - -kopyahin nyo lng yan..tapos sundin nyo un video na ginawa ko
e2 un link
http://www.youtube.com/watch?v=csXhTkHVp08- - - Updated - - -e2 pa isang ginawa ko hijri to gregorian calendar
http://www.youtube.com/watch?v=8uUrpmDtLow
sana po magshare din ang iba,
more excel tutorials coming soon.
Guide when creating application developments.
Steps for Application Development
There is no simple, surefire recipe for developing an effective spreadsheet application. Everyone has
his or her own style for creating such applications, and I haven�t discovered one best way that works
for everyone. In addition, every project is different and, therefore, requires its own approach. Finally,
the demands and technical expertise of the people you work with (or for) also play a role in how the
development process proceeds.
Spreadsheet developers typically perform the following activities:
? Determine the needs of the user(s)
? Plan an application that meets these needs
? Determine the most appropriate user interface
? Create the spreadsheet, formulas, macros, and user interface
? Test and debug the application
? Attempt to make the application bulletproof
? Make the application aesthetically appealing and intuitive
? Document the development effort
? Develop user documentation and Help systems
? Distribute the application to the user
? Update the application when necessary
Not all these steps are required for each application, and the order in which these activities are performed
varies from project to project.
Determining User Needs
When you undertake a new Excel project, one of your first steps is to identify exactly what the end
users require. Failure to thoroughly assess the end users� needs early on often results in additional
work later when you have to adjust the application so that it does what it was supposed to do in the
first place.
In some cases, you�ll be intimately familiar with the end users � you may even be an end user yourself.
In other cases (for example, if you�re a consultant developing a project for a new client), you may
know little or nothing about the users or their situations.
How do you determine the needs of the user? If you�ve been asked to develop a spreadsheet application,
it�s a good idea to meet with the end users and ask specific questions. Better yet, get everything
in writing, create flow diagrams, pay attention to minor details, and do anything else to ensure that
the product you deliver is the product that is needed.
Here are some guidelines that may help make this phase easier:
? Don�t assume that you know what the user needs. Second-guessing at this stage almost
always causes problems later.
? If possible, talk directly to the end users of the application, not just their supervisor or
manager.
? Learn what, if anything, is currently being done to meet the users� needs. You might be able
to save some work by simply adapting an existing application. At the very least, looking at
current solutions will familiarize you with the operation.
? Identify the resources available at the users� site. For example, try to determine whether you
must work around any hardware or software limitations.
? If possible, determine the specific hardware systems that will be used. If your application
will be used on slow systems, you need to take that into account.
? Identify which versions of Excel are in use. Although Microsoft does everything in its
power to urge users to upgrade to the latest version of the software, the majority of Excel
users don�t.
? Understand the skill levels of the end users. This information will help you design the application
appropriately.
? Determine how long the application will be used and whether any changes are anticipated
during the lifetime of the project. Knowing this information may influence the amount of
effort that you put into the project and help you plan for changes.
And finally, don�t be surprised if the project specifications change before you complete the application.
This occurrence is common, and you�re in a better position if you expect changes rather than
being surprised by them. Just make sure that your contract (if you have one) addresses the issue of
changing specifications.
Planning an Application
That Meets User Needs
After you determine the end users� needs, it�s tempting to jump right in and start fiddling around in
Excel. Take it from someone who suffers from this problem: Try to restrain yourself. Builders don�t
construct a house without a set of blueprints, and you shouldn�t build a spreadsheet application
without some type of plan. The formality of your plan depends on the scope of the project and your
general style of working, but you should spend at least some time thinking about what you�re going
to do and coming up with a plan of action.
Before rolling up your sleeves and settling down at your keyboard, you�ll benefit by taking some time
to consider the various ways you can approach the problem. This planning period is where a thorough
knowledge of Excel pays off. Avoiding blind alleys rather than stumbling into them is always a
good idea.
If you ask a dozen Excel experts to design an application based on precise specifications, chances are
you�ll get a dozen different implementations of the project that meet those specifications. Of those
solutions, some will be better than the others because Excel often provides several options to accomplish
a task. If you know Excel inside and out, you�ll have a good idea of the potential methods at
your disposal, and you can choose the one most appropriate for the project at hand. Often, a bit of
creative thinking yields an unusual approach that�s vastly superior to other methods.
So at the beginning stage of this planning period, consider some general options, such as these:
? File structure: Think about whether you want to use one workbook with multiple sheets,
several single-sheet workbooks, or a template file.
? Data structure: You should always consider how your data will be structured and also determine
whether you will be using external database files or storing everything in worksheets.
? Add-in or workbook file: In some cases, an add-in may be the best choice for your final
product. Or perhaps you might use an add-in with a standard workbook.
? Version of Excel: Will your Excel application be used with Excel 2013 only? With Excel 2007
and later? What about Excel 2003 and earlier versions? Will your application also be run on a
Macintosh? These considerations are important because each new version of Excel adds features
that aren�t available in previous versions. The Ribbon interface introduced in Excel 2007
makes it more challenging than ever to create an application that works with older versions.
? Error handling: Error handling is a major issue with applications. You need to determine
how your application will detect and deal with errors. For example, if your application
applies formatting to the active worksheet, you need to be able to handle a case in which a
chart sheet is active.
? Use of special features: If your application needs to summarize a lot of data, you may want
to consider using Excel�s pivot table feature. Or you may want to use Excel�s data validation
feature as a check for valid data entry.
? Performance issues: The time to start thinking about increasing the speed and efficiency of
your application is at the development stage, not when the application is completed and
users are complaining.
? Level of security: As you may know, Excel provides several protection options to restrict
access to particular elements of a workbook. For example, you can lock cells so that formulas
cannot be changed, and you can assign a password to prevent unauthorized users from
viewing or accessing specific files. Determining up front exactly what you need to protect �
and what level of protection is necessary � will make your job easier.
Note: Be aware that Excel�s protection features aren�t 100 percent effective � far from it. If
you desire complete and absolute security for your application, Excel probably isn�t the
best platform.
You�ll probably have to deal with many other project-specific considerations in this phase. Consider
all options and don�t settle on the first solution that comes to mind.
Another design consideration is remembering to plan for change. You�ll do yourself a favor if you
make your application as generic as possible. For example, don�t write a procedure that works with
only a specific range of cells. Rather, write a procedure that accepts any range as an argument. When
the inevitable changes are requested, such a design makes it easier for you to carry out the revisions.
Also, you may find that the work that you do for one project is similar to the work that you do for
another. Keep reusability in mind when you are planning a project.
One thing that I�ve learned from experience is to avoid letting the end user completely guide your
approach to a problem. For example, suppose that you meet with a manager who tells you that the
department needs an application to write text files that will be imported into another application.
Don�t confuse the user�s need with the solution. The user�s real need is to share data. Using an intermediate
text file to do it is just one possible solution; better ways to approach the problem may exist.
In other words, don�t let the users define their problem by stating it in terms of a solution approach.
Determining the best approach is your job.
Testing the application
How many times have you used a commercial software application, only to have it bomb out on you
at a crucial moment? Most likely, the problem was caused by insufficient testing that didn�t catch all
the bugs. All nontrivial software has bugs, but in the best software, the bugs are simply more
obscure. As you�ll see, you sometimes must work around the bugs in Excel to get your application to
perform properly.
After you create your application, you need to test it. Testing is one of the most crucial steps; it�s not
uncommon to spend as much time testing and debugging an application as you did creating the
application. Actually, you should be doing a great deal of testing during the development phase.
After all, whether you�re writing a VBA routine or creating formulas in a worksheet, you want to make
sure that the application is working the way it�s supposed to work.
Like standard compiled applications, spreadsheet applications that you develop are prone to bugs. A
bug can be defined as (1) something that does happen but shouldn�t happen while a program (or
application) is running, or (2) something that doesn�t happen when it should happen. Both species of
bugs are equally nasty, and you should plan on devoting a good portion of your development time
to testing the application under all reasonable conditions and fixing any problems that you find. In
some cases, unfortunately, the problems aren�t entirely your fault. Excel, too, has its problems (see
the �Bugs? In Excel?� sidebar).
Bugs? In Excel?
You may think that a product such as Excel, which is used by millions of people throughout the
world, would be relatively free of bugs. Think again. Excel is such a complex piece of software that it
is only natural to expect some problems with it. And Excel does have some problems.
Getting a product such as Excel out the door isn�t easy, even for Microsoft and its seemingly unlimited
resources. Releasing a software product involves compromises and trade-offs. It�s commonly
known that most major software vendors release their products with full knowledge that they contain
bugs. Most bugs are considered insignificant enough to ignore. Software companies could
postpone their releases by a few months and fix many of them, but software, like everything else, is
ruled by economics. The benefits of delaying a product�s release often don�t exceed the costs
involved. Although Excel definitely has its share of bugs, my guess is that the majority of Excel users
never encounter one.
In this book, I point out the problems with Excel that I know about. You�ll surely discover some more
on your own. Some problems occur only with a particular version of Excel � and under a specific
configuration involving hardware or software or both. These bugs are the worst ones because they
aren�t easily reproducible.
So what�s a developer to do? The solution is called a workaround. If something that you try to do
doesn�t work � and all indications say that it should work � it�s time to move on to Plan B.
Frustrating? Sure. A waste of your time? Absolutely. It�s all part of being a developer.
What about beta testing?
Software manufacturers typically have a rigorous testing cycle for new products. After extensive
internal testing, the pre-release product is usually sent to a group of interested users for beta testing.
This phase often uncovers additional problems that are usually corrected before the product�s final
release.
If you�re developing an Excel application that more than a few people will use, you may want to consider
a beta test. This test enables your intended users to use your application in its intended setting
on different hardware (usually).
The beta period should begin after you�ve completed all your own testing and you feel that the
application is ready to distribute. You�ll need to identify a group of users to help you. The process
works best if you distribute everything that will ultimately be included in your application: user documentation,
the installation program, help, and so on. You can evaluate the beta test in a number of
ways, including face-to-face discussions, e-mail, questionnaires, and phone calls.
You almost always become aware of problems that you need to correct or improvements that you
need to make before you undertake a widespread distribution of the application. Of course, a betatesting
phase takes additional time, and not all projects can afford that luxury.
Documenting the development effort
Putting a spreadsheet application together is one thing. Making it understandable for other people is
another. As with traditional programming, it�s important that you thoroughly document your work.
Such documentation helps you if you need to go back to it (and you will), and it helps anyone else
whom you might pass it on to.
How do you document a workbook application? You can either store the information in a worksheet
or use another file. You can even use a paper document, if you prefer. Perhaps the easiest way is to
use a separate worksheet to store your comments and key information for the project. For VBA code,
use comments liberally. (VBA text preceded with an apostrophe is ignored because that text is designated
as a comment.) Although an elegant piece of VBA code can seem perfectly obvious to you
today, when you come back to it in a few months, your reasoning may be completely obscured
unless you use the VBA comment feature.
credits to Mr John walkenbach for these guides. and he's books.
Post a Comment