Forum Discussion
Create data abstraction tool
Questions to ask yourself:
- Am I choosing the right tool? Excel is appropriate for calculations (arithmetic operations, string manipulations…); it is excellent for analyzing multiple data records. OTOH, if a user needs to be working with just one (medical) record at a time, without viewing or accidentally modifying other records, other software – including SharePoint and Microsoft Access – may be a better choice.
- How is this patient information to be stored? Do you expect it to be stored in one spreadsheet in a multi-patient workbook? In its own workbook? In some other type of file? In a (relational or NoSQL) database?
- Medical professionals have the responsibility for protecting the patient information from inappropriate disclosure, and storing it in Excel workbooks is a poor way to protect it. Does HIPAA apply? Does the patient information include PII?
- Can you protect the custom software itself from alteration? Do you need to protect it as intellectual property? Things to consider for Excel are here.
If you determine that you will use Excel…
Realize that you can write Excel formulas that are excellent at manipulating cell information (and this community can certainly help), but if your forms need to respond to user actions in ways other than formula recalculation, you will very likely need to write code in the procedural language VBA (Visual Basic for Applications; it's very similar to classic Visual Basic, less similar to VB.NET). Both Excel and Access and even Word rely on VBA for advanced custom capabilities.
You can browse your local library, which is very likely to have books on VBA, or even a used bookstore. The language has not changed (to my knowledge) for many years, so even an older book will be useful. If you are not already familiar with classes, objects, properties, methods, and events, start by learning a little about them – they apply for most modern computer languages, and there are probably YouTube videos with a good introduction.
Aside from learning VBA, do your initial design work: both sketch out the appearance of your forms and determine what features/capabilities (record search, retrieval, security…) and behavior (validation and data flagging, data storage…) they must have. Once you have more specific questions, ask again (in a separate post(s); identify which version of Excel you are using).