Fixed Size Arrays in VBA

            We can describe an array as a data structure which contains numbered list of items. Important fact is that we can refer these items by same name. Due to this reason arrays play major role in any programming language. In VBA, items are normally numbered from zero. But if we need, we can change it to start from different number.

            So this post will give you good understanding about fixed size arrays. I will use below sample data in the explanations.

In VBA we can declare fixed arrays in several ways. For an example let's create fixed array called FirstTenNames.

Dim FirstTenNames(9) As String

So this array can contain 10 items. Here index start from 0. However if you need to start index from 1,  you can declare array as follows

Dim FirstTenNames(1 to 10) As String

So now let's populate this array with first 10 names in column A. You can do it as follows.

Dim FirstTenNames(9) As String Dim WS As Worksheet Set WS = Worksheets("sheet1") FirstTenNames(0) = WS.Range("A2").Value FirstTenNames(1) = WS.Range("A3").Value FirstTenNames(2) = WS.Range("A4").Value FirstTenNames(3) = WS.Range("A5").Value FirstTenNames(4) = WS.Range("A6").Value FirstTenNames(5) = WS.Range("A7").Value FirstTenNames(6) = WS.Range("A8").Value FirstTenNames(7) = WS.Range("A9").Value FirstTenNames(8) = WS.Range("A10").Value FirstTenNames(9) = WS.Range("A11").Value

Now we know how to assign values to an array. What if we need to get values back from an array.
For example think you need to put this 10 names to new sheet (Sheet2) of this workbook. Then you should add following part to end of above code.

Dim WS_New As Worksheet Set WS_New = Worksheets("sheet2") WS_New.Range("A2").Value = FirstTenNames(0) WS_New.Range("A3").Value = FirstTenNames(1) WS_New.Range("A4").Value = FirstTenNames(2) WS_New.Range("A5").Value = FirstTenNames(3) WS_New.Range("A6").Value = FirstTenNames(4) WS_New.Range("A7").Value = FirstTenNames(5) WS_New.Range("A8").Value = FirstTenNames(6) WS_New.Range("A9").Value = FirstTenNames(7) WS_New.Range("A10").Value = FirstTenNames(8) WS_New.Range("A11").Value = FirstTenNames(9)

You will get below result after running the code.

In above example we wrote one line for each item in the array. However it is not practicable in real world as some arrays can contain millions of data. So there should be a better way to populate an array and get data back from it.  One solution is to use for next loops. So let's use for next loop to populate FirstTenNames array.

Dim WS As Worksheet Set WS = Worksheets("Sheet1") Dim FirstTenNames(1 To 10) As String For i = 1 To 10     FirstTenNames(i) = WS.Range("A1").Offset(i, 0).Value Next i

So you can see that we were able to shorten our code by great extent.  Also we can use for next loop to get back the data from the array as well. Add following part to end of above code.

Dim WS_New As Worksheet Set WS_New = Worksheets("Sheet2") For i = 1 To 10     WS_New.Range("A1").Offset(i, 0).Value = FirstTenNames(i) Next i

After running the code you will get first ten names in column A of sheet 2 of the same workbook.
Also it is a good practice to erase the array after using it. You can erase an array using "Erase" keyword. For an example you can erase above array as follows

Erase FirstTenNames

In our above example we used constant values in for next loops. So if we need to change the number of items in array we need to change it in every for next loop in the code. To avoid this we can use

For i = LBound(FirstTenNames) To UBound(FirstTenNames)   instead of    For i = 1 To 10

So we can modify our code as follows.

Dim WS As Worksheet Set WS = Worksheets("Sheet1") Dim FirstTenNames(1 To 10) As String For i = LBound(FirstTenNames) To UBound(FirstTenNames)     FirstTenNames(i) = WS.Range("A1").Offset(i, 0).Value Next i Dim WS_New As Worksheet Set WS_New = Worksheets("Sheet2") For i = LBound(FirstTenNames) To UBound(FirstTenNames)     WS_New.Range("A1").Offset(i, 0).Value = FirstTenNames(i) Next i Erase FirstTenNames

Now we don't need to change the for next loop manually if the number of items in the array changed.

How to Use Scroll Bar in Userform

Some times we need to use very large userforms in our VBA projects. Size of the form depends on the number of fields and various objects we have in our form.  And if we need to show them in one window, we need to create big userform to put them all. You can create a form of any size by giving appropriate values for Height and width in properties window. But if our userform is bigger than the screen we need to use scroll bars inside our form. It is easier to put a scroll bar to a userform. But I saw that many people have problems with setting scroll bars correctly. Lot of people say that their scroll bars not working at run time. So there are few important things you should know when using scroll bars.

I will explain these important facts using a simple example. I have created a large form which has height of 1200 and width of 420. And there are a lot of text fields inside my userform.

If you look at properties window, you will notice that ScrollBars value is set to 0-fmScrollBarsNone by default. So we need to change this property depending on our requirement. If you have a userform with larger width, then you need to set ScrollBars value to 1-fmScrollBarsHorizontal. If you have a userform which has higher height, then you need to set this value to 2-fmScrollBarsVertical. And if you need scroll bars in both directions you can set the value to 3-fmScrollBarsBoth. You need to set to this value only if both width and height of your form is bigger than the screen.

You should set ScrollTop value to the 0. So then it will always appear at top.

Finally you need to set the ScrollHeight. It is very important to set appropriate value to this. Othewise you may not able to scroll up to the bottom of the form. Also if you set higher value, then user will scroll after the end of the objects. So user will see like your form is extended more than needed. So you should set a optimum value for this property for correct functionality and nice appearance. For example if you have a form having a height of 1200, you should set ScrollHeight value around 1850.

Insert Text Box to a worksheet

         Today I'm going to show you how to insert activeX controls text box to a worksheet. Text boxes are very useful controls which we can use in both VBA userforms and Excel worksheets. We can use them for varies things. Text boxes can use to get the input from users. They can be used to show the results of any calculation. Also this object can be linked to specific cell in a worksheet. These are few usages of text boxes.

So if you need to insert a text box to a worksheet, follow these simple steps.

First you need to go to the Developer tab. Developer tab is not displayed by default in Excel application. If you don't know how to show the developer tab, you can learn it from my earlier post

How to show the Developer tab in Excel

If you go to the Developer tab you will see a "Insert" button within the "Controls" group.

Click on that button. Then a drop down list will appear showing two types of controls. Form Controls and ActiveX cControls.

Click on the text box listed under the ActiveX Controls.

After you click on that text box, take your cursor to the worksheet. You will see that your cursor has changed to + mark. Then click on anywhere on the worksheet and create a text box with suitable height and width.

You can drag the text box to any place within the excel sheet. And if you need to change the properties of text box, then right-click on the text box and click on the Properties.

You will see long list of properties related to your newly created text box. You can change any property to suit to your requirements. Also you can follow naming conventions if you like when you give name to the text box. For an example if you use text box to get name of user, you can name the text box as txtName.