Experiences in developing dynamic reports using VBA

VBA in this projectProgramI wrote a lot, but I also learned some experiences, which will be helpful for any language development in the future. The following are some of my development experiences.

1. overflow error

One of the most common errors when I write a program to run a report is that the row end mark is a result or a total result. Then, I can determine whether a row has been found, when the position of the query insertion changes, or the overflow error occurs without this technical mark (I use integer to record the row number, the maximum value is 32767 ). After thinking over the past two days, you don't need to do this. Now I think we can use the find method to directly find the end mark and then use the ERR Object to handle the error. This makes the report more readable and does not report errors.

2. Changes in the insert position of a query result in the modification of function parameters.

Previously, I wrote an insert sub to think it is relatively clever. In fact, we can use the find method to locate the query location, so no matter how the query Insertion Location is modified, it will not causeCodeWill not delay me so much time, good design is really very important.

3. Modifications caused by changes in the query Structure

Although I separate the program that copies the query to the report and write it as a sub, I think it is still troublesome. I just like to keep breaking through the limit. Now I think there is a better way. Save the query and report location information in an array and pass the array into a sub as a parameter. This VBA will become very easy in the future, you only need to change an array. The modification and debugging time is greatly reduced, and the complexity is greatly reduced. The changed part has been completely split and changed to a fixed one.

4. Real-time Summary

When I write the first program in a method, I will use the same method to solve all similar problems later. It is not only a program, but also other problems in my life, so sometimes it is not always good to repeat the past. Now I am doing a dynamic approach. Now I think there is a better way to reflect on it, but it is too late. In fact, many senior consultants are not necessarily better, because they only use one method to solve similar but different problems, and the method is not necessarily the best. Always keep the mindset of exploration and learning to grow faster, even if I have been doing ERP for 10 or 20 years, this is a good consultant.