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.

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.

Welcome to VBForums :wave:

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

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.

Thanks si_the_geek for the move.

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

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!

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.