Hot answers tagged

30

The three points you listed seem fair: It is a fairly important project so it has to work - a C# solution would not be as stable or work as well as the existing a VBA-based solution. Indeed, later, you tell: "I would like to take this opportunity brush up on my C# skills as I am currently not as competent in C# as I am VBA" (emphasis mine). In other ...


15

When people manage data, there are three fundamentally different ways they can add value: Computing Storage and Retrieval Forwarding and Sharing. For computing at the level of simple arithmetic, you can't beat Excel. Even if you are an experienced programmer, you can build a spreadsheet in a fraction of the time you'll take to write and debug a computer ...


14

Wikipedia (and my math teacher) tells me: Stacked exponents are applied from the top down. This is reflected the way Python evaluates it. Microsoft is wrong (once more) And Ruby evaluates it as Python, so it's correct without doubt, since Matz can't be wrong.


12

I would support going to C#. Others have covered your points very well, so I won't rehash what they've said. There are definitely a lot of good reasons to stick with VBA. However, one of my employers did an impeccable job at creating meaningful documentation. So much so that design decisions were actually written down with justification - if only all ...


10

The reason why in mathematics stacked exponents are applies from the top down is that the other way you just get multiplication of exponents: (((2^3)^4)^5) = 2^(3 * 4 * 5)


10

I hate to say it, but your arguments just don't hold water. Unit testing. This has been a solved problem for a very long time. There are many Unit Testing frameworks out there. Pick one. You should have been doing this already. I recommend ours, because it integrates into the IDE and provides other great features. Source control This one is a bit ...


7

I think you're probably remembering FLT_EPSILON, DBL_EPSILON, and so on. They aren't quite what you're describing though: they're not the smallest number greater than 0. Rather, they're the smallest number greater than 1. For better or worse, however, DBL_EPSILON-1 won't be even close to the smallest number greater than 0. Epsilon is really intended to be ...


7

If you really want something that works well for you, then I suggest you get used to the idea of "unnecessarily complex"... that's the nature of dealing with Microsoft Office file formats. I (sort of) like your idea of "blocks"... I would make sub-classed block objects, like Table, with Columns and Rows independent of the notion of cells. Then use your ...


6

If the end product that the users want is tabular data that they can edit or perform ad-hoc analysis on, then developing in Excel may be suitable or even advantageous. The last Excel application I wrote was in a DNA sequencing lab. The application queried an Oracle database for a list of experiments to perform, organized the needed primers into 96-well ...


5

I would advise you to stay with SVN for the MS Office documents for two reasons: It is already there and it is (in my opinion) better for keeping Office documents (look here). Has much more third party tools for doing this. The lock, though can be achieved in Git, is not "the Git kind of way of doing things". If you need these features, stick with the tool ...


5

It's possible to query Excel sheets using SQL with ODBC. The sheets are treated like tables. You can even join the sheets like you would database tables! I'd recommend you try this option first. Google java + ODBC + excel for details. There is apache's POI libary. http://poi.apache.org/ I'm not sure if this works on the old "binary" format or just the new ...


5

I have slightly different advice the most of the responses so far. Attempt to patch the current system I would at least learn the current system well enough to explain to the client how to use it. I would take this time to explain the flaws in their current system, avoid negative words, just tell them what it cannot do even if all the known bugs were ...


5

Something with only 6 forms and such should be easy to rebuild on a more modern framework. I've worked with migrating VB6 projects that had around 200 forms along with dozens of classes and database tables. It doesn't sound like you're looking at anything that messy but looks can be deceiving. I'd have to analyze the code, the database and business ...


5

I'd like to add some ideas/thoughts to @WalterMitty's nice answer. He's right in that it really depends on the purpose of the Excel files. I would add however that if they contain complex rules AND data, you could think about leveraging the power of rules engines. Rules engines such as Drools, OpenRules or OpenL Tablets allow you to use Excel files for ...


4

The proper C++ way is in the <limits> header: in particular std::numeric_limits<T>::epsilon() is defined (specialized) for all the built-in types as in cppreference


