saurabhlakhanpal's blog
Run a code after delay
Submitted by saurabhlakhanpal on 6 July, 2013 - 15:29Ever wondered how to insert a delay while running a code.
In this blog I'll explain how to add delay before running a code.
Assumption:
A delay of 25 seconds needs to added before running the code.
Steps:
1. Open a new file
2. Press Alt+F11 to open VBA editor
3. In the left navigation window select ThisWorkbook
4. In the editor window enter the code below
5. Close the VBA editor
6. Save the file as Macro Enabled File
7. Test with you inputs
Code:
Sub InsertDelay()
Dim Delay As Variant
Dim Start As Variant
Open Lotus Notes from Excel VBA
Submitted by saurabhlakhanpal on 6 July, 2013 - 14:10In this blog we'll see how to open Lotus Notes from Excel VBA.
Steps:
1. Open a new file
2. Press Alt+F11 to open VBA editor
3. In the left navigation window select ThisWorkbook
4. In the editor window enter the code below
5. Close the VBA editor
6. Save the file as Macro Enabled File
7. Test with you inputs
Code:
Sub OpenLotusNotes()
Dim LN As Object
Set LN = CreateObject("Notes.Notessession")
LN.Visible = True
End Sub
I think this has been informative and I thank you for viewing.
-Saurabh
How to use VBA select case statement
Submitted by saurabhlakhanpal on 6 July, 2013 - 13:26In Microsoft Excel everybody uses If-Then-Else statement every now and then. If statement is useful if you have a limited number of conditions. But let's assume you have to match around 100 conditions, in that case If statement will take a lot of code to be copied and pasted and also to execute.
Instead of using If statement in this scenario you can use CASE statement.
Syntax:
Select Case test_expression
Case condition_1
result_1
Case condition_2
result_2
.
.
.
Case condition_n
result_n
Case Else
result_else
End Select
Save file using cell contents as file name
Submitted by saurabhlakhanpal on 5 July, 2013 - 15:26In this blog, I'll explain how to save the file with cell content as file name.
Assumption:
The Cell Value which is used for file name is A1 and the extension is ".xlsx".
Steps:
1. Open a new file
2. Press Alt+F11 to open VBA editor
3. In the left navigation window select ThisWorkbook
4. In the editor window enter the code below
5. Close the VBA editor
6. Save the file as Macro Enabled File
7. Test with you inputs
Code:
Sub FileNameAsCellContent()
Dim FileName As String
Dim Path As String
Application.DisplayAlerts = False
- 4 comments
- Read more
- 116666 reads
Use VBA to ask user for File Name
Submitted by saurabhlakhanpal on 5 July, 2013 - 10:34Let's say you have a tempalate file and you perform a lot of work on that tempalate and accidentaly save that template with all the changes. The original tempalate will be lost and you'll have a lot of work to do.
To avoid this you can save the tempalate file before closing it. But, "to err is human" and what if you forgot to do that.
Well, VBA is here for your rescue.
Using VBA you can ask the user to input the file name every time you close the file.
Assumptions:
User wants to save the file as ".xlsx".
Steps:
1. Open a new file
2. Press Alt+F11 to open VBA editor
Add new WorkSheet and Name it as Current Date
Submitted by saurabhlakhanpal on 5 July, 2013 - 06:32You always add new WorkSheets in your WorkBook. Sometimes it seems very boring to do it manually (specifically if you have multiple sheets to add).
A simple solution to avoide this boredome is to automate it.
I've written the VBA code to add a new WorkSheet at the end of WorkSheets and name it as current Date.
Steps:
1. Open a new file
2. Press Alt+F11 to open VBA editor
3. In the left navigation value select ThisWorkbook
4. In the editor window enter one of the code below (based on your requirement)
5. Close the VBA editor
6. Save the file as Macro Enabled File
CheckBox is checked or not
Submitted by saurabhlakhanpal on 5 July, 2013 - 05:35CheckBoxes are very common in Excel forms. Based on the condition whether it is checked or not, you may want to run some code.
Now, how would you check whether the CheckBox is checked or not? It is very simple, use the steps below.
Steps:
1. Open a new file
2. Press Alt+F11 to open VBA editor
3. In the left navigation value select ThisWorkbook
4. In the editor window enter the code below
5. Close the VBA editor
6. Save the file as Macro Enabled File
7. Test with you inputs
Code:
Sub CheckBox()
Dim CheckBox As Shape
Data Flipping in a row - Write in reverse order
Submitted by saurabhlakhanpal on 4 July, 2013 - 07:22Flipping data in a row to write it in reverse order.
Let's say you have data in a row and you want to have the data in reverse order.
You can do that in Excel by writing the VBA code.
Assumption: The start position of data is range A1.
Steps:
1. Open a new file
2. Press Alt+F11 to open VBA editor
3. In the left navigation value select ThisWorkbook
4. In the editor window enter the code below
5. Close the VBA editor
6. Save the file as Macro Enabled File
7. Test with you inputs
Code:
Sub FlipDataInARow()
Dim oldStart As Variant
Dim oldEnd As Variant
Find SubString in a String - (InStr & InStrRev) Function VBA
Submitted by saurabhlakhanpal on 3 July, 2013 - 10:12In Microsoft Excel, the InStr function finds out if a SubString is present in a String and returns the position of the first occurrence of a SubString in a string.
Syntax:
The syntax for the INSTR function is - InStr( [start], String, Substring, [compare] )
Parameters:
Start (Optional)
Numeric expression that sets the starting position for each search. If omitted, search begins at the first character position. The start index is 1-based.
String (Required)
String expression being searched.
SubString (Required)
String expression sought.
Compare (Optional)
Sheet Change Event - Single Execution
Submitted by saurabhlakhanpal on 1 July, 2013 - 16:45Worksheet change event occurs when value in a cell is changed.
The below code will add the time-stamp in the cell next to the cell whose value is changed.
Use case: This can be used when a store wants to track the orders with order number and time-stamp.
Assumption: Column A will have order number and Column B will be updated with the current time-stamp when the column A is updated.
Steps:
1. Open a new file
2. Press Alt+F11 to open VBA editor
3. In the left navigation value select ThisWorkbook
4. In the editor window enter the code below
5. Close the VBA editor
Recent comments
5 years 34 weeks ago
6 years 20 weeks ago
6 years 32 weeks ago
6 years 35 weeks ago
6 years 36 weeks ago
6 years 42 weeks ago
6 years 50 weeks ago
6 years 50 weeks ago
6 years 50 weeks ago
6 years 50 weeks ago