[SOLVED] Excel VBA - next aviable drive function

Excel VBA - next aviable drive function

I have a sub that can map the shared c: drive of pc-s from an excel list, part of this script is a function that can return the next available drive. It works fine, but there are some small bugs that I would like to fix.

This function's job is to check for the next available drive letter and return it for the main sub. The function's only problem is that after it has finished with the letters 'D:',...,'Z:' the next letter it returns is '['. I would like to give an error message, that there are no more available drive letters after the last one.

Private Function NextAvailableDrive() As String

Dim iDrive As Integer
Dim iFirst As Integer
Dim iFirstFree As Integer, sFirstFree As String
Dim sNextDrive As String

iDrive = 67 'starts looking at D:\

      Do
         iDrive = iDrive + 1
          sNextDrive = Chr$(iDrive) + ":"
           iFirstFree = GetDriveType(sNextDrive)
       'Function returns 1 if drive is available
        Loop Until iFirstFree = 1

      sFirstFree = Chr$(iDrive) + ":"
      NextAvailableDrive = sFirstFree
End Function

Thank You

Solution

Just put code in your Do loop to check if i > 90 (letter "Z") and throw a message.