VBA in excel : making a Stock list

Hello,

i will try to explain as best as i can what the issue is, feel free and don't hesitate to ask questions if needed.

I am using excel to make a new version for a stock list, it involves flat sheets of metal such as steel, stainless steel, aluminium, zinc steel etc....
By using visual basic we managed to make the excel more functional, user friendly and a bit faster, but we have some errors we can't solve.

here is how the excel should work:

On top (range B2:T6) these are the materials and thickness of all materials that can be found in the entire excel vertically.
By clicking on a material it shows the material chosen and hides all the rest. When clicking on a thickness you get all results with that thickness on all materials in the excel.
you can combine the 2, press material and then a thickness and you get youre defined result within 2 clicks.

This said, you probably already figured the excel is slowing down as it is scanning the entire index of row 9 to 5000.
there are a few bugs in this excel, there is a small bug history written in range AC1:AK8.
the excel is doing all sorts of this when protection mode is turned on, when sharing mode is turned on, and a few more stuff.

The buttons REST and DIV in range M4:N4 are restplates and diverse plates, but these buttons don't filter as they should, you can't combine REST with a material (it is resetting)

Another buggy thing is the function resetposition
Function resetPosition()
ActiveWindow.ScrollRow = 9
End Function

example : you are looking for material DC01 in thickness 1
click on DC01, scroll down below thickness 1, then click thickness 1 in cell F5
the active scrolrow does not rest to 9

This is a hell of an explanation on a new topic i hope it isn't too much :-)
question would be, can these bugs be solved, and is there advice or idea's?

thank you very much

kind regards
Anthony

I have done something similar in the past, but I used a VBA Userform for the User Interface.
The data structure I used was all materials on one sheet with the main categories listed in Cell A2 and down.

In Row 1 from C1 Across to the Right. I had Headers identical to the list in Column A
Immediately below each header was a list of sub categories.

Below that sub list were "Titled" lists of items in each sub category. The "Titles" were identical to the list of Sub categories below each Header.

This provides three layers of lists, Main, Header and Category. (you can carry on to yet another sub-sub-category layer in the same manner.) The important detail for the code in the UserForm is that every List and it's Header/Title be surrounded by empty cells. Since I included prices in the most atomic list, each "Header: used three columns, 1 for the lists, 1 for the prices, and 1 as a separator.

The UserForm used dependent Comboboxes to display the lists. Only the Primary ListBox had a hard coded List source (Range("A1")). The code for the list source for all Controls was always (Examples only, not code)
ListArray = Rows(1).Find(Primary.Value).CurrentRegion.Value
AND
ListArray= Columns(PreviousHeaderColumn).Find(Value).CurrentRegion.Value

This Data structure and code was fast enough, that even with 3 List Sheets, up to 15 Main List Items (Categories/headers) and hundreds of sub lists and Items per Category (Header), The UserForm response seemed instantaneous to the User. This structure also makes it easy to maintain the lists and even add more Categories and Items later. If you use inventory control, this can be done programmatically so that Odd lengths can be available to the End Users. (Something to Think about later)



I would suggest that your Main List and Headers be of Material Types, the Secondary Lists be of Stock Type, "Flat", "Bar", "Tubing", etc and the tertiary Lists be of Thickness and Dimensions. It would be easy to set the default selections to "Flat", while still allowing the selection of other shapes

Quote:

---

Originally Posted by AnthonyV

I am using excel to make a new version for a stock list, it involves flat sheets of metal such as steel, stainless steel, aluminium, zinc steel etc....

---

It's the word new that caught my eye. I think you've got the chance to make things a lot easier for yourself, now rather than later.
What you're finding is similar to what many users find, that Excel is powerful, can do a lot of things, especially with vba automation, and can produce good looking, human-readable results. You have a stock list, so what better than to pretty it up, add some automation, make it really easy to move arround and find stuff out… only now you're finding it a bit more difficult than you expect and tying yourself up in knots. The main reason behind this is that you're trying to make your actual database (stocklist) human friendly and that that stocklist and the presentation to humans is one and the same.
[I once worked for a major worldwide telco whose projects database was in such a format, and I've done the same myself, so you're far from being alone.]

It would help you a lot to separate these aspects from each other. Your database could be completely invisible to humans, whilst you present the data it holds elsewhere. That way the database side of things can be a lot cleaner. You could go the whole hog and put your data in a database management system such as Access, but I suspect you might be unwilling to do that(?).
Instead, I'll show you some aspects of Excel that should make things a lot easier.
Just one rider: I'm no expert on databases! There's a lot to them and I don't pretend to know it.

I'll start by mentioning a few 'rules' regarding databases. I'll keep things straightforward by not having multiple tables linking to each other and just stick to a flat-file type database which is easy enough to produce from your existing workbook, but means there's a lot of repeated data.
I've created that sort of flat-file table on sheet Stock (2), I've had to make several guesses about your data layout and I may have messed it up a bit, so don't use it as a basis for anything in real life - it's there to show ideas, that's all.

First rule I'll mention is to make sure that each row in the table (a record) has all the information you want to hold about that item, and that all the information on that row is related to that item. This is at odds with what you have in your database: pairs of rows, related to each other, and only because they're next to each other. Column G has this where you're holding stock numbers in one row, and a date in the row below; great for humans, not so great for coding - imagine accidentally sorting your stock list - all those dates would no longer be related to the row above.
This links to another guideline, being that each column (field) contains only one type of datum; type both in the sense that they're all numbers, all text, all dates, all currency, and nothing else, and type in the sense that it relates to the same attribute (aspect) of the entity that the row represents (you shouldn't use the column (field) to hold dates which might be a delivery date or an order date, instead you'd have a separate column for each of those). If you want to store other data, store it in another field (column) on the same row as the rest of the data. I've done that with your stock list.
I think you've done something similar in column A: you've got the likes of Perfo, Exotic, Zincor around rows 4200 which seem to be headers at the top of a group of materials, if this is a group then this grouping can be in its own field; I've done that a bit with your stock list too (on Stock (2)) Type and SubType.

