Converting excel speardsheets/macro into a database system

[Guest]

I have constructed many interlinking excel based spreadsheets which have
numerous functionality through a series of macros I have written.

I realise that a lot of the calculation in the spreadsheet can be
altered/tampered with. Is there some system/software where I can have the
calculations in the background and just display the results so the
calculations cannot be tampered with and the new inputs can be easly entered?

Please note the only programming experience I have is with excel VBA, so I
doubt I wil be able to cope with any really complicated programming languages
(C++ etc).

For the meantime I have protected the cells/worksheets where possible.

Can anyone offer me any suggestions of how I can improve my systems?

 

[Jim Rech]

For the meantime I have protected the cells/worksheets where possible.

That's the way to go, as I've done for over 20 years of spreadsheet
application development. If someone really wants to change your formulas
you can't stop him but so what? He'll get the wrong answers. In the
systems I've worked on where a user might legitimately want to get a
different result than the formulas calculate I give users override cells.
"Here's the result but enter a different amount over here to be carried
forward if you don't like it."

If you'd prefer a different platform than Excel you might look at Visual
Studio (Visual C# / Visual Basic). You can create user forms there and do
your calculations in code. Also, you can meld the two with a COM add-in
that gets input from a sheet, does the calcs in code and puts the results
back in the sheet. This raises the complexity a lot but you'd learn a new
skill in the process.

--
Jim
|I have constructed many interlinking excel based spreadsheets which have
| numerous functionality through a series of macros I have written.
|
| I realise that a lot of the calculation in the spreadsheet can be
| altered/tampered with. Is there some system/software where I can have the
| calculations in the background and just display the results so the
| calculations cannot be tampered with and the new inputs can be easly
entered?
|
| Please note the only programming experience I have is with excel VBA, so I
| doubt I wil be able to cope with any really complicated programming
languages
| (C++ etc).
|
| For the meantime I have protected the cells/worksheets where possible.
|
| Can anyone offer me any suggestions of how I can improve my systems?

 

[Guest]

Thanks Jim
I think that perhaps I should leave this project on the backburner for now.
Having spoken further with my boss he prefers to have access to the original
spreadsheets so he can easily check that the calculations are correct. If a
new system is utilised it would be probably best (in terms of efficiency) to
hire a programmer to do the underlying coding. I would prefer to concentrate

 

[webmaster]

The trickiest part of C / C++ is navigating pointers ... which covers
most of the difficulty in the Excel C API. This difficulty is
completely eliminated with some tools, such as RapidXLL. Then you
can write functions, our use numerical tools, which pass entire ranges
of values without touching these pointers.

RapidXLL_NET automatically interfaces native C / C++ with Excel Add-Ins
and the .NET framework. Visit http://www.RapidXLL.net for samples
and a free trial.

Then you could write functions like this

RAPID_EXPORT
double getNextPrime(const double aNumber);

or pass entire ranges like this

RAPID_EXPORT
RapidValue computeVariance(const RapidRange& correlationMatrix, const
RapidRange& positionsVector);

Sincerely,

The RapidXLL Team