-
May 17th, 2017, 12:24 PM
#1
Thread Starter
New Member
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.
-
May 17th, 2017, 07:22 PM
#2
Junior Member
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.
-
May 18th, 2017, 09:07 AM
#3
Re: Trying to automate an Excel procedure
Welcome to VBForums
Thread moved from the 'VB6 and Earlier' forum to the 'ASP, VBScript' forum
-
May 18th, 2017, 09:21 AM
#4
Thread Starter
New Member
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.
-
May 18th, 2017, 10:15 AM
#5
Thread Starter
New Member
Re: Trying to automate an Excel procedure
Thanks si_the_geek for the move.
-
May 18th, 2017, 10:18 AM
#6
Junior Member
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.
-
May 18th, 2017, 02:25 PM
#7
Thread Starter
New Member
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!
-
May 18th, 2017, 07:58 PM
#8
Junior Member
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|