PDA

View Full Version : VBA Search form



geronimo
10-11-2019, 05:00 AM
Hi all,

I have a very large table (100k rows) which contain the bibliography of some romanian authors.

The table has only two columns
Column 1 - contains author name
Column 2 - contain all the authors works, each of their works on a separate row. Besides the work's name, the cell contains also some more data as: editor name; year of publishing.

I am looking to make a search form with the following search fields
Search Field 1 - Author name - it will search only in Column 1
Search Field 2 - Year - here i need to set up an interval (let's say 1880-1885) - it will search in Column 2
Search Field 3 - other keywords - it will search in Column 2

The search should be performed as follows
1. not all of the Search fields must be filled => Search will display the matching results only for the filled fields
2. Search field 2 - year interval - this will search in Column 2 for each of the years in the interval (ex. if interval is 1880-1883, it will search for 1880,1881,1882,1883)
3. Search result will also display corresponding data from both columns of the sheet.

So, to summarize, a few search examples:

Example 1 - i need to search for all the works of Author X from year 1900 - i put the author name (X) in Search Field 1 and i set the year interval 1900-1900
Example 2 - i need to search all the works that were published by Editor Y between the years 1900-1903 - i leave author Search Field (1) empty, i set a the year interval 1900-1903, and i fill the editor name in Search Field 3 => so in this case the search will basically look only in column 2 of the sheet and will display only the rows in which we can find the keyword form search Field 3 (in this case editor name Y) AND one of the years from the interval ( 1900,1901,1902,1903)


I never used VBA before and till this project cam up i never tried any VBA programming...so please be gentle with a complete beginner as i am :)

Can anyone help me do this, i will provide very fast all needed details if there are things to clarify

Thank you all in advance for any input and guidance in solving the problem

Cheers!

SamT
10-11-2019, 12:48 PM
I never used VBA before and till this project cam up i never tried any VBA programming...so please be gentle with a complete beginner as i am

Instead of thinking that this project consists of an Excel Sheet, a User Form and some Search code, you need to think of it as a single integrated App. That means that you need to consider the Data, (the stuff on the Excel sheet,) then the Search code. Only then can you think about the layout of the data on the Excel sheet. After you have optimized the Sheet for searching, then you can think about the User Form(s).

Consider the Data and the Search code: Searching a Data Base, Excel sheet Table, Excel sheet List, (consider all three as the same thing at this time,) may be very difficult or even impossible unless all Data "types," or "categories," is in a different column or DB Field from all other categories. An Excel Column should always be considered as a Data Base Field.

All these are different "types" of Data: Author full name, Author Surname, Author Given Name, Author Alias F.N., Author Alias S.N.,Author Alias G.N., Etc, Publisher, First Publish Date , Last Publish Date, Last Publisher, Etc, Author's Birthplace, Etc, Etc, Etc, They represent different Columns or DB Fields, not necessarily on the same sheet or in the same DB Table.

All these are different "Categories" of Data: Authors, Publishers, Titles, Subjects. They each should be placed on a different Sheet, (DB Table.) This does require an Indices sheet or DB Index Tables. This sheet is merely several lists, one list to a column, (each separated from the others by empty columns.) (Generally, each Index entry refers to a different Table, either in Excel or in a Data Base.) This is where your search will start, (If it doesn't exist here, it doesn't exist any where.) It also makes it relatively easy to add a hints function that helps Users with misspellings, but that feature is for later.

Still thinking ahead... The Authors Table, the Publishers Table, and the Titles Table are where you store all the relevant information about the Item: Birth date, Date of Death, Home town, Nationality, Etc, Etc, Etc. Think a bit about these now, but don't actually put them in place until later.

Something to think about. Data Bases use what is called A "Key" for searching purposes, A Key is merely a unique identifier in tables, an example would be that each of your Indices Tables has two columns. a "Name" Column before an Identifier Column:
Johann Sebastian Smith >> au_000001
Robert Anson Heinlein >> au_000002
Anson MacDonald >> au_000002_01
Lyle Monroe >> au_000002_02
(au_000002_01 and au_000002_02 are Pen names for au_000002. I chose this format of Keys to make it easy to search for all of the Author's works under any name; Titles are Keyed only with the Authors Real Name Key. YMMV)

One Flew Over The CooCoo's Nest >> ti_000001
The Principles of Mathematics >> ti_000002

HomeTown Publishers >> pu_0001
OtherTown Publishing >> pu_0002

4 digits allows 9999 Unique Items, 6 digits allow for 1 million Items. YMMV

While it is more work now to include Keys in Excel, at 100K entries you are starting to push the limits of what is handy to perform in Excel, and you should be considering that time when you must move to a Data Base solution.

Enough about the future, let us talk about right now:
First, some design notes: In the Tables Headers, there should not be any spaces in the Header names: Either compress all the words into CamelCase words; AuthorName or replace spaces with Underscores, Author_Name. Be consistent across the project, do not use both CamelCase and Underscores in the same Project. These forms will be used in both Excel and your code, as well as in any Data Base you might implement in the future.

Before you start: On the VBA Editor menu >> Options >> Editor Tab, Check all boxes in the Code Settings Frame


The table has only two columns
Column 1 - contains author name
Column 2 - contain all the authors works, each of their works on a separate row. Besides the work's name, the cell contains also some more data as: editor name; year of publishing.

Column 2, as is, is a Project killer. Your first lessons in VBA will be to write one-use code bits to correct this so that Column 2 is separated into many columns as mentioned in the first part of this post. The first step to this end is to set up your files properly.
Create a folder somewhere named "Bibliography Project" or something you will remember well. Move the Project Workbook to this folder. Create a folder inside/under this folder named "Archive". Open the Project in Excel and Save a Copy of it named like Original Name + orig.xlsm

Never again modify the original file in Archive! Always use and modify the copy in the "Bibliography Project" folder!

If you use the "Go Advanced button below the VBA Express post editor, it will open the Advanced Editorm wherein you can copy a section of an Excel sheet and paste it directly into you post. copy the most complicated cell in Column 2 and post it to us so we can assist you in cleaning up and separating all the data in column 2 into separate columns.


While coding, check that the top of the Code Page has "Option Explicit"

Open the Working version of your Project and Paste this code into the ThisWorkbook Code Page/Code Module
Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If Not Me.Saved Then Me.Save
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUi As Boolean, Cancel As Boolean)
Dim Nme As Variant
Dim Pth As String

With ThisWorkbook
Nme = Split(.Name, ".", -1)
Pth = .Path
End With

Me.SaveCopyAs (Pth & "\Archive\" & Nme(1) & "- " & CStr(CDbl(Now)) & Nme(2))

End Sub


Take the time to analyze and thoroughly understand this code before proceeding.

After we get your Tables optimized for searching, then we can start work on your User Form(s) and the Search Code