Traducir el blog

How to dynamically generate GUI

Posted on enero 15, 2011 by Excel Pedro Wave

Continuing with the previous post on Time Zones Map with World Clocks that serve as an example of how to build dynamically graphical user interfaces - GUI, when it is needed and not before. All examples are designed in Excel 2010 compatible with Excel 2007.

When I decided to create a World Clocks map with geolocated cities, I didn't think the biggest problem would be to upload the file on AyudaExcel forum, which is limited to compressed files of less than 97.7 KBytes.

The file has about 6 MBytes uncompressed and compressed over 1 MByte, so it was impossible to upload to the forum. After several hours I managed to occupy only 78.4 KBytes, thanks to the dynamic generation of all objects, clocks, cities and the world map that can be downloaded from my previous article:


I now share the new version 2.0 that occupies 168 KBytes and offers three new features:
  • World Map with day and night updated every 15 minutes
  • Translations into Spanish and English
  • Stars do not appear



As in the previous version, initially has a shape of each type needed to generate a map of cities and clocks. The initial forms are five:
  • Mapa - It is an image and is empty when you open the file.
  • Reloj0 - It is an analog clock graph larger than others and there is only one.
  • RelojDigital - It is a TextBox with a unique digital clock that appears when you click on the map.
  • Reloj1 - It is a graph of the first of the 34 analog clocks which is used to clone others.
  • Ciudad1 - It is an option button that represents the first of the cities, used to clone others.
Without graphical user interface - GUI dynamic generation, the application would look simpler and with minimal usability:



Sub Workbook_Open() dynamically generates a GUI with the following procedures:
  • RellenaTiempos - Tiempo sheet has only 4 rows and the rest are copied until row 146.
  • CreaMapa(True) - The day and night image of the world map is downloaded from the Web.
  • CreaRelojes - Generates 33 clocks from Reloj1 placing them around the map.
  • CreaCiudades - Generates all clocks into the Ciudades sheet from Ciudad1 with geolocation on the map, depending on their geographic coordinates.

Mundo sheet

The end result is a world map with days and nights, downloaded every 15 minutes from the following Switzerland website: www.fourmilab.ch, developed and maintained by John Walker, founder of Autodesk, Inc. and co-author of the popular AutoCAD program.

34 clocks are generated dynamically appointed as a Reloj1 to Reloj34 and at least 34 cities named as Ciudad1 to Ciudad34, as shown in the figure:

Initially each sheet contains the minimum number of rows and the remaining rows are dynamically generated.

Tiempo sheet


Contains formulas in rows 3 and 4 to get the angle of the analog clocks hands. The macro RellenaTiempos fill 242 rows and paste only the values generated by the formulas to reduce the execution time of the sine and cosine functions.

The viewing angle of the hours hand has simplified  to only change every 10 minutes and the minutes hand changes every 15 seconds. Of course, the seconds hand changes every second.

Ciudades sheet

The routine CreaCiudades copy row 3 down to get up to 700 cities.

Relojes sheet

The routine CreaRelojes copy row 4 down to obtain the information of the 32 analog clocks and their cities.

Acknowledgements

I have to publicly thank and acknowledge the great work from:

George Lungu to write very interesting articles on his blog Excel Unusual
This work has been inspired by: How to Make an Analog Clock in Excel

Chip Pearson to learn from his blog how to:

Web Services
GeoLookupXML wiki.wunderground.com

Download the new version from here:

RelojesMundialesPW22.xlsm




WARNING: This software is not designed for commercial use and it is Copyleft and licensed "as is" without assuming the author any consequence arising from the use thereof for purposes other than education.


Licencia de Creative Commons
RelojesMundialesPW20.xlsm by Pedro Wave is licensed under a

The best thing is to see the code implemented and designed to understand the dynamic generation of graphical user interfaces, because the code is not hidden or protected.

Traducción al español aquí.

Etiquetas: , , , , , , ,

No Response to "How to dynamically generate GUI"

Leave A Reply

Indícame las erratas que encuentres y qué es lo que te gustaría ver en los próximos artículos.

Suscribirse a: Enviar comentarios (Atom)

Mi lista de blogs

Mostrar todo