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....

Import XML Data in Excel Using VBA

Here is the complete VBA code to import XML Data into the Excel Workbook in a completely dynamic approach. Look at the following video.

Following is the VBA Code

Sub Impor_XML_Data()
    
    'Code By Kamal Bharkahda
    Application.ScreenUpdating = False
    Dim TargetSheet As Worksheet
    Dim ChooseFIle As Variant
    Dim TargetSheetName As String
    Dim TargetCellAddress As String
    
    TargetSheetName = Application.InputBox("Write a Sheet Name where you want to place the XML Data ***Case Sensitive***", "Target Sheet Name")

    TargetCellAddress = Application.InputBox("Write a Cell Address from where XML Data Start Placing", "Target Cell Address")
    
    
    Set TargetSheet = ThisWorkbook.Sheets(TargetSheetName)
    TargetSheet.UsedRange.Clear
    
    ChooseFIle = Application.GetOpenFilename("XML File (*.xml), *.xml", , "Kamal Bharakhda", , False)
    
    If ChooseFIle = vbNullString Then Exit Sub
    
    ThisWorkbook.XmlImport Url:=ChooseFIle, ImportMap:=Nothing, Overwrite:=True, Destination:=TargetSheet.Range(TargetCellAddress)
    
    MsgBox "Import Done"
    
    Set TargetSheet = Nothing
    Application.ScreenUpdating = True
    
End Sub

Import CSV Data into the Worksheet!

Hello Friends! Merry Christmas to all of you and to your friends and family.

Let me share a Christmas present for you all.

Do you want to import CSV file into the sheet and calculate the sum of the column data into the last row?

Well here is the file! and video of the file is in the comment box. Let me know if you guys have any queries.

Thank you. and enjoy your beautiful festival season.

Regards,
Kamal.

Sub ImportCSVFile()

    'By Kamal Bharakhda @ 919328093207 [E] kamal.9328093207@gmail.com
    Application.ScreenUpdating = False
    Dim xFileName As Variant
    Dim TargetSheet As Worksheet
    Dim Rg As Range
    Dim xAddress As String
    Dim TargetRange As Range
    
    Set TargetSheet = Sheets("DATABASE")
    TargetSheet.UsedRange.Clear
    xFileName = Application.GetOpenFilename("CSV File (*.csv), *.csv", , "Kamal Bharakhda", , False)
    
    If xFileName = False Then Exit Sub
     On Error Resume Next
    Set Rg = TargetSheet.Range("A1")
    On Error GoTo 0
    If Rg Is Nothing Then Exit Sub
    xAddress = Rg.Address
    With ActiveSheet.QueryTables.Add("TEXT;" & xFileName, Range(xAddress))
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 936
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
    
    Set TargetRange = TargetSheet.Range("A:A")
    
    'Splitting the Delimited Strings
    TargetRange.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
    Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
    :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1)), TrailingMinusNumbers:= _
    True
    
    With TargetSheet
        Dim TotalRow As Long
        TotalRow = .Cells(.Rows.Count, 1).End(xlUp).Row
        .Cells(TotalRow + 1, 3).Value = "Sub Total"
        .Cells(TotalRow + 1, 4).Value = Application.WorksheetFunction.Sum(.Range("D2:D" & TotalRow))
        With .Rows(TotalRow + 1).Font
            .Bold = True
            .Size = 13
        End With
        .UsedRange.Columns.AutoFit
    End With
    
    MsgBox "Process Done", vbInformation, "Message from Kamal"
    
    Application.ScreenUpdating = True
End Sub