Visual Basic Classic





Visual Basic is Microsoft’s event-driven programming language and integrated development environment (IDE) for its Component Object Model (COM) programming model. It is relatively easy to learn and use because of its graphical development features and BASIC heritage. It has been replaced with VB.NET, and is very similar to VBA (Visual Basic for Applications), the programming language for the Microsoft Office product line.

Share tech news, updates, or what's on your mind.

Sign up to Post

Can we export an excel sheet with VBA (i.e, xslm sheet) to sharepoints?

If yes how to export the macro enabled sheets to sharepoint , and how to run from sharepoints?  Can you explain?
Thanks in advance..
Get expert help—faster!
LVL 12
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

I am trying to create a summary report in sheet2. When i enter the employee code it automatically fill the rest of the values from sheet1. But getting error in vlookup. Need help
Good Morning,
I ask you if is possibile to print automatically pdf file "front/back"  by line command  with parameters
I did choose
to open the Excel file, but when saving it, I then have got
Hi Team,

Thank a lot for al the help that your team is doing . I would really appreciate all the help and its worth taking the membership.

I require quick assistance on the below code.  I have the standardised VBA attached below and I would like to make this VBA code in to the display as add in Icon in the Ribbon tool. As of now its not happening. Please help.

Sample image attachedCapture.PNG
Option Explicit

' Global constants
Const strPassword = "password"
Const strFolder = "C:\Cleanup1"

' Global variables
Dim objWord As Object
Dim objExcel As Object
Dim objCommentSheet As Object
Dim intRow As Long

Sub CopyCommentsToExcel()

    ' Constants
    Const xlOpenXMLWorkbook = 51
    Const xlTop = -4160
    ' Local variables
    Dim objFso As Object
    Dim objShell As Object
    Dim objFolder As Object
    Dim objFile As Object
    Dim objCommentWorkbook As Object
    ' Create general use objects
    Set objFso = CreateObject("Scripting.FileSystemObject")
    Set objShell = CreateObject("Shell.Application")
    Set objWord = Application

    objWord.DisplayAlerts = 0

    ' Load Excel, show it
    Set objExcel = CreateObject("Excel.Application")
    objExcel.Visible = True

    ' Create Excel workbook for comments listing, add header row
    Set objCommentWorkbook = objExcel.Workbooks.Add
    Set objCommentSheet = objCommentWorkbook.Sheets(1)
    intRow = 0
    AddComment "File", "Person", "Date", "Comment", "Error"

    ' Access the folder to 

Open in new window

My image

As a person who answers a lot of questions, I often see code that could be simplified, made easier to read, and perhaps most importantly made easier to maintain if the code was modified to use the Select Case statement. This article explains how to do that.

An example

Let’s say I wanted to determine if a variable (in this case a String) contains certain values, so I might write something like this.

If MyVar = "This" Or MyVar = "is" Or MyVar = "a" Or MyVar = "test" Then
                                            MsgBox "I'm interested in those words"
                                        End If

At one time in my programming career when I would write a line like the first one, I’d say to myself “I wish that I didn’t need to repeat the “MyVar =” and could write the following instead.

If MyVar = "This" Or "is" Or "a" Or "test" Then

Experience, however, taught me that I’d get a syntax error. Fortunately I eventually discovered the Select Case statement and I was able to write this instead which is similar.

Select Case MyVar
                                            Case "This", "is", "a", "test"
                                                MsgBox "I'm interested in those words"
                                        End Select

The rest of this article explains how to use the powerful Select Case statement.

The basic Select Case syntax

Select Case < The Expression >
                                            Case SomeCondition
                                                ‘ Do something
                                            Case Else
                                                ‘ Do the default action
                                        End Select


  • For the purposes of this article, “The Expression” is any valid expression that resolves to a string, a numeric value, or a Boolean value. In the case of Excel that includes cell values.  
  • When Visual Basic encounters a Select Case statement, it evaluates the Cases top to bottom, and once it finds that the Case is True it executes the code in that Case and ignores the rest.
  • There can be multiple Case statements
  • The Else case is optional

An expanded version of our Select Case above might look like this.

Select Case MyVar
                                            Case "This", "is"
                                                MsgBox "It’s one of the first two words"
                                            Case "a", "test"
                                                MsgBox "It’s one of the last two words" 
                                            Case Else
                                                MsgBox “Word not found”
                                        End Select

In Case you want to know more

Here are some other Case statements that illustrate the power of Select Case.

