Need for a Class-based Programming in Excel VBA!

Need of a Class based Programming and Responding to Events of Dynamically added Controls

Title speaks it self that I'm going explain here about Events Procedures of the Controls we are using in UserForms in Excel VBA.

I always wondered to myself about, "why should I required Class Based Programming rather I can manage my every tasks through General Modules in Excel VBA? "

I consistently in conversation with many experts of excel VBA who has very salient knowledge with class based programming and they always told me about we need class programming to Create the more secured, effective and manageable structure of the object based programming. Yes they are correct that's what the class programming were introduced to the world of programming.

in short, I settled with above thoughts that class programming is tool for me to make my programming structure more effective! Which indirectly means If i can manage everything from the general modules and userforms modules then I wouldn't have been in collaboration with class programming ever. So I didn't paid any attention towards class based programming so far.

But the day come when I have changed my perception regarding class programming. It wasn't simply optional part of programming but it's a necessary part of programming when you are building the solutions at certain level.

Ohh, How? You guys pretty much know about my experiments with userforms. I used to create effective user interfaces with userforms since the day I have been introduced to the userforms of Excel VBA.

As time passed I kept observing modern day application which has very nice looking interfaces and controls like menu bars and etc. But those controls were not available in the common resources of excel VBA libraries. So I have challenged myself to create those tools or controls for userforms in excel to build smart looking solutions. I have build one Menu Bar based Tool for my applications and here is the link of that: 

https://www.youtube.com/watch?v=owpGuoJYEcM

But I found one major problem when I was developing above control where I have to add controls dynamically on the userforms. And I found there My Events of the Control which were added dynamically during run time on the userforms were not responding or not working!

So to get the rid of this issues I researched a bit and Found that the Events and methods to the dynamically added controls were not worked directly from the userform's modules.

We have to define class and with Events variables in the class so those dynamically added controls will keep reacting on the sequential events triggers.

So that's how I learned Class programming is very necessary at dynamic programming.

Here is the article where the author has solve this issue and you can refer that! Comment for more queries and ideas.

https://codereview.stackexchange.com/questions/205263/responding-to-events-of-dynamically-added-controls

Passing Arrays to Sub Procedure

Very Very Important Tutorial for VBA Developers.

"Passing Arrays to the SubRoutine"

You won't be complete without using arrays in programming. Arrays are like blessings to the programmers. Like Juliet. :D

So usually in the life of Excel VBA developers, we code sometimes repetitive task within a single procedure. Like creating the same excel sheet or template for their office staff or for their customers using a single data table.

So what happens actually, we always Loop through a database for generating each report or template of many. so, it's a time consuming and it's even a bad way of using code VBA resource.

So what arrays will do, in a first attempt, arrays will collect the whole table into it and then you can use it for the rest. but then comes if we more repetitive processes then we have to create subroutines or sub-procedures to cut down the length of the main procedure.

So, now it's again necessary to pass the array to that subroutine or subprocedure otherwise we have to do the same procedure, again and again, we keep wasting time to fill the arrays and so on. So here is the little code will help you to understand the concept.

Option Explicit
Sub TestingArrays()
Dim Arr() As String
Dim temp As String
Dim I As Byte, J As Byte

temp = "A|B|C|D"
Arr = VBA.Split(temp, "|")

'Following is Process
For I = 1 To 5
    Call TestingLinkedArray(Arr)
Next I
End Sub

Private Sub TestingLinkedArray(ByRef Arr2() As String)
Dim I As Byte
For I = LBound(Arr2) To UBound(Arr2)
    Debug.Print Arr2(I)
Next I
Debug.Print "-----"
End Sub

- Kamal Bharakhda

Add New Item in ComboBox in a Cool Way

Step 1: In any column on a sheet let's assume Column "J". in Cell J1 type header of the combo box and in Cell J2 type "Add New Item"

Step 2: Select the Cell J2 --> Goto Formulas Tab --> Click on Name Manager --> Click on "New" --> Type any name you required, Let's say you typed "MY LIST"

=OFFSET(KDATA!$J$2,0,0,COUNTA(KDATA!$J:$J)-1,1)

Step 3: Now, U can design the user form and add the combo box in it. type the following line in the userform_initialize event

Userform1.ComboBox1.RowSource = "MY LIST"

Step 4: Double click on the combo box and write following VBA codes into it. and that's it.

Private Sub D3_Change()

    If U.D3.Value = "Create New Type" Then
        With shKDATA
            .Range("J" & .Cells(.Rows.Count, "J").End(xlUp).Row + 1).Value = _
            Application.InputBox("Please Type New Supplier Type", "Add New Supplier Type")
            With .Range("J" & .Cells(.Rows.Count, "J").End(xlUp).Row)
                If .Value = False Then
                    .Value = vbNullString
                End If
                U.D3.RowSource = "NEW_SUP_TYPE"
                U.D3.Value = .Value
            End With
        End With
    End If

