4

I am not a VBA programmer. However, I have the 'unpleasant' task of re-implementing someones VBA code in another language. The VBA code consists of 75 modules which use one massive 'calculation sheet' to store all 'global variables'. So instead of using descriptive variable names, it often uses:

= Worksheets("bla").Cells(100, 75).Value

or

Worksheets("bla").Cells(100, 75).Value =

To make things worse, the 'calculation sheet' also contains some formulas.

Are there any (free) tools which allow you to reverse engineer such code (e.g. create Nassi–Shneiderman diagram, flowcharts)? Thanks.

2
  • Ouch, I hope the originator is no longer working for the organisation - or any other org for that matter! Sorry, but I can't think of anything that would help. Perhaps if each variable cell is used in many places, you could export the code then write something to do a regular expression replace with the values - not really sure if that would help though. Commented Jun 26, 2012 at 22:26
  • Thanks I was thinking about something similar. Commented Jun 27, 2012 at 7:34

1 Answer 1

4

I think @JulianKnight 's suggestion should work Building on this, you could:

  1. Copy all the code to a text editor capable of RegEx search/replace (Eg. Notepad++).
  2. Then use the RegEx search/Replace with a search query like:
    Worksheets\(\"Bla\"\).Cells\((\d*), (\d*)\).Value
  3. And replace with:
    Var_\1_\2

    This will convert all the sheet stored values to variable names with row column indices.
    Example:

    Worksheets("bla").Cells(100, 75).Value    To    Var_100_75
    
  4. These variables still need to be initialized.
    This may be done by writing a VBA code which simply reads every (relevant) cell in the "Bla" worksheet and writes it out to a text file as a variable initialization code.
    Example:

    Dim FSO As FileSystemObject 
    Dim FSOFile As TextStream 
    Dim FilePath As String 
    Dim col, row As Integer 
    
    FilePath = "c:\WriteTest.txt" ' create a test.txt file or change this
    
    Set FSO = New FileSystemObject 
     ' opens  file in write mode
    Set FSOFile = FSO.OpenTextFile(FilePath, 2, True) 
     'loop round adding lines
    For col = 1 To Whatever_is_the_column_limit
        For row = 1 To Whatever_is_the_row_limit
         ' Construct the output line
            FSOFile.WriteLine ("Var_" & Str(row) & "_" & Str(col) & _
                           " = " & Str(Worksheets("Bla").Cells(row, col).Value)) 
        Next row
    Next col
    
    FSOFile.Close 
    

Obviously you need to correct the output line syntax and variable name structure for whatever other language you need to use.

P.S. If you are not familiar with RegEx (Regular Expressions), you will find a plethora of articles on the web explaining it.

Sign up to request clarification or add additional context in comments.

1 Comment

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.