4

Take a look at http://www.cplusplus.com/reference/clibrary/cfloat/ the FLT_EPSILON can be used to compare a float variable to 0 (zero), or nearly zero in that case. There's an other article here: http://www.cygnus-software.com/papers/comparingfloats/Comparing%20floating%20point%20numbers.htm EDIT: what you need is this q&a: https://stackoverflow.com/...


4

I'd say do the filtering as part of the processing. Programming in Excel is significantly more painful and limited than any server-side technology you could possibly be using. CSV as an output format is much easier to work with than Excel proper, and virtually every programming language can easily output CSV without requiring any libraries (even writing ...


4

When you are trying to persuade people about the advantages of using a different system you should avoid trying to persuade them. I know that sounds very strange. But what I mean is this: People like using the current system. It works for them. It's easy to use and quick to make changes. To persuade people to use another system, treat them like adults: ...


4

Use MySQL's INSERT... ON DUPLICATE KEY UPDATE syntax to automatically handle the insert/update logic. 40,000 is not that many rows - I'd be surprised if that command took more than a few seconds. Note that you can insert many rows at once: INSERT INTO table (id, name) VALUES (id1, name1), (id2, name2), ..., (idN, nameN) ON DUPLICATE KEY UPDATE id=VALUES(id)...


4

isValid() could be a method implemented in a Strategy pattern. IMO, the benefit of the pattern is future modifiability. That is, there will be a future need for new implementations of isValid() and you don't want to change your code that iterates and validates. In the definition of Strategy, that's the Context class. Your select block, which I believe would ...


4

You can point out that even Microsoft states in this article: Updating the code to improve features or to fix bugs would require that the Office artifact be re-emailed, re-structured, and re-worked by every single user and in every single file that has been using the customization. The article is about different approaches of developing stuff based ...


4

It really depends on how you intend to move to C# (ie what technology you are going to use). If you are going to use OpenXML, then you are talking a total re-write which, if you have a solution that works, isn't recommended. If you are talking about using Interop, you may find that the process is surprisingly smooth. I've moved a lot of code from VBA to C# ...


4

Gain control of how report data is formatted. Excel simply isn't a data format. It has it's own presentation ideas. You can make this work and it sounds like a lot has already been built help you do this. So maybe you don't want to swim against the tide at this point but you're tied to a lot of things you don't want to be tied to. Different versions of ...


4

You wrote The project I'm working on is web based price calculation and I'll only be here for 3 months and there are nobody in the office that can do any coding Note that these two statements are already showing the main issue here: if the company for which you are working really wants to offer a web based price calculation in a permanent fashion - ...


3

There are several libraries available in the Java world, the most popular being Apache POI I believe, which is an open source library that enables you to read and write Excel files (officially supports versions 97 to 2007 but should work fine with 2010 too).


3

.xlsx files are actually XML so you can use most any language out there. Perl's CPAN seems to have a number of options for instance. Most of it is going to boil down to how much money you want to spend vs completeness of the open source modules/ability to write your own compiler.


3

As a developer you will come across this situation all the time - I have a problem with two solutions. Both appear to solve the problem, both are of similar complexity, there is no obvious reason to choose one over the other (patterns, practices, personal preference, etc). Which should I do? The problem with the question you have posed above is that there ...


3

You should look into using a library that doesn't require Excel to be installed; one such example is EPPlus. There are others, as mentioned in this SO question: How to programmatically create a “true” Excel file


3

You are being a bit paranoid, every solution to hide a password won't work against someone who really wants it, but there are better ways to solve this issue so some concern is warranted. Your idea is far worse than the current situation, with your idea everyone has access to the database that can use the scripts, any of them could easily do bad things to ...


3

Here is a solution I have used often in the past: create a regular Excel document (typically in xlsx format) as as template, containing all column headers, including their title and a default formatting for the columns and maybe formatting for title cells. embed that template into the resources of your program. At runtime, the first step is to extract the ...


Only top voted, non community-wiki answers of a minimum length are eligible