My friend said he was going to create an application inside of Excel. I told him that maybe he meant macros but he seemed convinced he could create a typical CRUD application INSIDE of Excel.
Is this true?
+4
A:
You're both right. You can use VBA inside Excel and some form functionality to create a fully functional CRUD process with a UI inside of Excel, and you could persist that data to your workbook or to some other storage area (text, XML, Access, another DBMS). It would not be a full application, per se, as it is limited to running inside of the Excel app, but it would be something more than a simple macro of "do these pre-defined steps in order."
Anthony Pegram
2010-09-08 15:44:19
The fact that it only runs inside Excel is somewhat moot, since a lot of other technologies require an underlying application / VM / runtime / interpreter as well.
tdammers
2010-09-08 15:47:49
@tdammers, that is correct. It changes a runtime requirement from, say, having .NET 4 on the machine to instead having a working copy of Excel 2010. There are obviously other considerations to be made, such as code security, whether the user is allowed/has elected to run macros, etc.
Anthony Pegram
2010-09-08 15:50:34
Personally, I'd say the biggest concern is the immense amount of pain you'd have to endure just to get a very basic CRUD application running - you'll be working with a sub-optimal non-standard storage engine full of quirks, code in VBA for crying out loud, and all sorts of other limitations everywhere. If 'has to install a database' is a concern, I'd just write a native app and use Sqlite or something.
tdammers
2010-09-08 16:00:06
Even without Access being installed, you can use ADO in Excel to create/read/write a Jet database
barrowc
2010-09-09 02:35:24
A:
Sure you can.... use VBA and populate cells with data from a DB, when the cells change values update the database
But why would you is the bigger question here
SQLMenace
2010-09-08 15:44:52
A:
It is true. VBA can summon COM, which can do pretty powerful things. I used an excel file for receiving reports built by a macro inside, that searches many remote databases to group and aggregate information. You can modify the registry, make it run programs, make it restart the PC, show messages, create and edit files, make it use Word or Access, call .NET functionality. Anything that doesn't require complex rendering of something.
Alexander
2010-09-08 15:46:55
+1
A:
Sure. Why would you want to?
The short answer is that using VBA, you can create background worker methods that can interface with other Office apps, or with .NET/COM code. However, if you want to add complex business logic to an Excel presentation layer, my first thought would be to create the application in C#, and use the .NET Framework wrappers for Office interop. The first advantage is that you use Excel SOLELY for presentation, supporting an MVC-ish software design. Second, you keep the code where you expect to find it; in code, not embedded in a document.
KeithS
2010-09-08 15:50:23
As for "why would you want to," it is often said "use the right tool for the job." However, that assumes you have the right tool! Sometimes you simply have to "use the tool you have." I've been there. Literally. I've written tons of stuff (at a prior job) with VBA in Excel and Access that I would have loved to have done in C# or VB, but I didn't have those tools at the time or the ability to go around installing applications.
Anthony Pegram
2010-09-08 15:55:03
A:
You might use Excel/VBA because:
- You have VBA--a fully-loaded programming language (though the OO needs work).
- Scalar functions are overloaded to work with arrays.
- A decent IDE and debug facility.
- Excel provides a rich event-driven platform and extends VBA's capabilities with spreadsheet behaviour that "just happens" but would take a lot of coding in a conventional language.
- Form widgets that you can put anywhere, not just on a form.
- Simple but adequate vector graphics.
- Charts, charts and more charts--all dynamic.
- Automatic persistence or, if it's called for, interfaces to just about every file and database medium, including XML and cloud services.
- Relational tables are a native structure.
If it weren't past midnight, I'm sure I could think of some more good reasons, but hey....
Marc Thibault
2010-09-09 04:49:11