Preparing a dedicated application on request in Excel requires, first of all, ordering and validating data at the stage of entering them by end users. We usually want to record the data into an ordered structure - a table that we can later search using query builders, Excel functions, VBA code, ADO / DAO libraries and SQL language.
When we want to prepare windows with fields which controls given data type we can of course use the data checking mechanisms built into the sheet (menu items available on the Data / Data tools / Data correctness ribbon). However, due to the need to secure the program from the side of its modification by the end user, forms controlled in VBA may be a better solution. Such a solution allows us to completely hide the built-in interface and fully support the input, reading and presentation of data only inside visible windows. The executed program will not even look like as an Excel workbook, but rather as an independent database application with its own interface (the Excel menu will be hidden), built-in lists, reports, analyzes and printouts. The greatest advantage of this approach will also be the possibility of importing the worksheets to an Access database file or even to SQL server, and allow many users to work on a larger amount of data at the same time. It is important that by modifying the VBA code later, we can leave the application interface as it is.
It is also possible to create an Excel VBA application with the data storage in the form of separate file, e.g. in the Access database format. Such a solution significantly speeds up the analysis and generation of summaries, makes the program resistant to data corruption and enables data sharing by many users in a much more effective way than simply sharing a workbook. In addition, launching such a project does not require the installation of any additional elements or libraries at the client's stand - it uses the resources of Excel, Office and Windows.
