How to Use VBA ChDir Function in Excel (4 Suitable Examples)

In Microsoft Excel, it’s easy to change the directory or folder by using VBA Functions. There are a total of nine File/Directory Functions. One of the important File/Directory Functions in Excel VBA is the VBA ChDir function. In this article, we will look at how this function works in excel VBA.


Download Practice Workbook

You can download the workbook and practice with them.


VBA ChDir Function: Syntax & Arguments

The excel ChDir function is one of the advanced functions pre-owned by VBA. The ChDir indicates “Change Directory”. There are so many built-in functions in Excel VBA. And, the ChDir Function is also a built-in function categorized under File/Directory Functions.

Syntax

The syntax for the VBA ChDir function is:

ChDir(path)

Arguments

Argument Required/Optional Explanation
Path Required This is a string parameter that exhibits the directory or folder we wish to change.

Return Value

No value.

Type

VBA Function (File/Directory Function).

Applies to

Excel office 365, Excel 2019, Excel 2016, Excel 2013, Excel 2011, Excel 2010, Excel 2007, Excel 2003.


4 Uses of VBA ChDir Function

We know that when we want to save an excel file for the first time, we can select a folder for saving the files. After that, when we want to save other excel files, it saved in the same folder by default. We can change the folder or directory by using the excel VBA ChDir function. Let’s look at some uses of the function below.

Suppose that, we have a dataset in our excel file, which contains a students’ marks. The name of the student is Jhon, his subjects are in column C, the marks of each subject are listed in column D. Now, we calculate the total marks in cell C11 by using the SUM function. So, let’s save this file.

If we want to look at where the file is located, firstly, we need to go to File in the ribbon.

4 Uses of VBA ChDir Function

In the Home section, we can now view our file location.

4 Uses of VBA ChDir Function

If we wish to view the exact location. Just right-click on the file. And, go to Open File Location.

4 Uses of VBA ChDir Function

This will take us to the location of the file. And we can see that our file location is D:\Sabrina Ayon.

Let’s change the directory/ folder of the file.


1. Change the Present Folder Using VBA ChDir Function

To change the current folder of the file using VBA ChDir Function, we have to follow some steps below.

STEPS:

  • First, go to the Developer tab from the ribbon.
  • Second, click on Visual Basic to open the Visual Basic Editor.
  • Another way to open the Visual Basic Editor is simply to press Alt + F11.

Change the Present Folder Using VBA ChDir Function

  • Next, go to Insert and select Module from the drop-down menu.

Change the Present Folder Using VBA ChDir Function

  • Or, right-click on the sheet, then select View Code.

  • And, this will open up the visual basic window.
  • After that, copy and paste the VBA code below.

VBA Code:

Sub VBA_ChDir_Func_ChangeFolder()
    Dim chFolder As String
    chFolder = "D:\ExcelDemy\1111"
    ChDir chFolder
    MsgBox "Current Folder is: " & chFolder, vbInformation, "VBA ChDir Function"
End Sub
  • Next, press the F5 key or click on the Run Sub button to run the code.

Here, we just named the procedure VBA_ChDir_Func_ChangeFolder. Then, declared the variable as a string, cause we know that the parameter of the function is a string expression. We name the variable chFolder. As we want to save our file to the folder “D:\ExcelDemy\1111”. So we keep the path D:\ExcelDemy\1111 to our variable chFolder. And then, locate the file D:\ExcelDemy\1111 by declaring the ChDir function. Finally, the above code will change the folder to “D:\ExcelDemy\1111”. And, show the current file location in a MsgBox.

Change the Present Folder Using VBA ChDir Function

Output:

After running the code, a MsgBox will appear and display that the Current Folder is: D:\ExcelDemy\1111.

Change the Present Folder Using VBA ChDir Function

Read More: How to Use MsgBox Function in Excel VBA (A Complete Guideline)


2. Excel VBA ChDir Function to Replace the Current Directory

We can also change the directory using the VBA ChDir Function in excel. To do this, let’s pay attention to the steps below.

STEPS:

  • In the beginning, go to the Developer tab > Visual Basic > Insert > Module.
  • Or, right-clicking on the worksheet will open up a window. From there go to the View Code.
  • And, this will take you to the Visual Basic Editor field, where we can write VBA Macros.
  • On the other hand, pressing Alt + F11 will also open the Visual Basic Editor.
  • Likewise, in the previous method, we want to change the directory to “C:\Temp”.
  • Now, copy and paste the VBA code below.

VBA Code:

Sub VBA_ChDir_Func_ChangeDirectory()
    Dim chDirectory As String
    chDirectory = "C:\Temp"
    ChDir chDirectory
    MsgBox "Current Directory is: " & chDirectory, vbInformation, "VBA ChDir Function"
End Sub
  • Next, run the code by clicking on the Run Sub button or using the keyboard shortcut F5 key.

The superior code will change the directory to “C:\Temp”. This is as same as the earlier method.

Output:

As a result, a MsgBox will reveal the current directory by giving a massage Current Directory is: C:\Temp.

Change the Present Folder Using VBA ChDir Function

Read More: How to Use VBA Replace Function in Excel (11 Applications)


Similar Readings:


3. Shift One Directory with VBA ChDir Function

To move up one directory, we can use the beneath statement in our code. The two dots (“..”) in the statement is used to construct a comparative directory.

ChDir ".."

4. Switch Folder by Opening Drive Using VBA ChDir Function

Assumes that, again our default file path is D:\Sabrina Ayon likewise the first method. Now, we want to change the path to D:\ExcelDemy\1111 by opening the drive manually. For this proceed the steps down.

STEPS:

  • Similarly, first, open the visual basic editor by right-clicking on the sheet from the sheet bar and then go to View Code.
  • Next, write down the VBA code here.

VBA Code:

Sub ChDir_Func_Change()
    Dim Folder As Variant
    ChDir "D:\ExcelDemy\1111"
    Folder = Application.GetSaveAsFilename()
    If TypeName(Folder) <> “Boolean” Then
        MsgBox Folder
    End If
End Sub
  • Finally, Run the code by clicking the Run Sub button, on the other hand, press the keyboard shortcut F5 key to run the code.

In the above code, we name the procedure ChFir_Func_Change. And declare the variable name Folder as Variant. We can save the file manually by typing the statement Application.GetSaveAsFilename. By this, the Save As window will appear and we manually save our file where we wish to save.

  • Finally, after running the code, go to the folder D:\ExcelDemy\1111 and click on Save.

Related Content: How to Use VBA Switch Function (6 Suitable Examples)


Things to Remember

  • The VBA ChDir Function can only change the folder/directory of an excel file, this function can’t change the drive of a file. To change a drive we use the VBA ChDrive function.

Conclusion

The above examples are an overview of the VBA ChDir function in Excel. Hope this will help you! If you have any questions, suggestions, or feedback please let us know in the comment section. Or you can have a glance at our other articles in the ExcelDemy.com blog!


Related Articles

Sabrina Ayon

Sabrina Ayon

Hi there! This is Sabrina Ayon. I'm really excited to welcome you to my profile. Currently, I'm working in SOFTEKO as a Excel and VBA Content Developer. I'm a student of United International University and my program is Computer Science and Engineering. I love working with computers and solving problems. I’ve always been interested in research and development. So, here I will be posting articles related to Microsoft Excel. Hoped this may help you. Thank you.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo