I need a function that will convert a number of inches into a standard feet-inches display format. For instance a function that will take 103 inches and convert it into 8'-7".
	How do I get the list of selected items from a multi-select listbox?
	What is a dynamic array? How is it useful?
	How do I declare and use dynamic arrays?
	How do I progressively increase the size of a dynamic array so that no space is wasted?
	Why do I seem to lose data whenever I use dynamic arrays?

I need a function that will convert a number of inches into a standard feet-inches display format. For instance a function that will take 103 inches and convert it into 8'-7".
Keywords: Functions, Conversion
Posted November 26, 1996

The following custom function will make this conversion for you. Note, however, that the output of this function is a string, so you will not be able to perform any calculations on it.

Function ConvertInches(Inches) As String
    Dim Feet As Integer
    Dim Inch As Integer
    Application.Volatile
    Feet = Application.RoundDown((Inches / 12), 0)
    Inch = Inches - (Feet * 12)
    If Feet = 0 Then
        ConvertInches = CStr(Inch) & "''"
    Else
        ConvertInches = CStr(Feet) & "'-" & CStr(Inch) & "''"
    End If
End Function

How do I get the list of selected items from a multi-select listbox?
Posted November 11, 1996
Keywords: Listbox Multi-Select

The list of selected items for a multi-select ListBox is stored in the Selected property of that ListBox. The Selected property returns an array of boolean (True/False) values, one for each item in the list. If the value for a certain item is True then that item has been selected. If the value is False then the item has not been selected. Determining the selected items simply involves looping through the array returned by the Selected property and picking out the true values. Here is an example:

Sub GetSelectedItemsFromListbox()

    Dim oDialog As DialogSheet
    Dim oListBox As ListBox
    Dim sMessage As String
    Dim iCount As Integer
    
    Set oDialog = ThisWorkbook.DialogSheets("daListTest")
    Set oListBox = oDialog.ListBoxes(1)
    sMessage = "Selected Items:" & Chr(13)
    
    If oDialog.Show Then
        For iCount = 1 To oListBox.ListCount
            If oListBox.Selected(iCount) Then
                sMessage = sMessage & oListBox.List(iCount) & Chr(13)
            End If
        Next iCount
    End If

    MsgBox sMessage
    
End Sub
	

What is a dynamic array? How is it useful?
Keywords: Dynamic Arrays Sizing Redim
Posted October 16, 1996

Whenever you have a list of things to process, you use array variables instead of many single variables. When you know the maximum number of elements your list will need to hold, you use static (or normal) arrays as follows:  

Sub TestArray()
Dim szaNames(1 To 10) As String
Dim iCount As Integer

   For iCount = 1 To 10
      szaNames(iCount) = ThisWorkbook.Names(iCount)
   Next iCount
End Sub
	

Dynamic arrays are useful when you do not know in advance how many items will be required to be stored. You declare dynamic arrays in a manner similar to normal arrays but without specifying the size. Later you can use the ReDim statement to size (redimension) the array to the desired size. Example:

Sub TestDynamicArray()
Dim szaNames() As String
Dim iCount As Integer
Dim iMaxCount As Integer

   iMaxCount = ThisWorkbook.Names.Count
   ReDim szaNames(1 To iMaxCount)
   For iCount = 1 To iMaxCount
      szaNames(iCount) = ThisWorkbook.Names(iCount)
   Next iCount
End Sub
	

If you knew that the maximum size required is 100, you could declare a static array and use it. In that case, you would be using memory for 100 names even if you required only 10 names to be stored. Using a dynamic array uses memory more efficiently. The ReDim statement can be used repeatedly to change the size of the array.

How do I declare and use dynamic arrays?
Keywords: Dynamic Arrays Declare Redim Sizing
Posted October 16, 1996

Dynamic arrays help to conserve memory by declaring arrays of the exactly required size. To use dynamic arrays you need at least two declaration statements. The first is the usual Dim statement for an array but without specifying a size. This tells Excel that you need an array whose size you will specify later. At a given point in your code, when you know how many elements this array has to hold, you use the ReDim (redimension) statement to specify the size and then use the array like any other array. The ReDim statement can be used any number of times to resize the array.

