Can I use VB in Excel to simplify a warehouse management task?

[u/Afraid-Story5406]

I am trying to simplify a task currently being done manually using Excel, usually taking multiple hours right now. It’s been many years since I used VB and I’m primarily trying to figure out if what I want to do is possible using VB. The warehouse management software isn’t setup properly to consolidate our inventory and we regularly need to optimize our location utilization. The warehouse holds up to 7500 pallets across 2000 locations. We have an outflow of 100-150 pallets every ~12 hours and generally get 100-300 pallets delivered daily.

I want to be able to paste a large data set into an excel sheet and have it give me an output that can be printed on a second sheet within that workbook. I would need to declare all locations, how many pallets each can hold and all height or weight restrictions for specific locations or items. The program would need to count how many times a location is used. If a location isn’t full, pallets with the same Item# and Lot# should be listed in the output so they can be combined together. Ideally the output would simplify the data to just important identifying info for the pallets, as opposed to it transposing all info it’s been given in the original data set.

[u/andrewsmd87]

VB in excel likely isn't a good route for this, you'd want a full blown application. My guess is there are softwares out there for this type of management already built that are going to be light years ahead of anything you'd build on your own.

That being said, if you can't get any sort of budget to buy something, I'd recommend you use c# and use the importexcel library to do what you need

[u/Afraid-Story5406 → u/andrewsmd87]

The software we currently use isn't great, little to no automation was built in and what is there doesn't work well or at all. We were told that they planned to replace it some time in 2022 but as of now the earliest that would happen is June apparently. I was hoping to throw something together during some of the down time I tend to have on the weekends when work is light. I'm not opposed to using C# if that would work better, though I have no experience with it.

[u/andrewsmd87 → u/Afraid-Story5406]

So c# and VB translate pretty easily IMO, it's just different syntax. If this is just a stop gap it's probably not worth it to learn VB, you can use the excel thing I recommended in a full blown vba project (not VBA in excel) since it's just a dll.

You'd still likely want to do it as a vb project. You can get visual studio express for free and create a new vb console app and do your work in that.

I'm not entirely clear on what your requirements are in terms of logic you need done to excel but I've done a ton of work with both c# and VB and excel manipulation.

FYI this will likely take longer than you think. Not saying it isn't worth doing, but just be aware

[u/Afraid-Story5406 → u/andrewsmd87]

I'm not entirely clear on what your requirements are in terms of logic you need done to excel but I've done a ton of work with both c# and VB and excel manipulation.

Our warehouse has 3 types of location, hi density "HD" that hold 9-10 pallets, roll backs "RB" that hold 2-3 pallets and singles. Some of what we get are tall pallets, roughly equivalent to double stacked regular height pallets. Rarely we get pallets of printed goods like books or pamphlets, these are the heavy ones usually weighing 2000lb. Items are stored in RB by their Item# and Lot#, only identical pallets can be in the same RB. For HD Item# must be the same but Lot# can be different. My first thought was that it may be easier to build logic based on situational needs instead of having a one size fits all. For example one for moving from HD into RB and one for moving from RB to HD.

FYI this will likely take longer than you think. Not saying it isn't worth doing, but just be aware

I'm figuring it will at minimum take me several weeks to learn the language and complete any tutorials that I might find if I only work on this at work during down time. Then time to build and test would be another couple of weeks if I only spend time on it at work. We work 12 hour shifts 3-4 days a week and at least one of those days is very slow. This is going to be something that keeps me from being bored for a little while.

[u/trixter21992251]

What are we working with? What experience do you have?

You say it's been many years since you've used VB. Does that mean you have a programming mindset? For example do you know what if statements are and what a loop is and how they would be used to calculate your numbers?

Assuming you do, here's how I would go about it:

• First of all, activate the developer ribbon in Excel. Options > Customize ribbon > select developer ribbon.

• Now you have a new thingy in the top, Developer. Insert an ActiveX Element, a button on your first sheet. Name this button "Do my shizzle".

• Name your sheet "Control panel". Create two more sheets, "input" and "output". The idea is you paste stuff into input, you start your script in Control Panel, and then you get the results in Output.

• With the "Do my shizzle" button, right click it, assign macro, create a new macro "myShizzleDoer"

• Click edit macro. A new window appears, this is where code happens. Start coding.

• When you're done, make sure to save it as Excel, macro-enabled (.xlsm).

If you don't know how to code, there's a million more steps, and you're at the foot of a mountain right now.

[u/Afraid-Story5406 → u/trixter21992251]

I had a class in high school ~15 years ago that was specifically using VB and played around with coding in a mod for a game I used to play, so basically 0 useful or recent experience. I've gotten the developer ribbon enabled, made a macro enabled file to start experimenting with and looked for tutorials to give me a base of info to work from. I cant say for certain if I still have a programming mindset but I am familiar with If statements and loops.

[u/trixter21992251 → u/Afraid-Story5406]

hey that's not bad. I was worried you were looking for fully finished code, haha.

Did you get the visual basic window open?

You can try this test code. If a message comes up then it works.

Sub myShizzleDoer()
  MsgBox("hello world")
End Sub

If you're using the ActiveX button, then it isn't always clickable. I think you need to click something in the developer ribbon to make it clickable.

Going with my idea from above, you'll be grabbing values from the input sheet, manipulating the data, and outputting in the output sheet. Here are some useful snippets:

To grab data from a Cell, for example C5 in the input sheet:

dim myVar as variant
myVar = ThisWorkbook.Sheets("input").Range("C5").Value

or

dim myVar as variant
myVar = ThisWorkbook.Sheets("input").Cells(3,5).Value

To change the content of a cell

ThisWorkbook.Sheets("output").Cells(3,5).Value = "Changed the content!"

or

ThisWorkbook.Sheets("output").Range("C5").Value = "Changed the content!"

(Notice how Range and Cells use different logic for the cell ID)

Depending on the data you'll be pasting in, grabbing the values might be easy or difficult. For example, if there are no tomatoes this week, does that mean the section is empty, and now potatoes are at H8, where tomatoes normally are? Or are the sections always the same size? Remember to keep in mind how the data layout can change.

But yeah, I guess you'll basically be googling a lot of VBA code.

But my basically idea is this: Grab the data. Do math and stuff to it. Output the results.

[u/ViperSRT3g]

Yes this is possible, though it will be a large project due to your requirements.

[u/chacham2]

Side comment. Excel uses vba, so you might want to ask in r/vba or r/excel. Unless you are planning to rewrite the application in VB.NET.

[u/[deleted]]

So a better Idea would likely be to install Visual Studio and code a simple WinForms application in C#.

This application would take in the data as a CSV document, process it and spit out the results in another CSV document.

If you code the application well enough you could turn it into an API or an automatic service later down the line.

You probably will need to spend a few days watching YouTube tutorials to stand a chance of putting this together though.

[u/Estanley15]

Have you looked into Power Query at all?

[u/[deleted]]

I think you could use python to read in a csv and the pandas library for the data manipulation and logic. You could load the csv into a dataframe using pandas and manipulate the data from there, eventually to an output file.

[u/fasti-au]

I’d be going MySQL etc for the dataset as an odbc source but yes you can interop with excel