Showing posts from December, 2018

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

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 (w

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

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]     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 R

IST (Indian Standard Time) to EST (Eastern Standard Time) Zone in Excel VBA.

Use the following function to get IST Date Time Values of the cell value into EST Date-Time Value. Paste the following VBA code into Module and that's it! '/----------------- Function IST_to_EST(ByR ef SelectRange As Range) As Date          ESTValue = SelectRange.Value - (0.0416667 * 10.5)     IST_to_EST = ESTValue End Function '/----------------- See this video to get more idea on this. Please comment if you have any query.

Delete Row from the Table when you have multiple tables on the same sheet

My client has query was to delete a row from the table. It sounds easy. But when the same sheet has more than two tables starting from the same row (defiantly from the different columns) it's a bit tricky to delete only a row from the Specific table. let's say in the same worksheet You have Table1 and Table2 and you want to delete a row from table 1 dynamically by just selecting a cell inside the table range that's how my client wants to remove a row. I prepared the workbook to show the demo to delete a row of specific table range by selecting the cell inside of it. Here is the Code : Private Sub CommandButton1_Click()          If ActiveCell.Value = vbNullString Then Exit Sub          Const RestrictedRow As Byte = 1     Const FirstColofTable1 As Long = 1     Const LastColofTable1 As Long = 5     Const FirstColofTable2 As Long = 7     Const LastColofTable2 As Long = 11     Const Table1LeftIndex As String = "A"     Const Table1RightIndex As Str

If my input is 1,2 & 3 then result should be 1,2,3,12,13,23,123

Q. Need VBA code to get different combinations. Eg. if my input is 1 & 2 then the result should be 1,2,12. If my input is 1, 2 & 3 then result should be 1,2,3,12,13,23,123 Input can go up to 20 digits also Also, result 12 and 21 should treat as same Similarly, 123 & 321 or 231 or 312 is the same. here is the perfect dynamic solution. You can add more than 20 digits and it will still provide you with the perfect result. Put the following code in the module and that's it. I was working on this since yesterday. :D

Using Spin Button, Hide & Show the column one by one in Excel VBA

Show and Hide Columns one by one by Spin button up to mentioned columns limits. this is going to be the best small tool for excel users. here is the video. and here is the code of Spin Button. Private Const DefaltWidth As Integer = 9 Dim TargetSheetName As String Dim TargetSheet As Worksheet Dim FirstColNo As Integer Dim LastColNo As Integer Dim I As Integer Private Sub SpinButton1_SpinDown()     Application.ScreenUpdating = False     'Assigning Values to the Variables     TargetSheetName = "Sheet1"     Set TargetSheet = Application.ThisWorkbook.Sheets(TargetSheetName)     FirstColNo = TargetSheet.Range("A7").Value     LastColNo = TargetSheet.Range("A9").Value     If TargetSheet.Range("A7").Value = Empty Or TargetSheet.Range("A9").Value = Empty Then         MsgBox "Please Set The  Column Range Limits Before You Proceed", vbCritical, "Kamal Bharakhda"         Exit Sub     End If