End Sub

U = Name of Userform1
D3 = Name of ComboBox1

- Kamal Bharakhda

Look for the String in the Cell of Multiple Column Table and if not found then Filter the whole row

Suppose, You have 10 columns table in excel and want to filter out such rows which do not have String that you are looking for. If any cell of the row has such string then it should not be filtered out. 

Want your Excel File/Application will RUN smoothly in everywhere?

Hello Developers, 

With this post, I'm going to share with you a very handy and useful trick which will help you to make you Excel or Macro Enable Excel files DISTRIBUTABLE or PORTABLE.

The main issue usually excels developer faces when their highly invested spreadsheets stop working or generate of bugs in the initialization.

There are lots of causes of that problem but the main issues a developer usually face a Catastrophic Run Time Error. Which occurs when you have used a function or formula in your excel sheet which doesn't support in that office version.

Then what, developers nightmare starts from the same moment. He needs to go through each formula and code from the top to the bottom to find the unsupported functions.

But I have the solution and I'm gonna share it with you. :)

There is a website or we should call it a lifesaver website called

https://www.techonthenet.com

Now, when you gonna use any formula which you have doubts whether it will support in another system or not then just open that website and click on the search bar available in right upper corner and type the formula or function or VBA function in the text box.

Now, pick the first result from the result and everything about that formula or function will be available now.

But here comes the most interesting section of that page.

it's been titled as,

"Applies To"

Yes, go to that section and you will see the list of supported office versions. Wow, such a handy thing for the Excel developers.

My experience with Catastrophic Run time Error.

Formula IFNA has been introduced from the office 2013 version. And I have developed my application in 2016 at that time and when I have delivered that application to one of my clients who has office 2007 at that time, and application faced the error. I was in the terrible moments. My more than a hundred of hours of work is hell not responding in his system. Man, you need to see me at that time. But thanks to the god for my troubleshooting abilities it leads me to this wonderful website.

And from that point of time, I usually verify my function with dual checking with the website.

Hope it will help you in your development.

- Kamal Bharakhda

How to create Values as Developer?

If we talking about the development of an application then there are following things concerns the most.

1. Handy, Clean, Sober and Easy to understandable, User Input interface. which is simply known as a user interface or a front-end.

2. The Backend, which has lots of data processing procedures, functions, and formulas, which works on user inputs, should be very well written, optimized, updated and should be very well commented.

3. The Reporting or Outputs or main objective of the application should be How effectively, efficiently and quickly can be delivered without causing legging.

4. The Database structure, where the user's highly important data has been saved to connect, all the missing dots of the application.

So, a developer with a good vision, will consider all the above points and design a whole software based on the same consideration.

When we help the user to input the required field within their comfort it creates value.

1. When we help the user to process their data to form an output within their available resources like office version, RAM, Windows version and so on... creates value

2. When we help a user to provide the most effective output above than their actual requirement, creates values.

3. When a user can access and edit and update their earlier records easily, creates values.

4. When a developer design an application by considering for the next 5 years, creates values.

and only Value factor can exponentially grow and that's how your reputation grows exponentially and makes you a more profound developer in the market, which is itself a huge reward which further creates demand of yours as a developer.

- Kamal Bharakhda

Fastest Method to Copy Filtered Data in Excel VBA

Yesterday in my post,

https://www.facebook.com/groups/ExcelFreelancersGroup/permalink/2469040236459183/

related to 'Copy the "Filtered" Data from Sheet1 to the Sheet2' and Mr. Mehmet, asked the following thing in addition to the provided solution and here was his query

"Many of Excel users are using excel as Database and they are facing huge datasets such as 500k rows and 100 columns. Could you please provide a faster way to copy and paste data such as arrays or advanced filter or anything that you would suggest us."

****SOLUTION****

Now, I think Mr. Paul Kelly has your answer with such great detail as never seen or experimented before. I would like to thank him for his effort to get such figures which are mentioned in the image attached to this post.

So the basic query of my friend Mehmet is to know all about which method is Fastest of all.

So Mr. Paul has conducted his own speed test to know which method is really quick in the average scenario.

Here is his post in his words

------------------

"These speed tests on copying and filtering data really surprised me.

I didn't think Advanced Filter would be the fastest method.

The columns headers are the number of rows of data in the test.

The values are the time in milliseconds to filter and copy the results to a worksheet."

------------------

Thank you so much, Mr. Paul Kelly, again for these surprising data and hope at least we have found the best method to perform such a task.

- Kamal Bharakhda