IsValidPasswordString Function

'Following function will verify if the password string contains following characters or not?

Rem : List of Characters Group - ASCII
Rem : Group 1 : A-Z : 65-90
Rem : Group 2 : a-z : 97-122
Rem : Group 3 : 0-9 : 48-57
Rem : Group 4 : Special Characters Group 1 : 33-47
Rem : Group 5 : Special Characters Group 2 : 58-64
Rem : Group 6 : Special Characters Group 3 : 91-96
Rem : Group 7 : Special Characters Group 4 : 123-126

'Criterias of checking
'Minimum 8 Length
'At least One Capital
'At least One Small Character
'At least One Numeric
'At least One Special Character

Public Function IsValidPasswordString(ByVal passwordString As String) As Boolean
    
    'Variable declarations
    Dim txt                     As String
    Dim passwordCharacters()    As String
    Dim passwordCharacter       As Variant
    Dim testCapital             As Boolean
    Dim testLower               As Boolean
    Dim testNumber              As Boolean
    Dim testSpecialCharacter    As Boolean
    
    'Check for the Minimum Length Criteria
    If VBA.Len(passwordString) >= 8 Then
        IsValidPasswordString = True
    Else
        IsValidPasswordString = False
        Exit Function
    End If
    
    'Check rest of the conditions
    
    'Get characters in array
    txt = VBA.StrConv(passwordString, vbUnicode)
    passwordCharacters = VBA.Split(VBA.Left(txt, Len(txt) - 1), VBA.Chr$(0))
    
    'Get testified
    For Each passwordCharacter In passwordCharacters
        Select Case VBA.AscW(passwordCharacter)
            Case 65 To 90                                   'A-Z : 65-90
                testCapital = True
            Case 97 To 122                                  'a-z
                testLower = True
            Case 48 To 57                                   '0-9
                testNumber = True
            Case 33 To 47, 58 To 64, 91 To 96, 123 To 126   'Special Characters
                testSpecialCharacter = True
        End Select
    Next passwordCharacter
    
    'final conclusion
    If (testCapital And testLower And testNumber And testSpecialCharacter) Then
        IsValidPasswordString = True
    Else
        IsValidPasswordString = False
    End If
    
End Function

The benefit of Object-Oriented Programming (Class Modules) in VBA.

Many experienced professional VBA developers in the fraternity keep discussing how is OOP going to add value in writing programming? And the supporters of OOP, like me, always give them ideas on how OOP will create reusability of your code, how it will protect (encapsulate) your code from the outer world, etc., etc.

But today I want to push a very clear notion in support of object-oriented programming in VBA.

OOP will let your IMPERATIVE Programming into DECLARATIVE

IMPERATIVE means: To tell computer What to do? and How to do it?

DECLARATIVE means: to tell computer only What To Do!

Once we have designed a class for general methods and functions, our coding in General Module will be DECLARATIVE.

DECLARATIVE programming is fun, readable and neat & clean.

Hope this will help VBA Developers to get their self, practice more seriously in the OOP domain.

#kamalam

Problems and Their Solutions!

હું જ્યારે સેકેન્ડરી સ્કૂલમાં હતો, ત્યારે કમ્પ્યુટર્સ અમારા માટે એક જાદુઈ વસ્તુ હતી. હું વાત કરું છું વર્ષ 2001ની આસપાસ. હું ત્યારે ધોરણ સાતમાં અભ્યાસ કરતો હતો. મેં તરત જ કમ્પ્યુટરનાં ટીચર સાથે મિત્રતા કરી લીધી અને તેમણે મને તેમનો આસિસ્ટન્ટ બનાવી લીધો. એ સમયમાં જ ટીચરે મને માઈક્રોસોફ્ટ એક્સલ એપ્લિકેશન સાથે ઇન્ડ્રયુડુઝ કરાવ્યો. અને એમણે મને જયારે સમ ફન્કશન અને ઇફ ફંક્શન સાથે અવગત કરાવ્યો ત્યારે એ રાત્રે તો મને નીંદર જ ન આવી. મારુ મગજ ત્યારે જ દોડવા લાગી ગયું હતું. પછી મેં વધુ ને વધુ જાણવાનો પ્રયત્ન કર્યો અને મારુ પ્રથમ નાનું એપ્લિકેશન બનાવ્યું. એ હતું વિદ્યાર્થીઓના માર્ક્સને ગણી ને તેના પર્સેન્ટેજ બતાવતી શીટ.

