IDSCD-Studio

More like Numerical Programming Methods for RAD (Rapid Application Development).

    To be even more precise RAD for engineers. Most of us in the engineering field of any kind are well aware of a necessity for crunching numbers as quick as possible and printing out reports without any fuss and fancy expensive software. There is no need to rely on expensive software packages if same old "tried and true" methods from daily practice make more sence. If you can create your own custom application that will satisfy all your requirements and saves you a "half of your workday" sort'o'speak, what more do you need?
    Unless you are a part of a marketing team that needs a fancy selling package, any engineer needs a bunch of numbers to get crunched, a graph (or two) to be built (if any), and a report to print. All these tasks can be easily implemented into something like MS Office Excel, and by using VBA (Visual Basic for Applications) you can develop and customize an application tailored to your exact specification.
    So, I figured that while I'm working on my own stuff, I might as well share some ideas and insights that worked out well. So this part of my website will deal with VBA for Excel and numerical methods. Not exactly for topnotch professionals but if it helps someone to start his/her own application development then my job is done. All it takes is to begin at some point.

Follow the discussion on my blog.

 



   1. Hyperlink update on file opening.                             Back to Menu

+++Module: ThisWorkbook+++
  Sub Workbook_Open()
    Call Sheet1.HLinkUpdate
  End Sub

+++Module: Sheet1+++
  Sub HLinkUpdate()
    Dim LinkAddress As String
    LinkAddress = ActiveWorkbook.Path
    Hyperlinks(1).Address = LinkAddress & "\FileName.xls"
  End Sub

Comment:
   If you have a template with several documents linked together and want them to be updated when you move all files in a different folder, here is how it can be done. A hyperlink(s) will be updated upon opening the workbook.


   2. Show a splash-screen on file opening.                         Back to Menu

+++Module: ThisWorkbook+++
  Private Sub Workbook_Open()
    UserForm1.Show
  End Sub

+++Module: Module1+++
  Private Sub CloseSplashScreen()
    Unload UserForm1
  End Sub

+++Form: UserForm1+++
  Private Sub UserForm_Activate()
    Application.OnTime Now + TimeValue("00:00:03"), "CloseSplashScreen"
  End Sub

' Note: You can adjust TimeValue property to decrease or increase the display time. I typically use 3 seconds, so "00:00:03" is the show time.

Comment:
   The typical splash screen on the opening of the application (if you need any).
   You will need to create a separate module in the workbook (in addition to the standard ones) and a form that will be your splash screen. Use the VBE to insert new UserForm, and using toolbox add necessary controls like Image and Label in order to create your Splash Screen design. Once the splash screen form is finished it is time to add some code. It comes in three different Subs which you put in This Workbook module, Module1 (the name of the new module you insert into the project can be anything you like), and UserForm code window.


   3. Use Input Box to select a range of cells.                     Back to Menu

+++ ¦¦ +++
  Dim Rng As Variant
  Set Rng = Application.InputBox(Prompt:="Pick a Range", _
            Title:="Range Selection", Type:=8)
+++ ¦¦ +++