Case 1 to 7, 19, > 25

Will be true if the expression resolves to a number between 1 to 7 inclusive, 19, or any number greater than 25

Case Is = "blah"

Same as Case "Blah"

Case "alpha" To "omega"

Visual Basic can spell and any value that is alphabetically between the two (like “great”)  will be true. Be warned however that “Great” is not the same as “great” and the former will not be true. One way to avoid that is to put Option Compare Text at the top of your code module. When you do that all text comparisons will non-case sensitive. Another way is this which compares the upper case versions of the text.

Select Case UCase(Range("B2"))
                                            Case "ALPHA" To "OMEGA"

You can also nest Select Case statements like this example.

Select Case Range("B2")
                                            Case "alpha" To "omega"
                                                Select Case Range("B2")
                                                    Case "coding", "is", "fun"
                                                        MsgBox "I agree"
                                                End Select
                                        End Select

The MsgBox will be displayed if it contains one of those three words.

And what is perhaps my favorite

Remember where I said that “The Expression” could be anything that resolves to a string, a numeric value, or a Boolean value? Well, True (or False) is a Boolean value and so you can do this:

Select Case True
                                            Case Range("B1") = 5
                                                ' Do something
                                            Case Range("C4") = "Done"
                                                ' Do something else
                                            Case Else
                                                ' Do some default action
                                        End Select

And the first one of those things that is true will be executed. I find many uses for that.


For more suggestion about how to write understandable and maintainable Visual Basic code see my article on the subject.

If you find that this article has been helpful, please click the “thumb’s up” button below. Doing so lets me know what is valuable for EE members and provides direction for future articles. It also provides me with positive feedback in the form of a few points. 

If you have any suggestions for improvement or if you encounter any bugs, please send me a message. Thanks!

I have used folder browser dialog function to select a folder from a specified directory. But how can I select a file directly from a folder instead of selecting just the folder?
I want to save the current newly created Excel file like

wbTrg.SaveAs ThisWorkbook.Path & "\Departure List (of People already left).xlsx"

Open in new window

How to avoid the problem that, Departure List (of People already left).xlsx, maybe is already being opened?
I am at my next learning challenge.  I want to be able to attach a Microsoft Excel, Word or picture file and send it to an sql database using Visual Basic 6 as I have a program that is written in that language.  Are there any good code examples that I can start out with?  What components or references do I need to load?   What controls do I use?

Thanks for help in this regards to get me started.  Of course I will have follow up questions to this one I started.
Hi all,

I had a previous question where I needed to search a single folder for the 3 latest files in that folder then copy those 3 files to a new folder - this question got answered and the thread can be found here:

The next issue I have now is:

1) I have a main folder with 20 sub-folders

2) Everyday around 7AM, a new csv extract is added to each sub-folder

2) I need to search through each individual sub-folder and find the latest (the current days) file added to that sub-folder

3) I then need to copy each individual file from its respective sub-folder and place ALL the files in ONE folder - there's no chance of the filenames ever being the same

I found 2 solutions that work in their own way, but I need to "combine" the solutions.

Solution 1: This one will copy ALL files found in a single directory based on the current date to a separate folder

Option Explicit

Dim FolderToCheck, FolderDestination, FileExt, mostRecent, noFiles, fso, fileList, file, filecounter, oShell, strHomeFolder

' Enumerate current user's home path - we will use that by default later if nothing specified in commandline
Set oShell = CreateObject("WScript.Shell")
strHomeFolder = oShell.ExpandEnvironmentStrings("%USERPROFILE%")

'Variables -----
folderToCheck = strHomeFolder & "\Desktop\Terminations"           ' Folder Source to check for recent files to 

Open in new window

Hire Technology Freelancers with Gigs
LVL 12
Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

By Application.ActiveWorkbook.Path

Yesterday I got

but today I had

within Citrix, to the same physical file inside Citrix. I know Citrix can be running physically on different Windows servers. Is there a way to ensure that we can ALWAYS HAVE ONE PHYSICAL PLACE (or path), to one file within Sharepoint?
Hello ;
I have an Excel file with a drop-down list in C7 and the folder name in C9 and the file name in C8.
I have a macro that saves me in PDF page by page with the file name on the correct folder name.
I want a macro that scrolls the drop-down list and saves in PDF each state on its folder with their names.
Folder name: C9
File name: C8
The attachment file
And the two macros.
Thanks in advance. and file attached

