Trying to automate an Excel procedure-VBForums
Results 1 to 8 of 8

Thread: Trying to automate an Excel procedure

  1. #1

    Thread Starter
    New Member
    Join Date
    May 2017
    Posts
    4

    Trying to automate an Excel procedure

    My knowledge of VB script is exceptional poor:

    I am using software that allows the use of VB Script. The scenario:

    I have identified a list of 461 items. The number of items will change every month. Each item has two dates I wish to compare and then count some of the results. I can export this list into excel and easily do the operation there however I am attempting to automate the process using VB Script.

    The variables:
    A: Expected delivery date
    B: Closed date and time

    In Excel I use DateDif(A,B,d) for each line. Then I use Count(the datedif results column,>0).

    The objective is to know how many items have not been delivered before A (the expected delivery date).

    I have tried various combinations of script functions to achieve a single value to be presented but without luck; typically mismatches. See below:

    Function NumIndValue
    If DateDif(A>B) Then
    NumInvalue = 0
    If DateDif(B>=A) Then
    NumIndValue = Count(DateDif(B>A))
    End If
    End If
    End Function

    Any help would be greatly appreciated.

  2. #2
    New Member
    Join Date
    May 2017
    Posts
    4

    Re: Trying to automate an Excel procedure

    This might help. I assumed the actual date is always after proposed date. If not you'll need to change the code. Call the func for each set of date pairs. After the last pair the func returns a late/early day count. Not tested so YMMV. Bob

    Code:
    Private Function NumIndValue(ProposedDate As Date, ActualDate As Date) As Integer
       
       Static Counter As Integer
       
       'If DateDiff("d", ProposedDate, ActualDate) > 0 Then
    
       ' Edit - count ALL days you miss the date by
       If DateDiff("d", ProposedDate, ActualDate) <> 0 Then
          'early/late deliveries
          Counter = Counter + 1
       End If
       NumIndValue = Counter
       
    End Function
    Note -- I wrote this in VB5, test to use in VBS/VBA

    Hey thanks for the welcome. I haven't written any code in a long time, feels good.
    Last edited by Bob_too; May 18th, 2017 at 10:16 AM.

  3. #3
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    38,874

    Re: Trying to automate an Excel procedure

    Welcome to VBForums

    Thread moved from the 'VB6 and Earlier' forum to the 'ASP, VBScript' forum

  4. #4

    Thread Starter
    New Member
    Join Date
    May 2017
    Posts
    4

    Re: Trying to automate an Excel procedure

    Thank you for the suggestion. I have typed it and I am fiddling with it. In principle it ought to work its just getting the host program to recognize how to run it.

    For example it does not recognize the the word "Private" claiming Syntax error.

    When starting with the word "Function" I get an error claiming a problem on line 1 position 28. (Please note I have to substitute "ProposedDate" with A and "ActualDate" with B as these are the input values for these two arrays.

    This is a good starting point. Thank you. Any other suggestions are always welcome.

  5. #5

    Thread Starter
    New Member
    Join Date
    May 2017
    Posts
    4

    Re: Trying to automate an Excel procedure

    Thanks si_the_geek for the move.

  6. #6
    New Member
    Join Date
    May 2017
    Posts
    4

    Re: Trying to automate an Excel procedure

    No problem, hope it works for you, I changed it slightly just a minute ago. Should give a good starting point. You may need to remove Private. I wrote it in vb5.


    Private Function NumIndValue(A As Date, B As Date) As Integer

    Static Counter As Integer

    ' to count ALL deliveries (early and late)

    If DateDiff("d", A, B) <> 0 Then
    Counter = Counter + 1
    End If

    NumIndValue = Counter

    End Function
    Last edited by Bob_too; May 18th, 2017 at 12:12 PM.

  7. #7

    Thread Starter
    New Member
    Join Date
    May 2017
    Posts
    4

    Re: Trying to automate an Excel procedure

    This is promising: Different error messages!

    Seems B is a type mismatch. Although I believe my filter to derive the array for the Closed date is correct I suspect it might be causing an issue. To get to this error I had to rewrite as follows:

    Code:
    Function NumIndValue
    
    Dim Counter
    
    ' to count ALL deliveries (early and late)
    
    If DateDiff("d", A, B) <> 0 Then
    Counter = Counter + 1
    End If
    
    NumIndValue = Counter
    
    End Function
    I am looking forward to the next error to see progress!

  8. #8
    New Member
    Join Date
    May 2017
    Posts
    4

    Re: Trying to automate an Excel procedure

    Counter needs to be Static Integer to retain it's value between calls. Otherwise it's set to 0 each time and you lose your count. The function NumIndValue should be defined as an Integer.

Posting Permissions

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



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.