access database tables in Excel

[MartinBoers]

I'm using Excel 2010, which I have quite a bit of experience with, although I haven't used macros yet.

I'm also using SQL Server 2008 Express with Advanced Services. I'm still a newbie with SQL, and so far I've worked with databases only via SQL Server Management Studio.


Could someone please tell me if my understanding is correct with respect to the following points?

• Macros in Excel are written only in VBA.

• Excel macros can be created via Excel's macro recorder. For simple functionality, the macro recorder can generate VBA code for the user, while for more sophisticated functionality, macro code must be written by the user.

• Because the macro recorder is incorporated into Excel, there is no need to install Visual Basic separately.

• Excel macros can make SQL queries or commands to databases.

• Excel's PowerPivot add-in has SQL capabilities, but does not employ Excel macros.

• In Excel, the choice of whether to use macros or PowerPivot to make SQL queries or commands would most likely depend on the situation.

• By default, VBA code is interpreted at run time, but can be compiled if desired.

• Excel can use inline functions (as opposed to macros) to run code written in C# (and possibly other programming languages).


Thanks for any information anyone can provide.

Incidentally, I'm running Windows XP Professional.

[patel]

• Macros in Excel are written only in VBA. - YES

• Excel macros can be created via Excel's macro recorder. For simple functionality, the macro recorder can generate VBA code for the user, while for more sophisticated functionality, macro code must be written by the user. - YES

• Because the macro recorder is incorporated into Excel, there is no need to install Visual Basic separately. - YES

• Excel macros can make SQL queries or commands to databases. - YES

• Excel's PowerPivot add-in has SQL capabilities, but does not employ Excel macros.

• In Excel, the choice of whether to use macros or PowerPivot to make SQL queries or commands would most likely depend on the situation.

• By default, VBA code is interpreted at run time, but can be compiled if desired. - NO ?

• Excel can use inline functions (as opposed to macros) to run code written in C# (and possibly other programming languages). - ?

[snb]

All yes, except the last sentence, but that contains several assertions.

[MartinBoers]

Many thanks to both Patel and snb.

To snb:


All yes, except the last sentence, but that contains several assertions.

Could you please elaborate?

[Kenneth Hobs]

I would say yes to all except the last. I don't know what you mean there. I don't know what an in-line functions is. Maybe you mean built-in functions?

One can use VBA to make custom User-Defined-Functions (UDFs).

Of course in VBA, you can Shell() to executable code compiled by C# and such. Some DLL functions can be used from other language built DLLs in VBA.

If you installed Visual Studios rather than VBA, that might give more c# options.

I have used some vb.net functions from mscorlib.dll in VBA.

For my vb.net DLL COM visible example, see: http://www.vbaexpress.com/forum/showthread.php?t=45316

For c# and UDF in Excel, see: http://pragmateek.com/excel-udf-in-c-from-scratch/

[snb]

Excel can use inline functions :yes
(as opposed to macros): if you mean macros can't use inline functions: not correct, because macros can.
to run code written in C#: I don't know
and possibly other programming languages: it can read/write HTML, PHP, etc.

[MartinBoers]

Thanks for replying Kenneth Hobs and snb. I appreciate you taking the time to explain things to me.