' Note: Input Box Data Type.
    Code:        Meaning:
      0             A formula
      1              A number
      2             A string
      4             A logical value (True or False)
      8             A cell reference (range object)
      16           An error value (#N/A, etc.)
      64          An array of values
If you set type value equal to 3 (1+2, or number plus text) an Input Box can accept numbers or text.

Comment:
   If you want to make your application more interactive or have a range of data to select manually as an input, then use Input Box to select the range of cell during execution of the application.


   4. Entering value using Input Box.                               Back to Menu

+++ ¦¦ +++
  Dim a As Double
  a = Application.InputBox(Prompt:="Give me something!", _
      Title:="Anything you want!", Type:=1)
+++ ¦¦ +++

Comment:
   An input using Input Box during execution of the application.


   5. Clear cell content.                                           Back to Menu

+++ ¦¦ +++
  Worksheets("sheet1").Range("B1").ClearContents
+++ ¦¦ +++
  Worksheets("sheet1").Range("B1:C1").ClearContents
+++ ¦¦ +++
  Range("B1").ClearContents
+++ ¦¦ +++

' Note: The second variant is for merged cells. The last variant works only when the sheet is active.

Comment:
   Clearing cell content during execution of the application or you can assine a macro to a button in your application UI.


   6. Echo input or resulting value.                                Back to Menu

+++ ¦¦ +++
  Worksheets("sheet1").Range("I7") = a
+++ ¦¦ +++
  Range("I7") = a
+++ ¦¦ +++

' Note: The last variant works only when the sheet is active.

Comment:
   It is always a good idea to echo certain results during program execution in order to control the calculation flow.


   7. Using Excel's worksheet functions in VBA.                     Back to Menu

+++ ¦¦ +++
  Pi = Application.WorksheetFunction.Pi
+++ ¦¦ +++

' Note: Also works in Word.

Comment:
   Many but not all Excel worksheet functions can be used in VBA code. Application.WorksheetFunction statement does the trick.


   8. Numerical representation of Pi.                               Back to Menu

+++ ¦¦ +++
  Pi = 4 * Atn(1)
+++ ¦¦ +++

Comment:
   Pi trick. Sometimes it is more usefull to insert a formula into a procedure or a function.


   9. Convertion of degrees to radians.                             Back to Menu

+++ ¦¦ +++
  radA = (degA * Pi) / 180
+++ ¦¦ +++


   10. Convertion of radians to degrees.                            Back to Menu

+++ ¦¦ +++
  degA = (radA * 180) / Pi
+++ ¦¦ +++


   11. Handling an error.                                           Back to Menu

+++ ¦¦ +++
  On Error Resume Next
    If FrameSp = "" Then
      Exit Function
    Else
      LoadGnwRrail = (Displ * Sin(20 * Pi / 180)) / (3 * FrameSp)
    End If
+++ ¦¦ +++
  On Error GoTo ErrorHandler
  'Calculations
... 'Calculations go here
  ErrorHandler:
    If Err.Number = 6 Then
      MsgBox "Error" & Err & ":" & Error(Err.Number) & ". " & _
      "Darn, You forgot to input data!", vbExclamation
    ElseIf Err.Number = 0 Then
      Err.Clear
    Else: MsgBox "Error" & Err & ":" & Error(Err.Number) & ". " & _
         "Try again!", vbExclamation
    End If
+++ ¦¦ +++

Comment:
   In the first case, we simply anticipate the division by zero error and exit the sub or function.
   But when you know what types of error you are dealing with, then set up a trap accordingly. Like in the second example.


   12. Saving a Workbook as a new project on _Open.                 Back to Menu

+++ Module: Save_As +++
 Option Explicit
  Sub SaveAs_onOpen()

  Dim SaveFileName As Variant
  Dim Filter As String
  Dim Iname As String
  Dim Findex As Integer
  Dim Title As String
  Dim MsgNoSave, Style, NoDeal

  MsgNoSave = "File was NOT saved as a new project!"
  Style = vbOKOnly + vbInformation

  If MsgBox("Would you like to save this file as a separate project?", _
   vbQuestion + vbYesNo) = vbYes Then
    GoTo proceed
  Else
    Exit Sub
  End If

  proceed:
    Filter = "Excel Files (*.xls,*.xls)"
    Iname = "Project_"
    Findex = 1
    Title = "Save This Project Under a New Name"
    SaveFileName = Application.GetSaveAsFilename(InitialFilename:=Iname, _
     FileFilter:=Filter, FilterIndex:=Findex, Title:=Title)

    If SaveFileName = False Then
      NoDeal = MsgBox(MsgNoSave, Style)
      Exit Sub
    End If

    ActiveWorkbook.SaveAs Filename:=SaveFileName, FileFormat:=xlNormal, _
     Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
     CreateBackup:=False

    MsgBox "File saved as " & SaveFileName
  End Sub

' You have to call this procedure from ThisWorkbook in the Sub Workbook_Open().

Comment:
   Have you occasionally overwritten the original file from a different project? Well if you anything like me, you might like the pop-up reminder. Annoying but efficient.
   


   13. Entering initial data into cells before computation.         Back to Menu

+++ ¦¦ +++
  If Range("B7,B8,B9") = "" Then
    Msg = MsgBox("Enter initial parameters!", vbOKOnly + vbExclamation)
  Else
    V0 = Range("B7")
    Theta0 = Range("B8")
    t = Range("B9")

Comment:
   A safeguard of a sort when you want to enter the data before running the calculatons.


The site is under continuous development.

sloop

© 2011-2017 by IDSCD-Studio