એ સમયે એ મારા માટે બહુ જ મોટી વાત હતી. એ માર્કશીટ બનાવવા માટે મેં એક જ એક્સલ શીટનો ઉપયોગ કર્યો હતો. મેં મારા ટીચર ને એ શીટ બતાવી અને તેઓ ખુશ થઇ ગયા પણ એક જ શીટ ની અંદર બધું હતું એટલે અસ્તવ્યસ્ત લાગતું હતું એટલે ટીચરે મને તરત જ કહ્યું કે, "તે આ બધું એક જ શીટ માં કેમ બનાવ્યું છે?"
એ પછી ટીચરે બીજી શીટ ઇન્સર્ટ કરી અને મારી દુનિયા ફરીથી બદલી ગઈ. હું જાણતો ન હતો કે હું બીજી શીટ પણ ઇન્સર્ટ કરી શકું છું.

 
જીવન પણ કઈંક આવું જ હોય છે. આપણે બસ લાગેલા રહીએ છીએ કે એક જ પ્રકારની સમસ્યા માટે એક જ પ્રકારનાં સમાધાન સાથે. આપણે જીવનના બીજા આયામો તરફ તો નજર કરતા જ નથી અથવા તો જાણવાનો પણ પ્રયત્ન કરતા નથી.

 
જીવનના નવા આયામો કદાચ એક ક્લિક પર સામે નથી આવતા પણ એ હોય છે એ નક્કી.

#Kamalam

Recipe of Smart User interfaces on Excel VBA

When we talk about Developing Apps on Excel VBA, we have two straight options for designing the user interface of the app.

1. Worksheet based
2. UserForm based

Now, UserForm based user interface is common to understand but in the last few months, I understood the potential of the NAMED RANGES to build dynamic User interface on worksheets. Named Ranges are very helpful when user interfaces are changing frequently. when the named range is combining with conditional formattings, it creates magic! you can loop through all the named ranges and validate all name ranges based on the conditional formatting applied to them. It's blessings.

I'm now more confident building user interfaces on worksheets just like user forms.

Named Ranges + Conditional Formattings = Recipe of Smart User interfaces on Excel VBA.

#excel #vba #userinterface #userforms #worksheets #appdevelopment

#Kamalam

VBA or Office.JS! The dialogue of dilemma.

 

I have been a part of one Office.JS WhatsApp group for a long and it's not an ordinary group at all! I found a bunch of good excel professionals there but still, no conversation had happened in that group since it's been created. 

I know it's too early to decide or compare the interests of people for Office Js but it's also a reality that we do not have a proper driving force that can lead us to do some learning and experiment with the Office JS framework. We learned Excel and VBA just by doing some random stuff here and there and developed interests too. Even the freshers of this phase, tending to learn VBA because it's easier to understand and write. 

Python is still evolving to compete with VBA in all areas. I do not know whether people will accept Python as an interface for office automation or not! Most data scientists are learning python these days but excel is not only used for data science alone. Excel with VBA making that framework easy to build various business solutions prototypes, report automation, and somehow ERPs too! So, python has still a very long to chase to compete with Excel+VBA in all areas. 

If Office Js is the output of having thought of creating macros for a universal platform then they should have adopted & implemented C# and ASP.NET framework for the office automation scripting. It is cross-platform, adaptable, modern and most importantly it is user-friendlier than office JS in the context of coding! I would say, with just a little effort, current VBA professionals would have had become a part of that framework but I do not know about the likable future of Office JS. Please comment on your opinion here as well. 

are you guys a part of any "ACTIVE" group on Office Js? Please let us know. So we could join them as well and learn to support the future!

