Background:

I have an application that stores its information in a
database. I am now working on adding an Excel piece to
the program that allows the user to make very limited and
structured queries to the database.

For example, in a cell they can enter the
formula '=LightFixtureCount("A")' and my program will
return the count of light fixtures of type A. The user
will either type theses formulas in by hand (the rare
case) or use a dialog box that I write toc reate the
formula. In this example, the box would have a list of
light fixtures that are defined in the database. After
selecting the fixture they want, the above formula is
created for them and inserted in the active cell.

Actual Question:

If you were going to do this, what combination of
technologies would you use? I would like it to work with
Excel 2000 and later. I do not need to support Excel 97.
If your answer would be different if I did not need to
support Excel 2000, I would be interested to hear that as
well.

Right now, I have a quick-and-dirty version running in an
XLA file. It is working pretty well, but I am concerned
that I might run into problems of scale in the future as
the number of different queries I support grows larger.

I have no experience developing on top of MS Office, so
any advice anyone might have would be appreciated. Is my
XLA file gonna last me for two years? Will I wish that I
had done this in C# six months from now? Or should I
implement in VB?

Thanks,
David Robison

Why not build your queries in Access and output the results to Excel?

--
Michael Hopwood (Phobos)


"David Robison" wrote in message
...
Background:

I have an application that stores its information in a
database. I am now working on adding an Excel piece to
the program that allows the user to make very limited and
structured queries to the database.

For example, in a cell they can enter the
formula '=LightFixtureCount("A")' and my program will
return the count of light fixtures of type A. The user
will either type theses formulas in by hand (the rare
case) or use a dialog box that I write toc reate the
formula. In this example, the box would have a list of
light fixtures that are defined in the database. After
selecting the fixture they want, the above formula is
created for them and inserted in the active cell.

Actual Question:

If you were going to do this, what combination of
technologies would you use? I would like it to work with
Excel 2000 and later. I do not need to support Excel 97.
If your answer would be different if I did not need to
support Excel 2000, I would be interested to hear that as
well.

Right now, I have a quick-and-dirty version running in an
XLA file. It is working pretty well, but I am concerned
that I might run into problems of scale in the future as
the number of different queries I support grows larger.

I have no experience developing on top of MS Office, so
any advice anyone might have would be appreciated. Is my
XLA file gonna last me for two years? Will I wish that I
had done this in C# six months from now? Or should I
implement in VB?

Thanks,
David Robison

I'm not sure that putting the queries in Access would help me (though I
could be wrong).

My goal is to give people who are comfortable using Excel a way to import
data from the database. They need to be able to pull up a dialog box,
select a couple of parameters from drop-down lists, and then have their
number in a cell. I just want to see if using VBA in Excel is going to
scale well, or if I should be using something else instead (such as VB or
C#).

Having said that, is there a way that Access could help with the UI for
Excel?

Thanks,
Dave

"Michael Hopwood" wrote in message
...
Why not build your queries in Access and output the results to Excel?

--
Michael Hopwood (Phobos)

It is my opinion from your description that VBA will scale for your
application.

The choice between VBA and, say, Visual Studio (VS) usually depends on
how you want to deliver your code to users. An XLA add-in is easy for
a user to implement however you must consider that your 'embedded' VBA
source code can be viewed by an advanced Excel user, even if you
password protect it. Developing an add-in in VS will involve
registering a compiled component on the user's machine and your source
code is vulnerable only to reverse engineering. A network
administrator's security policy may dictate which approach is used.

The choice between C# and VB in VS.NET is now, as MS says, a lifestyle
choice. However, VB has some advantages over C# when it comes to
working with VBA. For example, many VBA functions have optional
parameters; with C# you must supply a 'Missing' object for each
optional argument you are not using which number thirty or more. For
more details see:

http://msdn.microsoft.com/library/de.../OffCSharp.asp

BTW I don't think the MS Access suggestion is appropriate to you.

"David Robison" wrote in message ...
I'm not sure that putting the queries in Access would help me (though I
could be wrong).

My goal is to give people who are comfortable using Excel a way to import
data from the database. They need to be able to pull up a dialog box,
select a couple of parameters from drop-down lists, and then have their
number in a cell. I just want to see if using VBA in Excel is going to
scale well, or if I should be using something else instead (such as VB or
C#).

Having said that, is there a way that Access could help with the UI for
Excel?

Thanks,
Dave

"Michael Hopwood" wrote in message
...
Why not build your queries in Access and output the results to Excel?

--
Michael Hopwood (Phobos)

Thanks for the feedback. Everything I had previously read in the MSDN docs
seemed to suggest that VBA was the way to go, I just wanted to get a little
feedback before rushing forward.

I do understand that an XLA add-in is not secure. However, I'm okay with
that, since it is just going to be some UI code and a couple of SQL queries.
I'm debating between password protecting the code to protect users from
accidentally messing with it and not protecting it so that they can see what
I did if they are curious. The database uses Access, so they can already
open that up and look at the structure if they want.

The code that does actual interesting things is written in a more secure
manner (it's a compiled add-in for AutoCAD).

Dave

"onedaywhen" wrote in message
om...
It is my opinion from your description that VBA will scale for your
application.

The choice between VBA and, say, Visual Studio (VS) usually depends on
how you want to deliver your code to users. An XLA add-in is easy for
a user to implement however you must consider that your 'embedded' VBA
source code can be viewed by an advanced Excel user, even if you
password protect it. Developing an add-in in VS will involve
registering a compiled component on the user's machine and your source
code is vulnerable only to reverse engineering. A network
administrator's security policy may dictate which approach is used.

The choice between C# and VB in VS.NET is now, as MS says, a lifestyle
choice. However, VB has some advantages over C# when it comes to
working with VBA. For example, many VBA functions have optional
parameters; with C# you must supply a 'Missing' object for each
optional argument you are not using which number thirty or more. For
more details see:


http://msdn.microsoft.com/library/de.../OffCSharp.asp

BTW I don't think the MS Access suggestion is appropriate to you.

"David Robison" wrote in message
...
I'm not sure that putting the queries in Access would help me (though I
could be wrong).

My goal is to give people who are comfortable using Excel a way to
import
data from the database. They need to be able to pull up a dialog box,
select a couple of parameters from drop-down lists, and then have their
number in a cell. I just want to see if using VBA in Excel is going to
scale well, or if I should be using something else instead (such as VB
or
C#).

Having said that, is there a way that Access could help with the UI for
Excel?

Thanks,
Dave

"Michael Hopwood" wrote in message
...
Why not build your queries in Access and output the results to Excel?

--
Michael Hopwood (Phobos)