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.

Add Data Analysis Command to Data tab in Excel 2013

          Analysis tool pack is very usefull tool which you can use for complex Engineering and statistical analysis. This add-in is available after you install the Microsoft Excel. But you need to load it first to use in Excel application. In this post, I will explain how to load this usefull tool. Follow these simple steps.

First, click on the File tab.

Click on options.

You will get the "Excel Options" box. Select the Add-Ins.

Once you select the Add-Ins, You will see "Analysis ToolPak" under Inactive Application Add-ins. Select it and click on "Go" button below.

Once you click on the "Go" button you will get below box. Check "Analysis ToolPak" and click on "OK" button

Now you can find "Data Analysis" command under "Data" tab.

So now you can do data analysis like Regression, Fourier Analysis etc. using this Data Analysis command.

How to transpose Values in a column to a row using VBA

          Today I'm going to create very simple macro to transpose values in a column to a Row. Assume we have names of the countries of the world in column A. So I have 257 country names in this column A. What I need to do is transpose these names to 1st row.  Here is a image of my current worksheet.

So we are going to put all these country names to row 1 using a simple macro.

First we need to open the VBA editor. Let's use shortcut keys for that. Press Alt+F11

You will get a window similar to below.

Click on Insert == > Module

Then Copy paste below code to coding area.

Sub TransposeData() Dim WS As Worksheet Set WS = ActiveSheet columnNumber = 1 For i = 1 To 257     Cells(1, columnNumber) = Cells(i, 1).Text         'Increment column number by 1     columnNumber = columnNumber + 1 Next i MsgBox "Completed" End Sub

Then click on any line between two lines Sub TransposeData( ) and End Sub to select the macro.

Then click on the run button.

You will get the following result.

Then you can delete the values in column A except cell A1.

So now let's consider about the code

Dim WS As Worksheet

This defines the worksheet variable

Set WS = ActiveSheet

So we have set active sheet to the variable WS

columnNumber = 1

This defines where we need to start entering the values. So here we order to insert from column 1 to column 257. We can change the number depending on where we need to enter our first value.

For i = 1 To 257 Next i

We used for next loop to go trough all the rows of column A

Cells(1, columnNumber) = Cells(i, 1).Text

This takes value from current cell of column A and assign it to relevant column of row 1.

columnNumber = columnNumber + 1

This line increment the columnNumber by 1. So next value of column A will be entered in next column of row 1.

Insert several images to a worksheet using VBA

    In an earlier post I explained how to insert one image to an Excel worksheet. If you want to learn how to insert one image you can go to that post from below link.

Insert an Image to Excel Worksheet using VBA

    Today's post is about how to modify that code to insert several images to a sheet. So I'm going to create very simple template to explain this. We will not have any text description or content in our sheet. This will only have images. But you can modify it to create your own template with lot of images, descriptions etc.

Assume below is the sheet where we need to insert our images.

And I have all images I want to insert in one folder. I have put them all to one folder so I can easily input file paths. But you don't need to put the all images to one folder.

So we have five images to insert. In our earlier post about inserting one image, we put the file path inside the code. Like that we can put paths of All the images in the code. But then it is not user friendly. Because our file may used by a person who doesn't know anything about VBA. So what we going to do is add another sheet call "File Paths" to this work book. And specify a column to put file paths of relevant images.

So now it is not a problem even our user doesn't know anything about VBA. Also we can put a button in this sheet to call our macro. So once the paths are entered, user can click that button. Then images will be inserted automatically to template sheet. And user will be directed to that sheet.

So now we need to develop a macro to do this. Actually this is much similar to code we used for insert one image. What we need to do is add a looping method to get paths of files one by one. So here is the full code.

Sub InsertSeveralImages() Dim pic_Path As String 'File path of the picture Dim cl As Range, Rng As Range Dim WS_Templte As Worksheet Set WS_Templte = Worksheets("Template") Set Rng = Worksheets("File Paths").Range("C3:C7") pastingRow = 2 For Each cl In Rng     pic_Path = cl.Value     Set InsertingPicture = WS_Templte.Pictures.Insert(pic_Path)         'Setting of the picture     With InsertingPicture         .ShapeRange.LockAspectRatio = msoTrue         .Height = 100         .Top = WS_Templte.Rows(pastingRow).Top         .Left = WS_Templte.Columns(3).Left     End With         pastingRow = pastingRow + 8           Next cl         Set myPicture = Nothing WS_Templte.Activate End Sub

Below is a brief explanation of the code.

First we need to define the variables

Dim pic_Path As String 'File path of the picture Dim cl As Range, Rng As Range Dim WS_Templte As Worksheet

We use "Template" sheet to insert the images.

Set WS_Templte = Worksheets("Template")

And Range("C3:C7") of "File Paths" sheet is the range where user put the file paths of the images.

Set Rng = Worksheets("File Paths").Range("C3:C7")

We use below line to define the row we going to insert our first image.

pastingRow = 2

pastingRow  value should be incremented by suitable amount before go to next loop to give space to insert  images. So we have put space of 8 rows in this example to insert one image. And you can notice that I have used below line before go to next loop.

pastingRow = pastingRow + 8

And we have used for each loop in this example to insert each and every image.

For Each cl In Rng         Next cl

And there is a coding part inside that For each loop. I have explained that part form my earlier post.