#OfficeJS #excel #vba #office365 #javascript #python #Csharp

ActiveObjects Turn back to me!

#excelvba #vba #excel

I was in the huge misunderstanding that ActiveSheet, ActiveCell objects are created normally by the recorded Marcos and so, in the dynamic programming/automation we should avoid using that because of its ACTIVE properties. I followed that for a long but today when I was working on creating a simple customized TreeView Tool for worksheet creating out of checkboxes, I came into the situation where I have to use Application. Caller property so I can determine dynamically what object has called the central module to apply further changes. We know that CheckBoxes are the Worksheet objects or we can say that the parent object of Checkbox control is the worksheet. I further noticed that, if I have to use the same TreeView tool across multiple sheets then my code should be worksheet independent. So to achieve that dynamically, I fall back to my own discarded thoughts which are to avoid ActiveObjects.

I have to use the ActiveSheet property to determine which object has called the module! Such uncertainty. But I learned a lot today.

Share your thoughts on using ActiveObjects as well.

Regards

Kamal.

Consolidating Multiple Sheets into ONE New Sheet using VBA.

Delete Target Sheet Automatically if the Target Sheet is Unprotected!

Friend asked me a little query.

he wants VBA to delete his Target Sheet automatically if the Target Sheet is Unprotected!

Now, that's something easy but it's not! Not at all! unless you know the best way to achieve that task using an available property of the Sheet Object.

There's a Sheet property called,."ProtectContents"

Here's how you can perform..

Step 1: Choose TargetSheet
Step 2: Open Code Editor for that Target Sheet Object from VBE.
Step 3: Paste Below Code

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    On Error Resume Next
    Application.DisplayAlerts = False
    If Sheets("SheetName").ProtectContents = False Then
        Sheets("SheetName").Delete
    End If
    Application.DisplayAlerts = True
    On Error GoTo 0
End Sub

That's it!

Now if someone will illegally try to crack your Target Sheet password from your workbook then after any cell selection by the user will trigger the above Sheet Selection Event.

The Code further investigate using the mentioned property whether the sheet is protected or not. if it is unprotected instead of Protected then Code will delete the sheet in a fraction of seconds.

Many of us do have an idea of this and many of us would not. So this strategy will help freelancers to make their templates more commercially protected!

Keep Learning.
Kamal Bharakhda

Questions Should be asked by Excel VBA Developer to their Clients

#Discussions

Questions Should be asked by Excel VBA Developer to their Clients before initiating the project.

We all are Learning lots of things as freelancers every day. Especially Non Technical part of the project. And you know it's the most important part of the project where we are getting to know everything before we begin.

I'm inviting you here to take part in this post. I hope this post will help each member of the group in their freelancing carrier.

General Category's Questions!

1. What does your business actually do? (Explain in Short!)
2. Why You want this application?
3. What current setup you have to simplify the process?
4. Why do you want to develop this application on Excel VBA Framework?
5. Have you ever tried to research for the already available solutions? If Yes, then what makes you not to choose them for the process?
6. Do you have your competitors? If Yes, then what they are using for the same process?

Application-oriented questions

1. Do you want the application to run on how many systems? (1/2/Multiple)
2. Do you want to keep your database in Excel or Outside of it?
3. If you want to keep your database outside then where you exactly want to place? (In Online Servers or in your Local System. because if you want your application should run on two or more systems then we have to centralize the database so every system can synchronize equally at a time.)
4. Do you want an application that should have a user-based login system? Like Admin will see everything and Employee will access few things of the system.
5. Will this application have anything effective for the professional year changes?
6. I will require information about each step of the application. because we will design a table of forms to save the information in the database. like, Username, address, etc.. etc..
7. Which of the operating system are you using?
8. Which of the Microsoft Office version are you using?

Choose User Interfaces for your application.

1. Example 1
2. Example 2
3. Example 3

In this section, you have to link here Your earlier made templates or VBA application's user interfaces. Let the client decide what he wants. You can also show examples of others which you can design on your own.

