Updateable multi-user macro
I can not program, but I love very much!
A slightly modified quote by artist Vasi Lozhkin.
Article I want to share the experience of raising a multi-user system on VBA Excel.
At the time of the decision to create a flexible application, there were about 7 macros working on large volumes (several files from 20 thousand lines to 370 thousand lines), weighing from 50 kilobytes to 12 megabytes, each of which was written in accordance with the knowledge existing at the time of writing. Each macro was changed, added, corrected in terms of errors, and considering that these macros were used by more than 60 people, not all of whom were tracking the changes, they constantly tugged at me showing another error, which I had already corrected and sent to everyone. To explain to the crowd how to properly use macros, I quit right away, because someone will not hear, someone will not understand what it is about, someone will take an already formatted table to work with macros, but I cannot predict who changes the tables.
It was necessary to make one code, and not 60 copies of each change sent by mail.
The solution quickly occurred to me, and Google quickly gave me the results of a programmatic change to the VBA code, which was later rolled back into practice. So, the text without pictures is not interesting, here is the first, this is the structure of the application, which looks like this:
The user opening the file, activating the excel event "opening a book" performs the procedure in the file "client". The procedure creates a menu by reading the files on the network. By clicking on the button of the desired macro, an action is taken to create a module in the client from a file located on the network, performs actions and destroys the procedure within itself.
')
What we get:
- file for any macro is always one.
- by clicking on the execution of a macro, the user will always use the latest code, which simplifies the support of macros.
- the ability to write a log file for errors, indicating the user, in case you need to beat the one who spoiled everything.
Further there will be a lot of copy-paste on the code changed for my tasks, I would gladly point out the authors, but honestly I don’t know, I took a lot of code from the bourgeois resources, something from the Russian, something invented by me, it was all accumulated for at least six months what in advance is a great litter before primary sources, if anything, write, delete.
So, the practical part.
We hang the event on the opening of the book:
The code is not tricky, the concept is one, give a kick for further action
Procedure create_module_for_file:
Three lines of three actions, in the beginning we need to pick up the file with the module. The following function is responsible for this:
Next, create a module:
And write to the module code from the file
Having received files, we load the menu.
When loading, a menu is created that fits into add-ons.
By code:
- to start, delete, in case there will be an update:
The panel has been created, now we are creating the menu directly, which will create buttons directly for each file with macros.
After the menu is created, buttons are added. The menu was built on the principle file -> one button. In turn, one file with a macro can have several procedures that need to be called, and you can enter them in the sub menu. This code creates 3 buttons:
The result is about this:
In writing mode, changed the concept of work. The task was to use the latest version of the code with each click "run macro". Considering these circumstances, certain files will be downloaded to the client, let's call them index files, that is, modules that are constantly present after opening the file, these are “menu” and “list action”, and the macro files themselves will be loaded by pressing a button. What is their meaning: the menu file forms the menu :)
In the menu we see an event referring to the procedure “action1 ... x”, which sits in the second file “list_action”. Let's look at its contents.
What we do, firstly, we need to remove the module in order to load and use it, whether the code is changed. Next we open the file, create a module and write the code from the file into it.
After these manipulations, we need to call the procedure that performs the manipulations, which are implied by clicking on the button to perform the procedure.
It is interesting here)
If we specify the name of the procedure that is directly in the code above, we have the action11 procedure that curses the fact that it cannot find the “kart_view” procedure, and this is correct, because in fact there is no procedure at the moment when the procedure button is pressed, a function is created, which in turn calls this procedure, the code is:
That is, at the time of calling "kart_view" from "action11a", this procedure will already be loaded into the module, vba does not see the catch.
The resulting result: click on the button, the code is loaded into the module, executed. In case of repeated execution, the module is deleted, reloaded, executed. When you close the file, the book is cleared of everything, so as not to take up space and lie quietly until the next discovery.
Just a couple of words on processing large files, I think it will be interesting. As mentioned, files are used in large volumes, more than 300 thousand lines, from which it is necessary to select, as a rule, 10-30 thousand and work with them.
If I had been asked how to make a sample from half a year ago, I would have been unaware of the conditional cycle. Later, many methods were tried, I will describe each of them for info, except for those that can be implemented using standard excel functions.
Initial data: there is a table of 300 thousand lines, we know 10 thousand values ​​that we need.
1) At first I tried to loop through the sample.
What we get, if we do not complete the cycle, it turns out that we need to sort out 300,000 lines 10,000 times to find the result. Working out vba will be about 500 lines per second, total
3000000000/500 = 6000000 seconds, (100000 minutes or 1666.66 hours or 69 days or 9 weeks or ...)
And the result is usually needed here and now, well, or in a couple of minutes a maximum, so there is no right away.
2) The second method I use is working with mysql server
To work with mysql server, which already was, for another project, the mysql ODBC driver was needed, I knew them 2, version 3.51, and version 5.1. The choice fell on 3.51, because the second one, due to discussions of its work on the forums, was not very worked well with encodings, and I wanted a base in utf8
The driver was put to me, and the first bump on which I stumbled is access rights
That is, you need an account to connect from the outside, in our case it will be a vba-macro which used to swear like this:
Having smoked sql manuals, found it
That is, on request above, an account is created for any user with root rights.
Naturally, no one gave me the root of the rights and the project using the mysql server for storing data was “frozen” because in the future it was necessary to write to the database, I had only reading.
For performance evaluation: the same macro using iterations using cycles worked for 59 seconds, with sql we managed to do the same for 3.5-4 seconds, that is, we really managed to accelerate more than 10 times.
By code, a good article is here: egregors.blogspot.ru/2013/05/mysql-vba-excel-mysql.html
3) Continuing to smoke the sql theme, began work on working with access.
The code turns out to be hellish, but working.
This method works slower than mysql, for some reason, but it is quite acceptable for use. But not me ...
I refused it the very first time when I needed a system in which the user creates a database in a certain folder and writes there 2 tables of 300 thousand lines each, for comparing and calculating them. With the tests, I got a base of about 600 meters in size, given that in the future 50 people can use this macro at the same time, it turns out a folder of about 25-30 gigs in size, for which admins would quarter me right away.
4) Actually the way I stopped. Yes, it works slower than the second and third methods, but these disadvantages are compensated by the functionality. Sql book request
Here ... If we take the conditions of the example, a line for the query will gather somewhere for 10 seconds, a query with a sample will hang up for 25 seconds, and a 10 seconds test will depend on the desired result.
Something like this.
A slightly modified quote by artist Vasi Lozhkin.
Article I want to share the experience of raising a multi-user system on VBA Excel.
At the time of the decision to create a flexible application, there were about 7 macros working on large volumes (several files from 20 thousand lines to 370 thousand lines), weighing from 50 kilobytes to 12 megabytes, each of which was written in accordance with the knowledge existing at the time of writing. Each macro was changed, added, corrected in terms of errors, and considering that these macros were used by more than 60 people, not all of whom were tracking the changes, they constantly tugged at me showing another error, which I had already corrected and sent to everyone. To explain to the crowd how to properly use macros, I quit right away, because someone will not hear, someone will not understand what it is about, someone will take an already formatted table to work with macros, but I cannot predict who changes the tables.
It was necessary to make one code, and not 60 copies of each change sent by mail.
The solution quickly occurred to me, and Google quickly gave me the results of a programmatic change to the VBA code, which was later rolled back into practice. So, the text without pictures is not interesting, here is the first, this is the structure of the application, which looks like this:
The user opening the file, activating the excel event "opening a book" performs the procedure in the file "client". The procedure creates a menu by reading the files on the network. By clicking on the button of the desired macro, an action is taken to create a module in the client from a file located on the network, performs actions and destroys the procedure within itself.
')
What we get:
- file for any macro is always one.
- by clicking on the execution of a macro, the user will always use the latest code, which simplifies the support of macros.
- the ability to write a log file for errors, indicating the user, in case you need to beat the one who spoiled everything.
Further there will be a lot of copy-paste on the code changed for my tasks, I would gladly point out the authors, but honestly I don’t know, I took a lot of code from the bourgeois resources, something from the Russian, something invented by me, it was all accumulated for at least six months what in advance is a great litter before primary sources, if anything, write, delete.
So, the practical part.
We hang the event on the opening of the book:
Private Sub Workbook_Open() create_module_for_file End Sub The code is not tricky, the concept is one, give a kick for further action
Procedure create_module_for_file:
filemod = OpenFileModule("menu") cl_d.create_module ("menu") cl_d.write_sub_for_module_action filemod, "menu" filemod = OpenFileModule("list_action") cl_d.create_module ("list_action") cl_d.write_sub_for_module_action filemod, "list_action"
Three lines of three actions, in the beginning we need to pick up the file with the module. The following function is responsible for this:
Function OpenFileModule(namemodule) Set FSO = CreateObject("Scripting.FileSystemObject") filepath = "-// //-" mSearch = ".txt" OpenFileModule = FSO.GetFile(filepath & "\" & namemodule & mSearch) End Function
Next, create a module:
Function create_module(name_module) ' ThisWorkbook.VBProject.VBComponents.Add vbext_ct_StdModule ' k = ThisWorkbook.VBProject.VBComponents.Count ' ThisWorkbook.VBProject.VBComponents.Item(k).Name = name_module End Function
And write to the module code from the file
Function write_sub_for_module_action(filepath, filename) Open filepath For Input As #1 s = "" Do Until EOF(1) Line Input #1, Data s = s & Data & z & z Loop Set vbComp = ThisWorkbook.VBProject.VBComponents(filename) With vbComp.CodeModule .InsertLines .CountOfLines + 1, s End With Set vbComp = Nothing Close #1 End Function
Having received files, we load the menu.
When loading, a menu is created that fits into add-ons.
By code:
- to start, delete, in case there will be an update:
Application.CommandBars(1).Controls("MTS_K").Delete - <source lang=«VBScript»> ' , , MenuPos = Application.CommandBars(1).FindControl(ID:=30010).Index + 1 ' Set Menu = Application.CommandBars(1).Controls.Add(Type:=msoControlPopup, before:=MenuPos, temporary:=True) ' Menu.Caption = "MTS_K"
The panel has been created, now we are creating the menu directly, which will create buttons directly for each file with macros.
' Set Menuname = Menu.Controls.Add(Type:=msoControlPopup) ' Menuname.Caption = " YES->NO"
After the menu is created, buttons are added. The menu was built on the principle file -> one button. In turn, one file with a macro can have several procedures that need to be called, and you can enter them in the sub menu. This code creates 3 buttons:
Set SubItem = MenuItem.Controls.Add(Type:=msoControlButton) ' SubItem.Caption = " " ' , SubItem.FaceId = "801" ' , SubItem.OnAction = "action11a" ' SubItem.Enabled = True ' Set SubItem = MenuItem.Controls.Add(Type:=msoControlButton) SubItem.Caption = " " SubItem.FaceId = "1038" SubItem.OnAction = " action12a" SubItem.Enabled = True Set SubItem = MenuItem.Controls.Add(Type:=msoControlButton) SubItem.Caption = " " SubItem.FaceId = "1038" SubItem.OnAction = " action13a" SubItem.Enabled = True
The result is about this:
In writing mode, changed the concept of work. The task was to use the latest version of the code with each click "run macro". Considering these circumstances, certain files will be downloaded to the client, let's call them index files, that is, modules that are constantly present after opening the file, these are “menu” and “list action”, and the macro files themselves will be loaded by pressing a button. What is their meaning: the menu file forms the menu :)
Set MenuItem = PLEXMenu.Controls.Add(Type:=msoControlPopup) MenuItem.Caption = " " Set SubItem = MenuItem.Controls.Add(Type:=msoControlButton) SubItem.Caption = " " SubItem.FaceId = "801" SubItem.OnAction = "action11" SubItem.Enabled = True
In the menu we see an event referring to the procedure “action1 ... x”, which sits in the second file “list_action”. Let's look at its contents.
Sub action11() Set cl_d = New Edit_module On Error Resume Next cl_d.delete_modul_full ("action_yes_no") filemod = OpenFileModule("action_yes_no") cl_d.create_module ("action_yes_no") cl_d.write_sub_for_module_action filemod, "action_yes_no" kart_view End Sub
What we do, firstly, we need to remove the module in order to load and use it, whether the code is changed. Next we open the file, create a module and write the code from the file into it.
After these manipulations, we need to call the procedure that performs the manipulations, which are implied by clicking on the button to perform the procedure.
It is interesting here)
If we specify the name of the procedure that is directly in the code above, we have the action11 procedure that curses the fact that it cannot find the “kart_view” procedure, and this is correct, because in fact there is no procedure at the moment when the procedure button is pressed, a function is created, which in turn calls this procedure, the code is:
Sub action11() Set cl_d = New Edit_module On Error Resume Next cl_d.delete_modul_full ("action_yes_no") filemod = OpenFileModule("action_yes_no") cl_d.create_module ("action_yes_no") cl_d.write_sub_for_module_action filemod, "action_yes_no" action11a End Sub Function action11a() kart_view End Function
That is, at the time of calling "kart_view" from "action11a", this procedure will already be loaded into the module, vba does not see the catch.
The resulting result: click on the button, the code is loaded into the module, executed. In case of repeated execution, the module is deleted, reloaded, executed. When you close the file, the book is cleared of everything, so as not to take up space and lie quietly until the next discovery.
Just a couple of words on processing large files, I think it will be interesting. As mentioned, files are used in large volumes, more than 300 thousand lines, from which it is necessary to select, as a rule, 10-30 thousand and work with them.
If I had been asked how to make a sample from half a year ago, I would have been unaware of the conditional cycle. Later, many methods were tried, I will describe each of them for info, except for those that can be implemented using standard excel functions.
Initial data: there is a table of 300 thousand lines, we know 10 thousand values ​​that we need.
1) At first I tried to loop through the sample.
tab1 = tab2 = col_tab1 = tab1.Cells(Rows.Count, 1).End(xlUp).Row ' (10 ) col_tab2 = tab2.Cells(Rows.Count, 1).End(xlUp).Row ' (300 ) for i = 1 to col_tab1 for ii = 1 to col_tab2 if tab1.cells(ii,1) = tab2.cells(i,1) then exit for end if next ii next i
What we get, if we do not complete the cycle, it turns out that we need to sort out 300,000 lines 10,000 times to find the result. Working out vba will be about 500 lines per second, total
3000000000/500 = 6000000 seconds, (100000 minutes or 1666.66 hours or 69 days or 9 weeks or ...)
And the result is usually needed here and now, well, or in a couple of minutes a maximum, so there is no right away.
2) The second method I use is working with mysql server
To work with mysql server, which already was, for another project, the mysql ODBC driver was needed, I knew them 2, version 3.51, and version 5.1. The choice fell on 3.51, because the second one, due to discussions of its work on the forums, was not very worked well with encodings, and I wanted a base in utf8
The driver was put to me, and the first bump on which I stumbled is access rights
That is, you need an account to connect from the outside, in our case it will be a vba-macro which used to swear like this:
Having smoked sql manuals, found it
CREATE USER '%'@'user' IDENTIFIED BY PASSWORD 'mtspass'; GRANT SELECT ON *.* TO '%'@'user';
That is, on request above, an account is created for any user with root rights.
Naturally, no one gave me the root of the rights and the project using the mysql server for storing data was “frozen” because in the future it was necessary to write to the database, I had only reading.
For performance evaluation: the same macro using iterations using cycles worked for 59 seconds, with sql we managed to do the same for 3.5-4 seconds, that is, we really managed to accelerate more than 10 times.
By code, a good article is here: egregors.blogspot.ru/2013/05/mysql-vba-excel-mysql.html
3) Continuing to smoke the sql theme, began work on working with access.
The code turns out to be hellish, but working.
Set cN = New ADODB.Connection ' cN.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=-// //-\base\base" & Environ("USERNAME") & ".mdb; Jet OLEDB:Database;" ' ' for i = 1 to col_tab1 nabor_act = nabor_act & “','” & tab1.cells(i,1) next i Set RS = New ADODB.Recordset ' RS.Open "SELECT * FROM kartoteka WHERE nom_act LIKE '” & right(nabor_act, len(nabor_act)-3) & ”' ;", cN, adOpenStatic, adLockOptimistic o = 2 Do While Not RS.EOF ter.Cells(o, 1) = "" & Right$(RS.Fields(1).Value, 6) ter.Cells(o, 2) = RS.Fields(2).Value ter.Cells(o, 3) = "" ter.Cells(o, 4) = RS.Fields(6).Value ter.Cells(o, 5) = "" o = o + 1 RS.MoveNext Loop cN.Close Set cN = Nothing
This method works slower than mysql, for some reason, but it is quite acceptable for use. But not me ...
I refused it the very first time when I needed a system in which the user creates a database in a certain folder and writes there 2 tables of 300 thousand lines each, for comparing and calculating them. With the tests, I got a base of about 600 meters in size, given that in the future 50 people can use this macro at the same time, it turns out a folder of about 25-30 gigs in size, for which admins would quarter me right away.
4) Actually the way I stopped. Yes, it works slower than the second and third methods, but these disadvantages are compensated by the functionality. Sql book request
Set cn = New ADODB.Connection ' cn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ActiveWorkbook.path & "\" & ActiveWorkbook.Name & ";Extended Properties=Excel 12.0;" ' cn.ConnectionTimeout = 40 cn.Open ' for i = 1 to col_tab1 nabor_act = nabor_act & “','” & tab1.cells(i,1) next i sql = "SELECT * FROM [res$] WHERE [ ] '” & right(nabor_act, len(nabor_act)-3) & ”' " Dim rs As New ADODB.Recordset Set rs = New ADODB.Recordset ' rs.ActiveConnection = cn ' rs.LockType = adLockOptimistic ' . Update(). . rs.CursorLocation = adUseClient ' rs.source = sql ' rs.Open rs.MoveFirst ' rew = "" Do Until rs.EOF ' rew = rew & "','" & rs.Fields(0).Value rs.MoveNext ' Loop rs.Close cn.Close
Here ... If we take the conditions of the example, a line for the query will gather somewhere for 10 seconds, a query with a sample will hang up for 25 seconds, and a 10 seconds test will depend on the desired result.
Something like this.
Source: https://habr.com/ru/post/217791/
All Articles