manuel macro
Sub PDF_Print()

'Déclaration des variables

    Dim NomDossier As String

    Dim CheminDossier As String


    On Error GoTo 1


    'Nom de dossier

     NomDossier = Range("C9")

     CheminDossier = "P:\COM-RET\COM-RET-PIL\Busines Review\PDF Buisnes Review\" & NomDossier & "\"


    If NomDossier = "" Then Exit Sub


    'Enregistrement au format PDF

    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _

    CheminDossier & "Business Review " & Range("C8").Value & ".pdf", quality:= _

    xlQualityStandard, includedocproperties:=True, ignoreprintareas:=False, _

    from:=1, to:=1, openafterpublish:=False



End Sub
How can we convert footnotes into regular text in a word file using VBA macro?
I have to work on an old legacy desktop application using Visual Basic 6.0

On the app, the grid used to display data is the fpspread control.

Anyone know anything about the fpspread ?

Is that a Microsoft grid control used in old vb 6 apps?
Anyone know a website link for reference on this control on msdn or some other website?
Is it possible to code in VB5 using modern Visual Studio?  I have a potential client that needs work done in VB5 (that's not a typo) and I'm trying to figure out what can be done to accommodate them.

I have a macro that prints via a drop-down list.
I want it to save in PDF on this path D: Mareno and not in .PrintOut
thanks in advance.
Sub Impression()
Dim Liste As String, C As Range
With Sheets("Buisness Support")
Liste = .Range("C7").Validation.Formula1
Liste = Right(Liste, Len(Liste) - 1)
For Each C In Range(Liste)
.[C7] = C.Value
Next C
End With
End Sub
On my form (fm_Inventory) there is a sub form (frm_inventory_sub)
On that sub form there is a field labeled DCN.  
On the After Update Event I have the following code:
    Dim stDOCName As String
    Dim stLinkCriteria As String
    Dim Sid As String
   If IsNull(Me.DCN) Then Exit Sub
    Sid = Me.DCN.Value

    stDOCName = "frm_Inventory"
    stLinkCriteria = "DCN=" & "'" & Sid & "'"
    If DCount("DCN", "qry_Inventory_Dups", stLinkCriteria) > 0 Then
        'Undo duplicate entry
        'Message box warning of duplication
         MsgBox "Warning DCN: " _
        & Sid & " has already been entered." _
        & vbCr & vbCr & "You will now been taken to the record.", vbInformation _
        , "Duplicate Information"
        'Go to record of original Employee
        Cancel = True 'add this line
        DoCmd.Close acForm, "frm_Inventory"
        DoCmd.OpenForm stDOCName, , , stLinkCriteria
    End If

Open in new window

Everything works great up to the end.  It finds the duplicates but when ti goes to open frm_inventory it can't.

I know the link criteria needs to change to open up the hardware with the record with the DCN in it.
I have never done this via a sub form.  I know I would need to add a new variable for the Hardware_ID Field

But how do I tell the code to open the correct record?

I did get
due to Open line below
    File0 = Application.ActiveWorkbook.Path & "\..\Index\" & File1 & ".txt"

    'File0 = ActiveWorkbook.Path & "\..\Index\" & File1 & ".txt"
    Debug.Print Application.ActiveWorkbook.Path
    Debug.Print File0
    Open File0 For Output As #1

Open in new window

I do not know why now Application.ActiveWorkbook.Path is showing one totally different path (not the exact path on which the Excel WorkBook is residing)
I wish to display some images on a windows form in sequence but put a few seconds between them so as to give the user enough time to read them.  However, when I try to use the timer below between each image, the timer runs first at the start of the program instead of between the the image.  How can I get the program to pause between each display instead of at the beginning.

        Dim i As Integer
        For i = 1 To 5
Free Tool: Port Scanner
LVL 12
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

When I use the Plaintext function on a field with HTML-code there are several tracing symbols to the value I am interested in. I tried to filter out the symbols I am interested in (using the Asc() function the recognize the letters and spaces and comma's and such) I get an error number 5 "invalid procedure call or invalid argument". I cloud use en "on error resume next" but I don't like that. Can you help me?

Further to this thread,

what can be the reason of error
due to last ColumnWidth line below
            Application.Workbooks(Window0).Close SaveChanges:=False
            If cnt0 = 1 Then
                Set HOBook = Workbooks.Add
                With HOBook
                    .Title = Title0
                    .Subject = Title0
                    .SaveAs Filename:=File0, FileFormat:=XlFileFormat.xlOpenXMLWorkbookMacroEnabled
                    '.SaveAs Filename:=File0, FileFormat:=xlOpenXMLWorkbookMacroEnabled
                    .Close SaveChanges:=False
                End With

                With Worksheets("Sheet1").Columns("A")
                 .ColumnWidth = .ColumnWidth * 2
                End With

                With Worksheets("Sheet1").Columns("B")
                 .ColumnWidth = .ColumnWidth * 2
                End With

                With Worksheets("Sheet1").Columns("D")
                 .ColumnWidth = .ColumnWidth * 2
                End With

                With Worksheets("Sheet1").Columns("E")
                 .ColumnWidth = .ColumnWidth * 2
                End With

                With Worksheets("Sheet1").Columns("F")
                 .ColumnWidth = .ColumnWidth * 3.5
                End With

Open in new window

I did get
due to SaveAs line below. Why?
    Dim Time0 As Boolean, cnt0 As Integer, File0 As String, Title0 As String, Window0 As String, RowID0 As String, Var1 As String, Var2 As String, Var3 As String, Var4 As String, Var5 As String, Var6 As String, Var7 As String, Var8 As String, Var9 As String, Var10 As String, Var11 As String
    Time0 = True: cnt0 = 1: File0 = Application.ActiveWorkbook.Path & "\Departure List (of People already left).xls": Title0 = "Departure List (of People already left)": Window0 = "Departure List (of People already left).xls"
Rep0:   'Windows(Window1).Activate
    'Debug.Print Window1
    With ThisWorkbook.Worksheets("Main Sheet").Range("AJ1:AJ50000")
        Set c = .Find(Search0, LookIn:=xlValues)
        If Not c Is Nothing Then
            Var1 = ThisWorkbook.Worksheets("Main Sheet").Cells(c.Row, 3).Value
            Var2 = ThisWorkbook.Worksheets("Main Sheet").Cells(c.Row, 4).Value
            Var3 = ThisWorkbook.Worksheets("Main Sheet").Cells(c.Row, 5).Value
            Var4 = ThisWorkbook.Worksheets("Main Sheet").Cells(c.Row, 6).Value
            Var5 = ThisWorkbook.Worksheets("Main Sheet").Cells(c.Row, 7).Value
            Var6 = CStr(ThisWorkbook.Worksheets("Main Sheet").Cells(c.Row, 8).Value)
            Var7 = ThisWorkbook.Worksheets("Main Sheet").Cells(c.Row, 9).Value
            Var8 = CStr(ThisWorkbook.Worksheets("Main Sheet").Cells(c.Row, 

Open in new window

Need to develop a vbscript (can't use PowerShell) to do the following:

Open a website which shows only data, no HTML code
Parse first block of data from it

Sample URL would be 'http://sample/data'

Result would be

Need to extract just the value after the 'F|' and the next '|' so this would be 68.3
I understand its possible to execute a sql statement against an Excel sheet.  Is it possible to execute a sql statement against something that's in memory like a record set or array?
Sorry I still need help linking Excel VBA to pull in a list from SharePoint (I am very unfamiliar with SharePoint).  This code works to pull the list data from SharePoint list, but I need to point to a different list in SharePoint (the list will obviously be in the same format) - how can I do that I cannot see hoe it points to SharePoint apart from To SharePoint reference.  I have attached the file the code is contained within if that helps.  Thanks so much for any guidance.

Sub UpdateIP()
' Unfilter and sort
Call Unfilter
Call EverythingOff

    ActiveWorkbook.Worksheets("Workplan").AutoFilter.Sort.SortFields.Add Key:= _
        Range("E:E"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
    With ActiveWorkbook.Worksheets("Workplan").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
    End With

' Delete MU pasted in values in column D of workplan

' UpdateIP Macro

Sheets("From SharePoint").Activate

With Range("F:G")
  .Replace Chr$(13), vbNullString
  .Replace Chr$(30), vbNullString
  .Replace Chr$(126), vbNullString
End With


    var1 = 13
    var2 = Range("C6").Value


Open in new window


Visual Basic Classic





Visual Basic is Microsoft’s event-driven programming language and integrated development environment (IDE) for its Component Object Model (COM) programming model. It is relatively easy to learn and use because of its graphical development features and BASIC heritage. It has been replaced with VB.NET, and is very similar to VBA (Visual Basic for Applications), the programming language for the Microsoft Office product line.