Results 1 to 2 of 2

Thread: Basic addition sub

  1. #1
    Seeker kitsu_ne's Avatar
    Join Date
    Jan 2014
    Location
    http://www.khanacademy.org/profile/killervulpix/#programs
    Posts
    16
    Articles
    0

    Basic addition sub



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Someone that I work with (in another office) asked me if I could help him with an issue he had. In a nutshell, he basically had a spreadsheet with dates when a job needed to be accomplished. He updates this every day for all the jobs that are being completed. He wanted an easy way to figure out what the next due date would be, which was 180 days later. So basically the Date + 180.

    I elected to write this for him as a Subroutine that would kick off whenever he pressed CTRL+SHIFT+A

    The code works great, but seeing as how I'm still fairly new to all this I was wondering if you would have done this differently? I'd love to be able to use this as an opportunity to improve. Thanks!
    The code has comments all over the place, but if you need clarification, let me know.


    In essense, the user selects a cell and executes the program. Message box asks for a number, if no number is provided it gives an error statement and tries again. At any time the user can cancel. If there's a number, or a date in this cell, the values are added together, and then the cell is replaced with this new value. If not, an error message is displayed. Once again, the user can cancel whenever they want.

    Thanks!!!!
    Code:
    Sub addValue()
    '
    ' Take a number and add a new number to it.
    '
    'To complete this transaction well need to create three variables.
    'The variable "addValue" will be collected as the number to be added to the existing value.
    'The term "Dim" allows us to create a new variable to be used temporarily throughout the function.
     
    
    'Now we ask for a value to add, and supply the default value of "180".  We check to see if the value is a number, and if not we supply an error message.
    Dim addValue
    Dim testValue
    Do
    addValue = InputBox(Prompt:="Input a value to add.", _
                  Title:="Add Number", Default:=180) 'The underscore character is a continuation character.
    
    If addValue = "" Then
        Exit Sub
    End If
    
    testValue = IsNumeric(addValue)
    If testValue = False Then
       testValue = MsgBox("This is not a number Value.", vbRetryCancel)
    End If
    
    If testValue = vbCancel Then
        Exit Sub
    End If
    
    'The below "loop until testValue = True" causes the above statement to repeat until the user supplies a number value.
    Loop Until testValue = True
    
    
    'Whatever the user supplies is returned by the function and then added to the "addValue" variable.
    'Now we need to determine what we are working with, a Date or a Number.
    'To do this we use two functions, "IsDate()" and "IsNumeric()".  These will return a boolean value (True/False)
    'We also specify a small bit of code to be processed if the function returned "True".
    'First we check if the value is a date, and if so, we process the code with this format.
    If IsDate(ActiveCell) Then
    Dim firstDate As Date
    Dim newDate As Date
    
        firstDate = ActiveCell.Value   'This takes the current cell value and places that value into the firstDate variable.
        newDate = firstDate + addValue 'Here we add the two variables together and place that value into the newDate variable.
        Selection.Value = newDate      'Lastly, we take this new value, and put it into the active cell, replacing the old value.
        
    'If the first test was false, we have another test performed to see if the value is at least a number that can still be added.
    ElseIf IsNumeric(ActiveCell) Then
    Dim firstNum As Integer
    Dim newNum As Integer
        firstNum = ActiveCell.Value   'This takes the current cell value and places that value into the firstNum variable.
        newNum = firstNum + addValue  'Here we add the two variables together and place that value into the newNum variable.
        Selection.Value = newNum      'Lastly, we take this new value, and put it into the active cell, replacing the old value.
    'If the two tests were false, we display an error message, and then exit the subroutine.
    Else: MsgBox "You cannot add this number."
            Exit Sub
    End If
    
    'Success!
    End Sub

  2. #2
    You could use
    Code:
    addValue = Application.InputBox(Prompt:="Input a value to add.", _ 
                 Title:="Add Number", Default:=180, Type:=1) 'The underscore character is a continuation character.
    to force input of a number and get rid of a couple of checks.

    Application.InputBox is different from the usual VBA InputBox as it allows you to define the type of input allowed. Type = 1 is a number, 2 is a string, 8 is a range... See help for more info.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •