Solved

Using Trends to predict when to order just-in-time

Posted on 1997-05-15
10
534 Views
Last Modified: 2012-05-04
I am a newbie VB programmer.  I am in "advanced" programming in my school.  The first semester was about HTML coding and now we are learning about VB code.  The problem is, our school isn't that rich and the guy who is teaching the class doesn't have enough time to teach our class (he donated his time freely), so now my school is calling the class an "Independent Study" class (because he ins't teaching or helping us anymore).  Any ways, has given us our final project and we have little coding know-how to do his little wizard VB projects.

We are using the VB 4.0 that comes with Excel 95.  My project is "Inventory Management".  I have to use the TREND function to predict when to order stock, for just-in-time.  In my module, I would have something like this for code:

Do
OnHandRead = Application.InputBox("Number of Products Sold", "Enter on hand count", OnHand)
OnHand = IsNumeric(OnHandRead)
If OnHand = False Then MsgBox "Invalid Number", 16, "Error"
Loop Until OnHand = True
OnHand = OnHandRead

(and one for dates too) I was thinking of taking the dates and converting them back to real numbers and taking the TREND of that (i.e. 1/1/98 comes out to be around 36790 or something like that).  
So, lets say we have 39790, 39791, 39792, 39793, 39794 for the numbers for the dates, so TREND(39790, 39793, 39796, 39799, 39802) would produce 3 days for the trend, right?  Then, I could have 5,10,15,20,25 for each day for the number of products sold, so that TREND(5,10,15,20,25) would produce 5?  Then I could have them type in the original on-hand they had and setup something like this (assuming they originally had 100 on-hand):

pseudo-code
SomeVar = (100 - [30 / 3] * [5 * 1])  
If Somevar / OrginalOnHandCount < 40 then something...???

I am not sure what formula to use or how to set it up.  I already tried the TREND function in the module coding and didn't work.  Any formulas, coding or suggestions will taken gladly.

Thanks,

Adam Leinss
0
Comment
Question by:Adam Leinss
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
10 Comments
 
LVL 5

Accepted Solution

by:
y96andha earned 200 total points
ID: 1426331
Please try this piece of code. It shows how to use the trend function. Please comment if you have any questions about it.


Dim numdates As Long
Dim value, flag As Long, currentstock As Long
Dim eachday As Single
numdates = 0
Worksheets.Add
ActiveSheet.StandardWidth = 30

Do
numdates = numdates + 1

value = ""
Do
value = Application.InputBox("Date for stock info", "Date", value)
flag = IsDate(value)
If flag = False Then MsgBox "Invalid date", 16, "Error"
Loop Until flag = True
ActiveSheet.Cells(numdates, 1).value = CVDate(value)

value = ""
Do
value = Application.InputBox("Number of items in stock", "Count", value)
flag = IsNumeric(value)
If flag = False Then MsgBox "Invalid Number", 16, "Error"
Loop Until flag = True
ActiveSheet.Cells(numdates, 2).value = value

Loop While MsgBox("More previous data?", vbYesNo) = vbYes

numdates = numdates + 1

ActiveSheet.Cells(numdates, 1).value = CVDate(CLng(Now))
value = ""
Do
value = Application.InputBox("Number of items currently in stock", "Count", value)
flag = IsNumeric(value)
If flag = False Then MsgBox "Invalid Number", 16, "Error"
Loop Until flag = True
ActiveSheet.Cells(numdates, 2).value = value
currentstock = value

ActiveSheet.Cells(numdates + 1, 1).value = ActiveSheet.Cells(numdates, 1).value + 1
ActiveSheet.Cells(1, 3).Formula = "=TREND(B1:B" & numdates & ",A1:A" & numdates & ",A" & numdates & ")"
ActiveSheet.Cells(2, 3).Formula = "=TREND(B1:B" & numdates & ",A1:A" & numdates & ",A" & numdates + 1 & ")"

eachday = ActiveSheet.Cells(1, 3).value - ActiveSheet.Cells(2, 3).value

daysleft = currentstock / eachday

MsgBox "We will run out of stock in " & CLng(daysleft) & " days"

0
 
LVL 22

Author Comment

by:Adam Leinss
ID: 1426332
Hi y96andha!  I appreciate the time you took to help me, you don't know how much it means to me!  I just have one question.  When I ran the code, I got "Run-time error 6,
Overflow".  When I clicked Debug, it went to the line:
"ActiveSheet.Cells(numdates,1).value = CVDate(value)".  Do you have any idea what the problem could be and any possible solution to it???

Thank you!
Adam Leinss


0
 
LVL 5

Expert Comment

by:y96andha
ID: 1426333
That is strange. I did not get the same error, I've never seen it. I just installed Excel for 95 and pasted all the code into a module and added sub Main and end sub at the beginning and end, and it worked immediately.

When you get the error, could you please use ?value in the debug window to get the contents of value and post it here? In what format did you enter the date? I have used both "1997-05-16" and "16/5/97", and both worked OK.
0
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 
LVL 22

Author Comment

by:Adam Leinss
ID: 1426334
I entered the date as "12/12/98" and when I typed ?value in the debug box, it gave me "12/12/98"?  I will delete and re-install Excel and see if that helps. If it works on your Excel 95 version then it has to work on mine! :)