Please make comments with your suggestions and ideas. Let all the members will gain more benefits. :)

Kamal Bharakhda

Finding a String within a List of Strings

Don't you think it's too easy to perform such a procedure using INSTR function?
Yup, it's damn easy but the reason behind writing this post was a little bit core side of the problem.
Let's take an example here

suppose you have 100 cells containing a string. Now, you want to extract the cell address where the cell's string contains "1p" or anything else.
Now, Cell's string could have been anything but you have to find a specific string within a set of cells.
It's very easy to find such using following code.

dim I as Long 
For I = 1 to 100 Step 1
if VBA.Instr(1,Cells(I,1).value, "1p") > 0 then 
msgbox .cells(i,1).Address
end if 
Next I 

Above code will simply give you the address of the matching cell with string. But here comes the catch. String 1p is in standard or lower case and you might don't know in the cells which case would be there so, to avoid that confusion programmer generally uses the following line using OR logical treatment.

dim I as Long 
For I = 1 to 100 Step 1
if VBA.Instr(1,Cells(I,1).value, "1p") > 0 or VBA.Instr(1,Cells(I,1).value, "1P") then 
msgbox .cells(i,1).Address
end if 
Next I

But believe me, to execute the proper way is a bit easy and different using inbuild VBA functions. Because more stuff on the logical operation the processor would take more time to process it. So better is to use the following idea to process the same thing using  UCASE function.

dim I as Long 
For I = 1 to 100 Step 1
if VBA.Instr(1,UCASE(Cells(I,1).value), UCASE("1p")) > 0  then 
msgbox .cells(i,1).Address
end if 
Next I

Function UCASE will consider each alphabet in the string as UPPER case and that's how you bypass the CASE issue while finding them and matching the string withing string.

Our group member Anil Khanna Karunakaran has asked my this query at personal so I thought this could be a learning point for many friends.
See these kinds of little techniques are a very important part of programmers. Actually, this small difference actually defines the programmers sometimes in some situations.

Moral of the story is everything is capable to provide results. but the good programmers will do it more creative way. So, kindly share your techniques here with us to solve some general issues.

- Kamal Bharakhda

Build Searchable Dropdown Control using ComboBox and ListBox

Steps to achieve this control

Step 1: Insert an Userform
Step 2: Insert TextBox from Toolbar
Step 3: Insert ListBox from Toolbar
Step 6: Write Few names in Sheet1 in column 1
Step 7: Insert a button on a worksheet and write the following code to it.

Private Sub CommandButton1_Click()
    UserForm1.Show
End Sub

Step 4: Change ListBox Heigh to 0 from Properties window.
Step 5: Copy Following Code

Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    
    MsgBox Me.ListBox1.List(Me.ListBox1.ListIndex)
    
End Sub

Private Sub TextBox1_Change()
    
    Dim I As Long, tmp As String, arr() As String
    Dim J As Long, ArrCount As Long, K As Long
    
    With Me.ListBox1
        .Clear
        .Height = 0
    End With
    
    If Me.TextBox1.Value = vbNullString Then Exit Sub
    
    tmp = vbNullString
    ArrCount = 0
    
    'Filling the Array with Values which matches the criteria
    With Sheet1
        J = .Cells(.Rows.Count, 1).End(xlUp).Row
        For I = 1 To J Step 1
            If InStr(1, .Cells(I, 1).Value, Me.TextBox1.Text) > 0 Then
                tmp = tmp & .Cells(I, 1).Value & "|"
            End If
        Next I
    End With
    
    'Filling Listbox
    With Me.ListBox1
        .Top = Me.TextBox1.Height + Me.TextBox1.Top
        If tmp <> vbNullString Then
            tmp = VBA.Left(tmp, Len(tmp) - 1)
            Erase arr
            arr = VBA.Split(tmp, "|")
            ArrCount = UBound(arr)
            For K = 0 To ArrCount Step 1
                .AddItem arr(K)
            Next K
            .Height = 100
        Else
            .AddItem "No Item Found"
            .Height = 18
        End If
    End With
    
    'Code By Kamal Bharakhda
    
