I know it's VBA, but really?!?



  • I'm digging through a reporting spreadsheet into order to find out what makes it tick. The following is typical of what I am finding:

    Public Function Get_DateRange(pblnPrev As Boolean)
    
    <span style="color: #008000; font-weight: bold">If</span> <span style="color: #008000; font-weight: bold">Not</span> pblnPrev <span style="color: #008000; font-weight: bold">Then</span>
    	FirstShiftStart <span style="color: #666666">=</span> Format(strShiftStartTime, <span style="color: #BA2121">&quot;hh:mm:ss&quot;</span>)
    	SecondShiftStart <span style="color: #666666">=</span> Format(strShiftEndTime, <span style="color: #BA2121">&quot;hh:mm:ss&quot;</span>)
    	strCurrentTime <span style="color: #666666">=</span> Format$(strServerNow, <span style="color: #BA2121">&quot;hh:mm:ss&quot;</span>)
    	ReportStart <span style="color: #666666">=</span> Format(strShiftStartTime, <span style="color: #BA2121">&quot;yyyy-mm-dd hh:mm:ss&quot;</span>)
    	ReportEnd <span style="color: #666666">=</span> strServerNow
    <span style="color: #008000; font-weight: bold">End</span> <span style="color: #008000; font-weight: bold">If</span>
    
    strSQL1 <span style="color: #666666">=</span> <span style="color: #BA2121">&quot;Exec &quot;</span> <span style="color: #666666">&amp;</span> strSPName <span style="color: #666666">&amp;</span> <span style="color: #BA2121">&quot; &quot;</span>
    
    strSQL <span style="color: #666666">=</span> strSQL1 <span style="color: #666666">&amp;</span> <span style="color: #BA2121">&quot;@StartTime = &#39;&quot;</span> <span style="color: #666666">&amp;</span> ReportStart <span style="color: #666666">&amp;</span> <span style="color: #BA2121">&quot;&#39;,@EndTime = &#39;&quot;</span> <span style="color: #666666">&amp;</span> ReportEnd <span style="color: #666666">&amp;</span> <span style="color: #BA2121">&quot;&#39;, @Unit = &#39;&quot;</span> <span style="color: #666666">&amp;</span> _
    					intUId <span style="color: #666666">&amp;</span> <span style="color: #BA2121">&quot;&#39;&quot;</span>
    

    End Function



    To summarise:
    • Function is treated as a Sub
    • A single, lonely parameter in
    • Lots of incoming global variables
    • Main results output via another global variable
    • Lots of other side effects


  • Trolleybus Mechanic

    @OzPeter said:

    To summarise:

    • Function is treated as a Sub
    • A single, lonely parameter in
    • Lots of incoming global variables
    • Main results output via another global variable
    • Lots of other side effects

     

    And also, what if the report is run at ;DROP DATABASE-- o'clock?  WHAT THEN?

     



  • @Lorne Kates said:

    @OzPeter said:

    To summarise:

    • Function is treated as a Sub
    • A single, lonely parameter in
    • Lots of incoming global variables
    • Main results output via another global variable
    • Lots of other side effects

     

    And also, what if the report is run at ;DROP DATABASE-- o'clock?  WHAT THEN?

     


    You get to go home early.


  • Considered Harmful

    @mikeTheLiar said:

    @Lorne Kates said:

    @OzPeter said:

    To summarise:

    • Function is treated as a Sub
    • A single, lonely parameter in
    • Lots of incoming global variables
    • Main results output via another global variable
    • Lots of other side effects

     

    And also, what if the report is run at ;DROP DATABASE-- o'clock?  WHAT THEN?

     


    You get to go home early.
    And you never have to come back.



  •  What do you mean by "Function is treated as a Sub"?



  • @Medinoc said:

     What do you mean by "Function is treated as a Sub"?

     

    Functions have their return value SET...Subs do not have a return value....So Not using the available return value of a Function is "Function is treated as a Sub"!


  • @Medinoc said:

     What do you mean by "Function is treated as a Sub"?

     

    Functions have their return value SET...Subs do not have a return value....So Not using the available return value of a Function is "Function is treated as a Sub"!


  • @Medinoc said:

     What do you mean by "Function is treated as a Sub"?

     

    Functions have their return value SET...Subs do not have a return value....So Not using the available return value of a Function is "Function is treated as a Sub"!

  • Trolleybus Mechanic

    @TheCPUWizard said:

    Functions have their return value SET...Subs do not have a return value....So Not using the available return value of a Function is "Function is treated as a Sub"!
    @TheCPUWizard said:
    Functions have their return value SET...Subs do not have a return value....So Not using the available return value of a Function is "Function is treated as a Sub"!
    @TheCPUWizard said:
    Functions have their return value SET...Subs do not have a return value....So Not using the available return value of a Function is "Function is treated as a Sub"!
     

    Stack overflow exception in FunctionAsASub()...



  • @Lorne Kates said:

    @TheCPUWizard said:

    Functions have their return value SET...Subs do not have a return value....So Not using the available return value of a Function is "Function is treated as a Sub"!
    @TheCPUWizard said:
    Functions have their return value SET...Subs do not have a return value....So Not using the available return value of a Function is "Function is treated as a Sub"!
    @TheCPUWizard said:
    Functions have their return value SET...Subs do not have a return value....So Not using the available return value of a Function is "Function is treated as a Sub"!
     

    Stack overflow exception in FunctionAsASub()...


    Your stack can only hold three function calls? That's a lot of local variables.



  •  Bug closed (Working as designed). Function Get_DateRange has expected return value of Nothing. See documentation.



  •  I've seen the same, and worse, in projects when the author (I hesitate to say programmer) knows one language's syntax and decides to use it in a completely unrelated language.  The worst I've seen is an access database with tons of completely non-functional VBA modules that were written in PL/SQL.  That project was handed to me as "finished and ready for production except for the data pulls". 


Log in to reply