Here we are coming with one more exciting post which can help you to solve very basic but very important problems while writing VBA codes. For example, what VBA code you write to add borders in a range?
You might be using a code like below:
Range("A1:D10").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
Range("A1:D10").Borders.LineStyle = xlContinuous
In this post, we have shared similar codes that will makes your life easy.
The following code returns the extension from any file name:
Code:
Right(strFilePath,instr(1,StrReverse(strFilePath),"."))
Following code reads the text in the given cell or range. The code can be used where you want to alert the user through system audio device:
Codes:
Range("A1").Speak
Cells(1,1).Speak
This code protects the sheet for manual inputs; however if the changes are done through programming then it will allow the changes.
Code:
Sheet1.Protect Password:="123", UserInterfaceOnly:=True
In some situations when you want to accept other actions or interrupts performed by the user then you can make use of DoEvents. For example, you are running a long loop and you want to allow the user to stop the loop in-between.
Public bStop As Boolean
‘This function runs a loop on each row of the sheet. If the Boolean variable is True then it will exit from the loop
Sub StartLoop()
Dim lCounter As Long
For lCounter = 1 To Rows.Count
DoEvents
If bStop = True Then
Exit For
End If
'Code to be executed for each loop
UserForm1.Label1.Caption = lCounter
Next
End Sub
‘This button event will change the Boolean variable to True
Private Sub cmdStop_Click()
bStop = True
End Sub
When you want to perform certain tasks on each cell of a large Excel range then a normal loop may take lot of time to complete the task. It would be good idea if you convert the range to an array, perform the required changes and write it back to the Excel range. As the array variable is stored in RAM instead of Hard Disc, the loop will run faster.
Sub RangeToArray()
Dim varArray() As Variant
Dim lRow As Long
Dim lColumn As Long
'Convert the range to array
varArray = Range("A1:Z10000")
For lRow = 1 To 10000
For lColumn = 1 To 26
'Perform changes in the array
varArray(lRow, lColumn) = varArray(lRow, lColumn) + 1
Next
Next
'Write the modified array back to the Excel
Range("A1:Z10000") = varArray
End Sub
This function can be used to evaluate formulas or arithmetic calculations directly in the coding. This is helpful when you do not want to perform these calculations on a cell.
Code:
Evaluate(“=VLOOKUP(7,A:B,2,0)”)
When you are performing multiple changes or navigation in the Excel sheet, Excel application keeps on updating the screen to show the latest changes. This normally slowdowns performance of the code. To solve this problem, you can use ScreenUpdating property of Excel application. When you turn it False then screen will not be updated during code execution. If the property is True then screen will be updated as normal.
Code:
Application.ScreenUpdating=False
‘Perform changes in sheet
Application.ScreenUpdating=True
When you need to create a folder through programming, you can make use of MkDir VBA function for creating a folder.
Code:
MkDir (strFolderPath)
When you need to delete a folder through programming, you can use RmDir VBA function like below.
Code:
RmDir (strFolderPath)
Code:
Kill ("E:\Work\ExcelSirJi\Folder 1\*")
RmDir ("E:\Work\ExcelSirJi\Folder 1")
When you need to rename a file or files then you can make use of Name VBA function to easily rename the file(s). Note that you can also use Name VBA function to move the files from one folder to another. Have a look at the examples mentioned below.
Code:
Name strSourceFilePath As strDestinationFilePath
Thanks for reading the article, subscribe us to get more VBA tricks
We are offering Excel VBA Course for Beginners to Experts at discounted prices. The courses includes On Demand Videos, Practice Assignments, Q&A Support from our Experts. Also after successfully completion of the certification, will share the success with Certificate of Completion
This course is going to help you to excel your skills in Excel VBA with our real time case studies.
Lets get connected and start learning now. Click here to Enroll.
How to Insert Symbol in Excel? I came across many queries regarding inserting special symbols in Excel. Here we are guiding how you may do this quickly in excel. Follow these steps and you may…
In this article we will learn about VBA code to get computer name. Excel VBA, or Visual Basic for Applications, is a programming language that can be used to automate tasks within the Microsoft Excel…
How to Export Access Data to Excel using VBA Code? Creating a VBA tool in MS Access is always better as compare to MS Excel. MS Access provides better user interface and ability to handle…
VBA CODE TO HIDE MENU RIBBON IN MS ACCESS In MS Access, there are multiple ways to protect your code or tool from un-authorized access and edits. Some developers prefers to hide MS Access ribbons…
VBA Code to Browse a Folder Quite often a VBA developer requires code to browse a folder. This is mainly for saving the output file or reading the input file(s). Below is the VBA code…
Merge Excel Files From last few months, we have been receiving frequent requests from users for a VBA tool which can consolidate Excel files from a folder. So here we come with one more free…