To make even more efficient use of memory, you can free the memory used by the array as soon as you are done using its values, using the Erase statement. All these are illustrated in the example code below.

Sub DynamicArray()
Dim iaNumbers() As Integer      ''' Dynamic array of integers declared
Dim iMaxCount As Integer

   '... Some processing here...
   ReDim iaNumbers(1 To iMaxCount)   ''' Dynamic array sized before use
   '... Array value used here...
   Erase iaNumbers()          ''' Done using array. Free up its memory.
   '... Lots of processing here...
End Sub
	

Another technique is to progressively increase the size of the array as an when required. This is explained in the next FAQ.

How do I progressively increase the size of a dynamic array so that no space is wasted?
Keywords: Dynamic Array Resizing Preserve Chunks
Posted October 16, 1996

There are times when you need to resize a dynamic array progressively as you obtain more items to be added to an array. That is, you do not know the required size until you have stored some of the values in the dynamic array. To handle this, you need to use the ReDim statement repeatedly. To ensure that the array is resized without losing the previously stored values, use the Preserve keyword. The example function below shows how to do this.

Dim mszaFiles() As String

Sub TestGetFiles()
Dim szMesg As String
Dim iFoundCount As Integer

      ''' Test call to show how to obtain the list of files in
      ''' the current directory containing "MY" in their names.
      ''' Will return names like "MyData.txt" and "Anatomy1.doc"

   iFoundCount = GetFilesWithX("m")
   szMesg = "Found " & iFoundCount & " file(s)."
   If iFoundCount > 0 Then
      szMesg = szMesg & Chr$(10) & "First file:" & mszaFiles(1) & "."
   End If
   MsgBox szMesg
   Erase mszaFiles
End Sub

    ''' Returns list of file names containing the specified string
Function GetFilesWithX(szLookFor As String) As Integer
Dim iCurrSize As Integer     ''' Current size of array
Dim iTotalCount As Integer   ''' Count of files found
Dim szFileName As String
Const iALLOC_CHUNK = 20      ''' Resize by this many, at a time

   iCurrSize = 0
   iTotalCount = 0
   szFileName = Dir$("*.*")
      ''' Dir$() returns an empty string when it finds no
      ''' more files matching the spec.
   Do While szFileName <> ""
         ''' Check if name contains specificed string.
      If InStr(1, szFileName, szLookFor, 1) > 0 Then
         iTotalCount = iTotalCount + 1
            ''' If current size cannot accommodate, resize
         If iTotalCount > iCurrSize Then
               ''' We need to increase the size of the array
            iCurrSize = iCurrSize + iALLOC_CHUNK
            ReDim Preserve mszaFiles(1 To iCurrSize)
         End If
         mszaFiles(iTotalCount) = szFileName
      End If
      szFileName = Dir$()
   Loop
      ''' Free up any extra space
   If iCurrSize > iTotalCount Then
      ReDim Preserve mszaFiles(1 To iTotalCount)
   End If
   GetFilesWithX = iTotalCount
End Function
	

In cases where the list is not very big, you can call the ReDim statement for each element to increase the array size one by one. The above code demonstates a better way by allocating memory in chunks of 20 at a time.

Why do I seem to lose data whenever I use dynamic arrays?
Keywords: Resize Redim Preserve Dynamic Arrays
Posted October 16, 1996

If you use the ReDim statement to resize a dynamic array, by default, the entire array is initialized. To retain any previously stored values, use the Preserve keyword following ReDim as follows.

Dim DynamicList()

   ReDim DynamicList(1 To 5)
   DynamicList(1) = "One"
   DynamicList(2) = "Two"
   ReDim Preserve DynamicList(1 To 6)  ''' Values are retained
   ReDim DynamicList(1 To 7)           ''' Values are lost
	

Back to Top

Back to the FAQ Table of Contents

Back Up My Hard Drive? How do I Put it in Reverse? :)