Next is field names (column headers), these should be unique, so I've tweaked them a bit where you've got repeating column headers shaded different colours. Each column should have something (not left blank) as a header too! I don't know what to make of column F (dates mostly, but other stuff too) so I've called it HdrE (don't ask!).

Next, you've got some calculations in columns S and T, often sums within a grouping, sometimes sums across a single row, sometimes with hard coded multipliers from where I have no idea; you've got thousands in there and they're going to be difficult to maintain - how long would it take you to verify that all those formulae were looking at the right ranges?. I've left them in the table in sheet Stock (2) but really we should let the presentation side of things look after that.

The next thing that's quite good about such a table is that the order it's sorted in shouldn't really matter; if you add a new row of data at the bottom, it can stay there. You can sort it if you want, but it shouldn't make any difference elsewhere.

This next bit's not something I'd recommend, because again it's using the database itself to display stuff (reports if you like) to humans, but I include it to show how this:

Quote:

---

Originally Posted by AnthonyV

On top (range B2:T6) these are the materials and thickness of all materials that can be found in the entire excel vertically.
By clicking on a material it shows the material chosen and hides all the rest. When clicking on a thickness you get all results with that thickness on all materials in the excel.
you can combine the 2, press material and then a thickness and you get youre defined result within 2 clicks.

---

is 98% achievable without a single line of code, using Excel's built-in functionality.

So at the top of sheet Stock (2), you have a slicer (a box containing buttons showing all your materials) similar to your stock list. There's another slicer showing all the Zs, (thickness?), like yours. A slicer is just a way of easily picking how you want a column to be filtered. You can click on a single material, or hold the Shift key down and select several (contiguous) ones, or hold down the Ctrl key and click on multiple (non-contiguous) ones. You can clear the filter by clicking on the Attachment 26268 icon at the top right corner of a slicer.

So starting off with a completely unfiltered table (ensure this by clicking anywhere in the table, then in the ribbon goto the Data tab, and click the similar Clear icon:
Attachment 26269
Now click on 304 kgw button and note what happens to the Z slicer buttons' appearance; buttons which if clicked would show no results are sort of greyed out, but better, those which will result in visible rows are all at the top, what's more, in order.

Excel is doing this fast, faster probably than you or I could code for. Play with it.

Quote:

---

Originally Posted by AnthonyV

excel is doing all sorts of this when protection mode is turned on, when sharing mode is turned on, and a few more stuff.

---

I suspect many of these things will no longer be a problem.

Quote:

---

Originally Posted by AnthonyV

The buttons REST and DIV in range M4:N4 are restplates and diverse plates, but these buttons don't filter as they should, you can't combine REST with a material (it is resetting)

---

I've added a couple more slicers which filter for columns I've added Type, and SubType. I've probably got this all wrong, but this problem should no longer arise, because these attributes are in separate columns they can be filtered independently.

An aside:

Quote:

---

Originally Posted by AnthonyV

Another buggy thing is the function resetposition
Function resetPosition()
ActiveWindow.ScrollRow = 9
End Function

example : you are looking for material DC01 in thickness 1
click on DC01, scroll down below thickness 1, then click thickness 1 in cell F5
the active scrolrow does not rest to 9

---

When I follow the route taken by the code when you click the 1 in cell F5, the resetPosition code is never called. If you put it somewhere towards the end of the code that's executed when you click cell F5 it seems to work as you want.


Coming back to my principal point, there are loads of ways of presenting the information in such a database table without seeing the database itself; queries, with formulas, and quite powerfully and easily via a pivot table - which I've done a bit messily, on Sheet2. This is where you can let the presentation side of things do your calculations, subtotals, counts etc for you - robustly (and quickly). A major advantage is that you can have multiple reports based on the same data, and show only that part of the data you need to see in each report, so your reports are not cluttered with lots of columns of data that are irrelevant/distracting to that report.

@p45cal -- good writeup, esp the part about making it Excel-friendly

https://www.ecanarys.com/Blogs/Artic...d-Architecture

Attachment 26271

wow big replies on the topic! thanks for all effort everyone! :-) i much appreciate this!

I have to say ive never done slicers, in fact the new version of this excel is my idea, i took care of the idea's and the layout, a colleague wrote the Visual Basic part.

i didn't tell about where we came from and what our intentions are with a new stock list version. maybe not something everyone would read but it does give an image why we sailed into this problem :-)

the main idea to use buttons like 'DC01' and a thickness was to get ride of those darn small filter buttons that are like 1x1 square millimeter on my screen.
we are using a very basic excel, just rows and collums and tabs to keep track of our internal stock. but it's so outdated and takes precision to aim clicking, enless scrolling, i wanted to get rid of those annoyments and add some quality of life features.

the most important things were:
-to have some bigger buttons which are easy and fast to aim at.
-instead of scrolling your finger cripple, there should be a result within 2 fast clicks.
-add colors according to our internal color coding system, for representation, and it makes it more easy to see/search what we are looking for when working in the excel. (we also print out lables to nail on the packaging, see attach)
-there would be an general update in formula's for new sizes
-add more, and link stuff like european EN codes for quality and dimensions to the labels we have.
-etc....

a general upgrade you might say

i am a cnc programmer for laser, punching, bending and welding machines, besides that also 3d modeling in Inventor.
the excel is not for representation, it's only for just a few people to keep track of our sheets stock

what i didn't include in that excel is another tab that the purchase department is using, we keep a 5 year history in that tab, and use it to process material usage and buy new materials.
that tab also needed an update but we already got stuck in the stock tab :-)
the purchase tab has the classic tiny filter buttons, which we hate, but it does the job for anyone that uses magnifying glasses, and a lot of patience as the filter is buggy and click not twice but sometime 3 times before excel responds.

