Hello there,

I have been given a spreadsheet with many complex formulae in the cells.

My task is to write some VB (in a DLL, but that is immaterial) that replicates the behaviour of these formulae.

Can you tell me - is there a utility (either within Excel or "off-the-shelf") that will convert these GHASTLY-looking things (i.e. nested nested nested IF statements and other rubbish all on one line) into a VB code snippet (or psuedo-code snippet).

This would GREATLY help me in this conversion process.

Thanks.

James

P.S. On a related topic - My Excel skills are very limited. I presume there are "best practice" guidelines for writing formulae in cells, so as to avoid "spaghetti formulae" everywhere. But I don't know anything about this, really. I would like to suggest to the person writing the original spreadsheet that these cells and formulae are written/structured rather poorly - but I don't know if that is true or not! Please can you tell me if there are generally-accepted ways of creating sheets to avoid "spaghetti formulae"? Thanks.

//##

The problem (if you can call it that) with excel formula is exactly as you point out. You can fit very complex (spagetti) formula on one line. As far as "best practice" is concerned, sometimes it is tidier to have as few formula as possible on the spreadsheet resulting in, well, spagetti. But as long as the spreadsheet also contains detailed comments explaining the formula, this isn't necessarily a problem.

I don't know of any off the shelf utilities that will convert your excel formula into VB.

 You can enter a formula in VBA excel in the following way:

  Location = " formula "

  Example:

  ' Paste formula
        Range ("$M$10") = "= (SUMIF("$B$10:B$113","$L10","$K$10:$K$113")))"

  Or

  ' Paste formula
        Range(B_Str(4)) = _
        "=IF((SUMIF(" & B_Str(1) & "," & B_Str(2) & "," & B_Str(3) & _
        ")=0),"""",(SUMIF(" & B_Str(1) & "," & B_Str(2) & "," & B_Str(3) & ")))"


  Remember that two quote signs are doubled in the string: ( """" ) as above.

Thanks.

I have found the best way to do this is simply to paste the formula into Notepad, then turn it into pseudo-VB manually, by examining it bit by bit and inserting the necessary line-breaks and tabs to make it more readable. I then take that as a basis from which to write my real VB equivalent.