Thanks,
Adam
0
 
LVL 22

Author Comment

by:Adam Leinss
ID: 1426335
Well, I tried to re-install Excel 95 and I still get the same answer.  When I try ?CVdate(value), I get the overflow error again?  Maybe its something with my computer, I will try it at the school's computer lab on tuesday and see if it works there.

Thanks,
Adam
0
 
LVL 5

Expert Comment

by:y96andha
ID: 1426336
That is very strange indeed.

I just tested, this is copied directly from my debug window in Excel 95:

?cvdate("12/12/98")
1998-12-12
value="12/12/98"
?value
12/12/98
?cvdate(value)
1998-12-12

Could you try some other combinations, like ?cvdate("1998-12-12"), ?now and ?cvdate(now)?

0
 
LVL 22

Author Comment

by:Adam Leinss
ID: 1426337
These are the first things I tried from the Debug Window

?cvdate("12/12/98")
12/12/98

value="12/12/98"
?value
12/12/98

?cvdate(value)
[after I do the last one {?cvdate(value)}, I get a overflow error]

Second batch:
?cvdate("1998-12-12")
12/12/98

?now
5/17/97 11:15:44 PM
?cvdate(now)
[after I do the last one {?cvdate(now)}, I get a overflow error]



0
 
LVL 5

Expert Comment

by:y96andha
ID: 1426338
Hmm.. Try using CDate instead of CVDate, see if that helps.
I guess it must have something to do with the country settings. I live in Sweden, and use Swedish Excel and Windows NT, so my standard date format is 1997-05-17.


0
 
LVL 22

Author Comment

by:Adam Leinss
ID: 1426339
Ok, this will be the last time I bother you! :) I took the code to school and it worked!? Go figure, my Excel 95 must be corrupted in some way.  Any ways, now that the coding actually works, I have some questions on exactly how it works.

The first set of questions, is the programming asking how many are sold per day or how many you received of that item on that day?

For the second set of questions, is it asking how much stock you have left over or the total stock you had to begin with?  When it entered 12/1/98 and enter, lets say, 25, and answer no, it gives a date of 5/21/98.  Over what period of time is the program basing it calculations?  A month?  Six months?

Thanks for all your help!

Adam
0
 
LVL 5

Expert Comment

by:y96andha
ID: 1426340
The example only demonstrates how to fit a line to a number of samples. It will only work to calculate the average consumption during a period of time.

You enter the number of items in stock at certain dates in the past, and then you enter the number currently in stock. The program will calculate the average daily consumption using the TREND function, and then display the number of days left until all of the stock has been used up at that rate.

I do not really know how much mathematics you know. It would be easier to discuss in E-mails, could you mail me at andreas.hansson@mbox303.swipnet.se?





0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses
Course of the Month5 days, 3 hours left to enroll

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question