WS_Templte.Activate

And above line will direct the user to sheet where the images are inserted.

Split Function

             Split function is a very useful function in VBA. We can use this function to split a string in to sub-strings. Function will return these sub-strings in an array.

There are four parameters in this function

1. Expression
2. Delimiter
3. Limit
4. Compare

First parameter is required and other three are optional.

I will explain how to use this function by simple examples. Assume that we have a string like "a,b,c,d,e,f,g,h,i,j" and need to split this string at every occurrence of  ","
And then need to store those sub strings in a column.

So first we need to define a string array

Dim WrdArray() As String

This array will store the sub-strings once we split our input string.

And we need to define a string variable to hold our input string.

Dim text_string As String

Then we will assign our input string to that string.

text_string = "a,b,c,d,e,f,g,h,i,j"

After that we can use Split function as follows

WrdArray() = Split(text_string, ",")

Now sub-strings are stored in this WrdArray array. So what we need to do now is retrieve those sub strings from that array and store them in a column.
Below code segment will do that. Sub strings will stored in column A.

For i = LBound(WrdArray) To UBound(WrdArray)     Range("A" & i + 1) = WrdArray(i) Next i

Array we get (WrdArray)  is a zero-based, one dimensional array. So LBound(WrdArray) is equal to 0. That's why we used the line Range("A" & i + 1) in above code

And below is the complete VBA code of the above example.

Dim WrdArray() As String Dim text_string As String text_string = "a,b,c,d,e,f,g,h,i,j" WrdArray() = Split(text_string, ",") For i = LBound(WrdArray) To UBound(WrdArray)     Range("A" & i + 1) = WrdArray(i) Next i

If you run it, you will get  below result

In above example we used "," as our delimiter. What if we omit the delimiter string

Here is an example.

Dim WrdArray() As String Dim text_string As String text_string = "This is a example string." WrdArray() = Split(text_string) For i = LBound(WrdArray) To UBound(WrdArray)     Range("A" & i + 1) = WrdArray(i) Next i

We haven't use any delimiter here. So if we omit the delimiter, Split function will assume Space character (" ")  as delimiter.
So you will get the below result.

Third parameter of Split function is limit. Default value is -1. However if the limit is greater than zero, then the string will be divided in to sub-strings at first (Limit-1) occurrences. So the number of sub strings will be equal to the Limit. Below subroutine will give you clear idea about how it works.

Sub Example_Limit_Parameter() Dim WrdArray() As String Dim text_string As String text_string = "This/is/a/example/string." WrdArray() = Split(text_string, "/", 3) For i = LBound(WrdArray) To UBound(WrdArray)     Range("A" & i + 1) = WrdArray(i) Next i End Sub

Here is the result

Compare is the last parameter of the this function. It defines what kind of comparison should be done by the split function. It can have only two values. Either CompareMethod.Binary (0) or CompareMethod.Text (1).  You can use CompareMethod.Binary for case sensitive comparisons and CompareMethod.Text for case in-sensitive comparisons.

Insert an Image to Excel Worksheet using VBA

         Are you working with excel templates in your office life. Then you might able to automate some of your time consuming tasks with VBA. If you go through the posts of this blog you will find lot of resources to improve your workbooks. And if you are new to VBA start with this post.

Getting started with Visual Basic Editor in Excel

 In today's post I will explain how to insert images to your excel sheets and how to set their properties.

So let's begin with a simplest example. I have a worksheet with no data. Think I need to insert below image to this worksheet using a macro.

Actually we can do this with just one line of code.

Set myPicture = ActiveSheet.Pictures.Insert("C:\Users\Mel\Pictures\Photo-0625.jpg")

You should replace the image path of above code with relevant path of your image.
So here below is the result we get.

As you can see this is not in a very useful form. Because image is in it's original size. Also we should have the control to insert image where we need.

So we need to improve our code like below.

Set myPicture = ActiveSheet.Pictures.Insert("C:\Users\Mel\Pictures\Photo-0625.jpg") 'Setting picture properties With myPicture     .ShapeRange.LockAspectRatio = msoTrue     .Height = 250 ' Set your own size     .Top = Rows(2).Top     .Left = Columns(2).Left End With

Value of Left and Top define the place where this image will insert. Also image will be re-sized to the height we given in the code. Value of .ShapeRange.LockAspectRatio determine whether to keep aspect ratio or not.
Below is the result of above code.

And there might be situations, where we need to insert our image to a empty space located middle of some other content. In that case we can't keep our aspect ratio and we need to set height and width to match with that free space. But if the ratio of height and width we set have higher deviation from that of original image, this can have big impact on the appearance of the image. But small deviation won't be visible at all.

Below is the code where both height and width are defined.

Set myPicture = ActiveSheet.Pictures.Insert("C:\Users\Mel\Pictures\Photo-0625.jpg") 'Setting picture properties With myPicture     .ShapeRange.LockAspectRatio = msoFalse     .Height = 250     .Width = 300     .Top = Rows(2).Top     .Left = Columns(2).Left End With

And here is the result.

In this post I explained how to insert only one image to a excel sheet. But if we need to insert one image we can do it manually. So this method is useful only if we have lot of images to insert to our worksheet/worksheets. So I will explain how to insert several images to a worksheet with a click of a button in another post.