we also have a ERP system, but it's simple and the stock function is outdated, there is no overview what-so-ever. and we need that overview to be able to work quickly.
so we don't use the stock system in the ERP, at least not in 2020.

what i did do in the ERP and the software we use to create programs for the machines, is that i wrote a whole new database for materials so it would be more easy to link them in the future as they now all have the same uniform ID, and added EN codes. it was just a logical step to upgrade the excel for stock also...

anyway i'd like to thank all effort from everyone, i'll get this info to my colleague as soon as i see him. the excel that p45cal made looks very promissing and we could use it as a base.
SamT thanks for your advice, my colleague will read VB as i can't :-)
we will check into it and return to the forum whenever we get this chance with this Covid-19 issue :-/



see you later and be save you all !!

kind Regards,
Anthony


Attachment 26304

Read all three answers to your original query.

In re, Paul's Data Tier:

Quote:

---

we are using a very basic excel, just rows and columns and tabs to keep track of our internal stock

---

.That pefectly fits. It also meets both P45cal's and mine answers.

We all three also discussed the Presentation Tier. To Expand on Paul a bit, this include Reports, things to be viewed by people, things to be Printed, and things to be saved. The Presentation Tier also includes UserForms as a GUI for people to use to create the previously mentioned "Things," and for Data Entry and maintenance, (Editing and deleting as needed)

All thing Programming proceeds from Data, and Reports are a form of data.
Excel Data Bases/Data Tables, should always be unformatted. The only recommended formatting on a Data sheet is to change the Header's Font (Name or Bold) and to bottom Border the headers. The Many-Small-Table format I mentioned in my first post was designed to make it easy for Building contractors to make their own DB, while making it possible for me to code for unknown Data. I doubt that it would be the best style for you, I think a standard Flat DB is what you should use. You might want to include what I then called Categories into your DB as Fields.

A Small language primer:
DB table: XL Sheet (except LookUp Table Sheets With Many Index Tables)
DB LookUp or Index Table: A Primary Key to Primary Key cross reference between Tables
DB Table name: Xl Sheet Tab Name or CodeName (Multi-Column Header on a Many-Table sheet)
DB Field Name: Xl Header
DB Record: XL Row
DB Field: XL one Cell in a Row
DB Primary Key: XL column A (First Table Column on a Many-Table sheet) An Item specific identifier
DB RecordSet: XL Table Row
Data Point: A Single field

Index Tables are handy to cross reference Part IDs to Supplier IDs When you use a separate Table for Suppliers.

The first step I would do is

Quote:

---

is that i wrote a whole new database for materials so it would be more easy to link them in the future as they now all have the same uniform ID, and added EN codes. it was just a logical step to upgrade the excel for stock also...

---

Make sure that table is Flattened and without formatting.


The next step would be to create blank Templates for every Report, thing to be viewed by people, thing to be Printed, and thing to be saved. Compare all Data points in all Templates to the Fields/Headers in the Data Tables/Sheets.

Only after that would I begin to work on the final phase of the Presentation Tier: The UserForms that all Users would use to interact with the Data and the rest of the Presentation Tier.



One place where my own style of programming differs from the usual is that I keep all Middle Tier code that relates to Data Puts and Gets in the Code module of the Data Sheet. I find this more programmatically logical than the usual style of using Standard Modules for this specific purpose. Example, assuming a Control(s) on some of the UserForms need a list of Thicknesses of a specific Material:

Code:

---

Public Property Get Thicknesses(Material As String) as Variant
    Thicknesses = CustomFunction(Material)
End Property

Private Function CustomFunction() As Variant
  'Well developed Code to return an array of all available thicknesses of an inputted Material from the table on this sheet
  CustomFunction = that array
End Function

---

That Class Property Get will return an array of Thicknesses to whatever code requests it via

Code:

---

X = TableName.Thicknesses(Material)

---

that code in that Sheet can be directly copied to any other Sheet (table) without editing and can be used by any code by merely changing the table name (Tab name or CodeName) used in the request.