How to make Excel calculators

Posted on viernes, mayo 06, 2011 by Pedro Wave

Traducción al español aquí

In the previous article on Calculating minds I was showing a calculator written in Excel and now I shall explain how to make calculators in Excel.

New enhanced calculator

If you like the calculations as well as I sure that you will like the new calculator with these improvements (Version 4):
  1. Calculator mode without floating keys.
  2. New calculator beta 5, with all trigonometric functions.
  3. New full calculator 6, with all 67 function keys.
  4. Softkeys or keyboard equivalents for all keys.
  5. Improved tooltips with less flicker or blink.
  6. International geolocalized calculator.
  7. Information and help translated into 6 languages​​: English, Spanish, French, Italian, German and Portuguese.
  8. Display the calculator in 56 colors.

ATTENTION: To change the display color, choose a color in row 1.


Be sure to try Calculator without keys mode, selecting a keypad number 7 and up. If you know or you learn all the tooltips or the physical keyboard equivalents you will be able to calculate pressing physical keys such as the blind method of typing. For example, the numbers e and pi are displayed by pressing E and P keys, respectively.

How it was done the floating keys calculator

This calculator is initially opened without a floating key, ie without any shape drawn initially by default, each key forms is generated dynamically when you select a calculator number.

If you test it, you will find it much easier calculating with it than with the first general purpose electronic calculator in the world, the ENIAC, which lasted about 12 years since the contract sign until its final shutdown, but I hope you do not use it to calculate the trajectories of projectiles and hydrogen bombs.

This workbook

When it is opened run the macro Workbook_Open that sets the range codPais of the "Cod" sheet in cells B2:C220 linking the country dialing code with a value for your language: 1-English 2-Spanish; 3-French 4-Italian 5-German 6-Portuguese, etc. This value is stored in the BM48 cell of the "Calc" sheet.

In order for the function key supports OnKey "/" such as operation of dividing and not its effect by default to open menus, this property must being changed:

If AO48 = 1 then calls macro ActivaTeclado and dibujaTeclas with the keyboard number in cell BN13.

Workbook_BeforeClose calls DesactivaTeclado and re-modify the default value:

Calc sheet

Worksheet_Change event launches by changing the value of these cells:
  • AO48 = 1: ActivaTeclado; = 0: DesactivaTeclado
  • AC48 to activate o deactivate Tooltips, calls to dibujaTeclas
  • BM48 change language and if AC48 = 1: dibujaTeclas
  • BN13 calls to dibujaTeclas with another keyboard number

Worksheet_SelectionChange runs in two cases:
  • When Tooltips are enabled and active cell is in column 101, by reading the value of the active cell that contains the equivalent key down and calling macro tecla
  • If the row is the one and the columns between 21 and 76, change the display color

Module modAyuda

AyudaCalc macro is changing the text and the position of the giant key help in 9 steps, calling textoAyuda looking translation in the "Languages​​" sheet with the VLOOKUP function.

Module modCalc

This module contains the macro that does the calculations themselves: tecla passing the key value of the pressed key buttons virtually on the spreadsheet or really in the physical keyboard.

Each digit of a number is forming the number visualized on the display.

Changes to a darker color the last key pressed, and if it is an operation with two operands, the operator is diffuses in yellow to remember what was the last operator pressed.

Opera function performs operations for operators with two operands, such are: add, subtract, multiply, divide, percentage, module, logarithms and powers in y base.

Additional functions of the calculator are:
  • valTrigo performs trigonometric functions with degrees and radians.
  • u_fact calculates the factorial of an integer.
  • swapTeclas swaps the two operands.
  • visTecla hide or show a key.
  • grabaTeclado to create another sheet with a new key number, calling posTeclas
  • Traduce passing a code gets a translation into one of 6 languages.

Module modTeclado

The routine ActivaTeclado runs Application.OnKey function once for each key of the "Teclado" sheet by assigning a code in column D to a procedure in column E, getting physical keys assigned to the virtual keys on the calculator.

DesactivaTeclado makes the opposite assigning default code to each physical key.

With extensibility may include programmatically in VBE, Tools, References, the reference to Microsoft Visual Basic for Applications Extensibility 5.3 needed to dynamically create procedures calling IncluyeSubModulo to be executed only once, and is therefore commented in the macro ActivaTeclado. Each of these nearly 100 procedures automatically generated then calls macro tecla  with its key type argument, such as in this example:

Module modTeclas

Routine dibujaTeclas is reading from a "Teclasn" sheet, where n is a keyboard number from 0 to 100 (0 to 6 per default), each of the keys that make up the keyboard and draw them according to their properties: name, text, font, color, size, style, top and left position, width, height and visible.

posTeclas routine creates a new "Teclasn" sheet when you want to record a new keyboard.

creaTecla add a shape calling AddShape with msoShapeBevel type with the appearance of a button, similar to a key.

tipTecla function gets the text from the Tooltip of a key from the column F of the "Teclado" sheet with its operation and, in parentheses, their corresponding equivalent physical keys, eg: Random number (A) so that pressing A key calculates a random number.

How to make calculators

This video explains how to use and create Excel calculators.

The best is the ability to create new calculators, customizing the shape of keys and expanding and improving the graphical user interface to the taste of each, without knowing programming, just to know how to change text and size of keys shapes and knowing to drag and drop buttons, which is learned in two minutes watching the above video.

Comparison with the Windows Calculator

As that seems to Micro$oft Calculator, nothing is further from my intentions. Of the 8 calculators that I supply, the only one I wanted to look like the Micro$oft Windows is No. 2 and only in its appearance not in their calculations  that leave a lot to be desired for several errors that hides: Incorrect Calculator Results When You Use the Percent Key Test in the standard and scientific Micro$oft calculator: 4 sqrt - 2 = -8.1648465955514287168521180122928e-39 (any calculator gives 0) 50 + 25% gives 12.5 (others calculators giving 62.5) 50 [Inv] [Hyp] sin give 4.6052701709914238266212392672083. The arc hyperbolic sine in degrees and radians gives the same value always in radians, when in degrees should give 263.8625443153 0 x^y 0 = 1 most calculators give a value 0 or better unspecified or undefined, but Google calculator also gives 1 with 0^0. See: Wikipedia Zero to the zero power The scientific mode before Windows 7 is not the key square root [sqrt], which is a real mess. When switch between standard and scientific Micro$oft calculator, calculations are lost, which does not happen with my Excel floating calculators. I have much more reason to never use this impoverished calculator...

Running this calculator

To run with CTRL + SHIFT + C shortcut keys in a shortcut created on the desktop with this destination:

Keyboard shortcuts for Windows The Windows calculator I run with ALT + SHIFT + C and has a very large stick which makes very small!!! and that would not be maximized or enlarged?!!! which is why I started ruminate on the idea of making one in Excel, that it has an excellent Zoom...

Traducción al español aquí

No Response to "How to make Excel calculators"

Leave A Reply

Dime si te gusta lo que lees y, si no te gusta, dime por qué. Tengo habilitada la moderación de comentarios. Tu comentario se publicará pronto.

Tell me if you like what you read here and if you don't like, tell me why. I've enabled comment moderation. Your comment will be published ASAP.

Mi Lista de Blogs- My Blog List