End Sub

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

Run your VBA Program Daily & Automatically!

My Client has a Query!

He wants to receive an email as a notification just before a 15 days before his employee's vacation period starts.

here is his explanation in his words : "I am thinking about a script that opens the spreadsheet once a day iterate through all the employees (about 20) then check if today's date is equal to the day that each employee will complete one year minus 15 days, and, if the result is true, send the notification. But I don't know how to implement that with code."

Here is the solution how to make this automation happened.

1. Create a workbook

2. Add Worksheet

3. Name it as Employee Manager

4. Create a Table and enter the Employees Details in line by line

5. Create a VB Program which finds the Joining Date of Employee and if it matches the criteria then It will Email you just before the vacation period starts for him/her.

6. Supposed, Employee joined on 1st Jan 2018 then on the 15th December 2018, you will receive an email about the employee and his vacations details.

7. Now, it's very normal to create process mentioned in Point 6. (If it still thought for you then don't worry... let us know, we will provide you the solution for that module too...)

Now guess, up to the point 7 everything goes according to the plan. Now, it's time to automate it!

Now suppose in the D drive, in some folder, your this workbook is placed and it isn't open even. But the Module with VB code is also there inside. You will be required to use TWO other services of Microsoft Windows for this process to automate the VB code in the module. How? Here is the process.

8. The First thing you required is to create a VB Script. yes a file with an extension of ".VBS". You have to open a Notepad and write some code and you have to save it as any name with .VBS extension. which will further create a VB Script file.

9. Now, if you click the VB script file, if everything is okay with it, then it will find the workbook, then it will find the module and then it will run the sub-procedure which will roam through each line of employee details and if your current date is 15th December 2018, then It will trigger the email procedure and will send the email to you automatically without opening the excel file. isn't its a great thing?

Following is the YouTube link to create a VBS file to trigger the sub-procedure.

Run Macro Automatically without Opening Excel File

Now the question is how can we create code to click on that VBS file automatically on each day so you don't have to do manually on each day?

10. Here is the answer, Use Windows Task Scheduler Tool. Just Target The .VBS file you have to create and configure it in such a way that it will trigger the VBS file on each day when your computer is open.

Following is the YouTube link to trigger the VBS file by Task Scheduler on daily basis.

Launch VB Script Using Task Scheduler

If you guys liked my idea then please let me know in the comment and please do like and share this article.

Thank you.

Kamal Bharakhda

Future of VBA

It's necessary to think of exactly 10 years ahead of now because it makes your actions more based on future.

well, when you are doing good something anything, the best thing you received out of it is a community of Good People. Yes, the best asset.

I met one guy on LinkedIn named Vincent ISOZ and he suggested me to provide the same VBA solution on power query. because according to him it is the future. in the next 10 years, every corporation will moving towards cloud office. Then he wrote one comment and I really felt, I should post that comment here in front of you guys, because if VBA is the tree then we are the leaves of it.

well here is his comment...

"I also worked for Microsoft as an external consultant during 4 years. VBA will be stopped in 2024 (according to the roadmap I saw but you can probably add the 3 usual years of delay) as only Office Online will still be maintained (cloud strategy of Microsoft). We already know that the replacement of VBA is Power Query (with the M language) or JavaScript with JQuery (that's one of the reasons why Office 2019 allows you to create functions in JavaScript instead of in VBA).

And almost nobody will write VBA anymore as only JavaScript Excel functions will be supported on Mac, Windows, and Online.

Microsoft is moving everything to the web using mainly Typescript that is compiled in JavaScript and powered by Electron.

But the future will tell us what will really happen (the roadmap change many times). We will speak again about this in 2024+3 = 2027. See ya!" - Vincent ISOZ

Lol, his comment stunned me. I do have lots of argument with him thereafter but the may the best commenter win the argument. 

But I know he didn't opposed the VBA Idea but he advocated him self for the upcoming demands from the industry. So he literally asked us,

are we ready to supply over demands?

Let's discuss this....