Visual Basic for Applications (VBA) enables building user-defined functions (UDFs), automating processes and accessing Windows API and other low-level functionality through dynamic-link libraries (DLLs). VBA is closely related to Visual Basic and uses the Visual Basic Runtime Library, but it can normally only run code within a host application rather than as a standalone program. It can, however, be used to control one application from another via OLE Automation. VBA is built into most Microsoft Office applications.

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

Sign up to Post

In Ms Access I am trying to make a API call to Paypal to try them out. But no matter what I get a Error "0". Does anyone have any idea what the problem is. I have put my code below.

Option Compare Database
Option Explicit
Public Function PayPalGetTransactionDetails()

'On Error GoTo Errorhandler

    Dim Http As New msxml2.XMLHTTP60
    Dim Doc As New msxml2.DOMDocument
    Dim fp1 As String
    Dim fp2 As String
    Dim XmlString As String
    Dim UrlPath As String
    Dim TransactionIdValue As String
    Dim APICALL As String
    Dim UserNameValue As String
    Dim PassWordValue As String
    Dim SignatureValue As String
    Dim SubjectValue As String
    UserNameValue = "Dgstgsgsh_api5.gmail.com" 'API Paypal User Name
    PassWordValue = "647547dfydyudrt" 'API Paypal Password
    SignatureValue = "Afgyfuiyiyifgifyifgyiih" 'Supplied by Paypal.
    APICALL = "GetTransactionDetails"
    SubjectValue = "Dgstgsgsh@gmail.com"
    fp1 = "C:\Database\ExportedOrders\PaypalApiTestRequests\"
    fp2 = "C:\Database\ExportedOrders\PaypalApiTestResponses\"
    UrlPath = "https://api-3t.paypal.com/2.0/"
    TransactionIdValue = "57547564e67744757"
    Http.Open "POST", UrlPath, False
'    Http.setRequestHeader "CONTENT-TYPE", "text/html"
'    Http.setRequestHeader "Username", UserNameValue
'    Http.setRequestHeader "Password", PassWordValue
'    Http.setRequestHeader "Signature", SignatureValue

    XmlString = "<?xml version=""1.0"" 

Open in new window

Get your problem seen by more experts
LVL 12
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

I have a listbox (List4) on a form (frmScholarship_Codes) that contains 2 values, -1 or 0 (true or false).  My form's recordsource is qryScholarship_Codes_RS.  When someone chooses a value from the listbox (List4), I want to change the recordsource to a parameter query, qryScholarship_Codes_PARAM, which filters the form data based on the selection from the List Box.  In qryScholarship_Codes_PARAM, the parameter being [forms]![frmScholarship_Codes]![List4]

Each time I run this query, I get an error.  

This is my code:
Private Sub List4_AfterUpdate()

  If List4 = "" Then
  Me.RecordSource = "qryScholarship_Codes_RS"
  Me.RecordSource = "qryScholarship_Codes_PARAM"
  End If

End Sub

Thank you!  after-update-error.JPG
If there is unhighlighted   data after the highlighted cell in a row then we have to clear all the data from start  till highlighted cell and paste the unhighlighted data from start in the row
and if there is no data after the highlighted cell in a row then simply copy and paste that row don’t do anything with that row
I am struggling with what should be a very simple fix...or so I thought.

I have an On_Change worksheet event in which every once in a while, the Target Range contains multiple cells.

#1 - I have no idea why this is the case, maybe because the worksheet has a lot of merged cells?
#2 - In these instances, I want to change the range to be a single cell range, because I'm performing calculations on it. But I am struggling to figure out how to do this. I tried this
rng = target
   If rng.Columns.Count > 1 Then
    rng = Cells(rng.Row, rng.Columns(1).Column) ' this is the place where I am having an issue; I am not understanding why this is not resetting my rng object to a single cell
  End If

Open in new window

My Main Code in the On_Change Worksheet Event:

If Not Intersect(Target, Range("AA26:AD41")) Is Nothing Then
 Application.enableevents = False
 Call FormatDefectClock(Target)
 Application.enableevents = True
 End If

Open in new window

My FormatDefectClock Code:

Sub FormatDefectClock(rng As Range)
Dim textclock As String
On Error GoTo oops
  ActiveSheet.Unprotect "111" 
  If rng.Columns.Count > 1 Then
    rng = Cells(rng.Row, rng.Columns(1).Column) ' this is the place where I am having an issue
  End If
  If rng.Value2 = Empty Or rng.Value2 = 0 Then 'if data is cleared
    rng.offset(0, 60).Value2 = 0 ' reset the value
    Call ResetFormat(rng)
  End If
  If rng.NumberFormat = "h:mm;@" And rng.Value2 <> "" And rng.Value2 <> 0 Then ' new data has 

Open in new window

Hello! I'm trying to figure out how to get an email to auto-send with a formula, not a manual input. The code below works only if I manually input "PAST DUE" in the cells.
Is there a way to make this work with a formula changing the cell value to "PAST DUE"? I have also added Application.EnableEvents = True to the immediate window.

Option Compare Text

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub
Dim OutApp As Object
Dim OutMail As Object
Dim strto As String, strcc As String, strbcc As String
Dim strsub As String, strbody As String

If Target.Column = 5 And Target.row > 3 Then
    If Target.Value = "PAST DUE" Then
        Set OutApp = CreateObject("Outlook.Application")
        Set OutMail = OutApp.CreateItem(0)
        strto = "sample@email.ccom" 'enter email
        strcc = ""
        strbcc = ""
        strsub = "Overdue Deposits"
        strbody = "Customers with overdue deposits and deposit amount overdue:" & vbCrLf & vbCrLf
        strbody = strbody & Cells(Target.row, "A").Value & " " & Cells(Target.row, "B").Value & vbCrLf
        strbody = strbody & vbCrLf & "Please send email to customer."
        With OutMail
        .To = strto
        .CC = strcc
        .BCC = strbcc
        .Subject = strsub
        .Body = strbody
        End With
        Set OutMail = Nothing
        Set OutApp = …

I'm mainly working with MS Access for my client, and I noticed a reccuring issue: Data import.

This is a major source of troubles, especially when data (usually excel or CSV files) comes from unknown (and unreliable) source, usually they hold bunch of incorrect data, preventing databases from working properly (crash, unhandled data, vanishing data, data replaced by incorrect ones ....... the list of potential bugs is infinite).

So, I wrote a generic library database, who's purpose is to handle data import, validation, convertion, computation, from virtually any data source, to virtually any data source.

Now, I would like to distribute (and why not, sell) this "Framework" (can I call it like this ?), for privacy purpose, I'm thinking about converting it into a COM componnent that VBA projects will be able to set a reference on.

Wich steps should I follow to achieve this goal ?
What about software licence ? And price ?
Hi Team,

I need to open an ERP applicaltion using Hyper link coding, i would like to enter into that applictaion by entering user name and password,

Could you please help me, i tried to link that application but unable to find the linking refrences, Hence used Hyper link function,

Sub OpenApplication()
Dim Path As String
Path = "C:\Users\aravindan.s\Desktop\XYA.lnk"
ThisWorkbook.FollowHyperlink (Path)

End Sub
Hello everyone

I work for a small charity and we provide a courier service in our city. In the past seven years, the data has been saved in an Excel workbook with lots (!) of VBA code.
I am about to leave the company and I have finally succeeded to have everything stored in an Access DB, as I could not guarantee for the data integrity in Excel once I have left. It took me a lot of effort to convince my boss to use Access in the first place. I have now transferred over 40.000 records and I need some advice on the table structure/relationships between them. Here is the model:

Let me explain in detail what we do and then my question:

A customer goes to a supermarket, buys his/her groceries and drops them off at the customer service desk. One of our couriers then goes to a shop and delivers the groceries. Shops are supermarkets, department stores and some smaller specialty shops.  So I record the name of the shop and the customer and save it to Access.  However, we also provide deliveries for flower shops and beverage/wine shops.  As these shops tend to have one-time customers (like flowers sent as a present, or the occasional wine order), we decided not to record these customers. Instead, we set the shop and the customer to be the same and added the delivery address to a memo field in Excel.  Apart from that, shops cannot have deliveries to themselves and rarely can end customers/consumers have deliveries from them them to another place. For …
Within Excel VBA I wish to retrieve the maximum date found when multiple matches are found.   I tried the VBA Function Vlookup and a sort, but it does not work,,,

Lookup Value         Date (Expected returned Value)
Conoco                     01/26/1965
Amoco                     01/26/2005

Table Array
Conoco  01/01/1900
Conoco  01/26/1965
Shell       07/01/1995
Shell      01/26/2005
The 14th Annual Expert Award Winners
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

I need to map network drives to copy a file !  I tried to use the microsoft suggested function getnextletter but am getting errors.
Seems the oDrives is not an object As I get object required at that line!

    Public Function GetNextLetter(DriveLetter)
          'Start x at the ascii value of the drive letter to start the search
       'unless something is passed in. This sample uses capital letters and
       'starts at F.
       If IsEmpty(DriveLetter) Then
        x = 70
        x = Asc(DriveLetter)
       End If
       Set oDrives = wshNet.EnumNetworkDrives

       'Step by two since the first item in the collection is the drive letter
       'and the second item is the network mapping
       For i = 0 To oDrives.Count - 1 Step 2
        If Chr(x) & ":" = oDrives.Item(i) Then
            x = x + 1
        End If
       GetNextLetter = Chr(x) & ":"
    End Function

The VBA that references this function is!

Private Sub Command0_Click()
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set wshNet = CreateObject("WScript.Network")

strSource = GetNextLetter("F")
wshNet.MapNetworkDrive strSource, "\\TQFPRK06\project management\"
strDestination = GetNextLetter("F")     '<-- You can change the letter.  It represents the starting letter to work from.
wshNet.MapNetworkDrive strDestination, "\\TQFPRK06\Tech_drive\"
strSource = strSource & "test.xlsx"

objFSO.CopyFile strSource, strDestination & "\"
Any one can tell me how to find out the table width in percentage using word vba macro.

I need to amend the code below to accept the "F4" key so the Combo Box list drops down and I can use the arrow keys

Private Sub cboTask_KeyDown(KeyCode As Integer, Shift As Integer)
Select Case KeyCode

 Case vbKeyReturn, vbKeyTab, vbKeyUp, vbKeyDown
   KeyCode = KeyCode  'Accept these keys

  Case Else
   KeyCode = 0 'Block all other keys
 End Select

Open in new window

I did choose
to open the Excel file, but when saving it, I then have got
I need to convert the backend database of my application from an mdb to an accdb.  I have about 200 installations so I need to do this as a data conversion in VBA.

I am considering:
  1. Installing an empty accdb containing the tables but no data.  
  2. Then, for each table in the mdb, adding the records to the corresponding table in the accdb.  
  3. Finally, unlink the table in the mdb and relink to the tables in the accdb.

Does this sound reasonable or is there a better way?

Thanks in advance

I need a UDF to compute the mean of Folded Normal distribution.

I am looking for a recomentation for a third party component (as I understand this is not supported with built in one) that would allow a combobox with specific items visible but disabled, ie

 Item a
Item b disabled
 Item c
I wanted to create a Outlook plugin that has a button to mark an email as private, I have found code online to get outlook to create a new email message here https://msdn.microsoft.com/en-us/library/cc668191.aspx
I am struggling to modify it so firstly I can get this code to work off a button on the toolbar and secondly rather than create a new email I want to modify the currently open email and set it to private.
I have macro code to do this but since you have to self sign the macro it is not easy to distribute to other computers.
I am happy for someone to take this up as a freelance job as it should be straight forward and it is my lack of programming skills


I have a subform that have a hyperlink field which open the document to which that Source is link. When clicking on Link path it display Message which i shown in attach image. I have tried using VBA Code as "Docmd.SetWarning False",Also the location is put as trusted Location and look for other stuff also on google but no luck. Can any one help me in this like how to stop showing this message everytime a link is open.

Message: "Some file contains viruses or may be harmful to your computer.  It is important to be certain that this file is from trustworthy source. Would you like to open this file."

Thanks in advance.
Free Tool: Subnet Calculator
LVL 12
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Hello Team

I am currently creating a MS Word mail merge via MS Access vba.

I have the bookmarks set in a word document with the corresponding vba code running in MS Access by looping through a record set.  For example:

If Not rs.EOF Then rs.MoveFirst

Do Until rs.EOF
 wDoc.Bookmarks("CompanyName").Range.Text = Nz(rs!CNAME, "")
    wDoc.Bookmarks("Line1").Range.Text = Nz(rs!Line1, "")
    wDoc.Bookmarks("Line2").Range.Text = Nz(rs!Line2, "")
 wDoc.Bookmarks("Line3").Range.Text = Nz(rs!Line3, "")....etc

All code is working correctly to produce each word and pdf document but what I require is, assistance with  how to remove blank lines produced by blank fields passed from the recordset. For example, there are 6 bookmarks associated with the address detail but if bookmark 'Line3'  is not populated then the address has a blank line space in between the other populated bookmarks.

Can you please advise on how to produce an address block without any blank bookmark line spaces appearing in between?

Thank you in advance.

Query13.txtI need to concatenate values from the same field in Access to read as one long string.  
For every account, our staff enters notes in our system and those notes are saved for posterity on a sql server that is the source of my query.  
I need to the most recent note entered by specific staff members for specific account numbers.  

Each note can consist of multiple lines in our system. This is what a single note looks like when entered in our system by employee 07676:

 And each line is stored as an individual record on the server.  Here is the same note , as stored on our sql server:

One note entered, three lines, three record in the same field that I need concatenated .

Every note entered by the same person, on the same day,for same account, is assigned a [NoteSequence] number which is the field the above three records have in common (in addtion to the account number, employee id, date).

I need to concatenate the values in the [NoteMessage] field  where [NoteSequence]=[NoteSequence]  order by [TransactionSequence]

I need to turn this :
Borrower Intent - Keep Property                  
RFD006 -  Curtailment of Income      

into this:
IB-Inbound .Borrower Intent - Keep Property.  RFD006 -  Curtailment of Income          

TransactionSequence is the order of each line item/record.
NoteSequence is the number assigned to each note made by the same …
We have a macro-enabled .xlsx that:
Creates a new tab in the workbook
pulls data from a website and
pulls specific values  from the newly created tab to populate cells in an exisitng tab

Spoken again.  The Update Data from Server button is supposed to:
Create the FinalData tab in the .xls
Pull data from the MDA server/website
and populate it with the data pulled from MDA site then  
Populate the necessary columns/rows/cells of the Testing Spreadsheet tab with the data from the FinalData tab.
But not all have the same information in the same columns. The “Testing Spreadsheet” tab within the .xls itself should have the same information in all of the same cells across the projects as long as the spreadsheet was not modified by whoever created it. However, the “FinalData” tab that has the results will oftentimes not have the same information in the same columns for each project since the requirements for final testing may vary by project. As a result, the function that auto-populates the cells in the “Testing Spreadsheet” tab needs to call the testing numbers by column name instead of by column location within the “Final Data” tab to make sure we get the correct results.

Can anyone help?

I want to get the median from fairly large sql server tables in Excel VBA using an ADO recordset.  

Getting the median is a pain, even with SQL Server 2012+ (PERCENTILE_CONT)..

I have a simple routine to get the median from an ADO recordset with works fine with small data sets, but would like the fastest solution for larger ones.

Here is my function...

Is there a better way?

Public Function getMedian(sTable As String, sField As String, Optional sDateField As String) As Variant
    Application.Volatile    'to respond to recalc event (F9)
   'this will get the median value for a field, not including zeros
    Dim oRS As New ADODB.Recordset
    Dim oWS As Excel.Worksheet
    Dim iMiddle As Integer
    Dim lRecs As Long
    Dim l As Long
    Dim dblVal1 As Double
    Dim dblVal2 As Double
    Dim sSQL As String
    Dim sSheet As String
    Dim bFilterDate As Date
    'check if there is a db connection... if not kick out
    If Not chkConn Then GoTo exitMe
    'check if activesheet is ByDate... If so, then filter by the dates
    sSheet = ActiveWorkbook.ActiveSheet.Name
    'Debug.Print "activesheet: " & sSheet
    If sSheet = "ByDate" Then
        'check if there is sDateField... If not, kick out....
        If sDateField = "" Then GoTo exitMe
        bFilterDate = True
        Set oWS = ActiveWorkbook.Sheets(sSheet)
    End If
    oRS.CursorLocation = adUseClient
    'On Error GoTo errorMe
    sSQL = "select " &

Open in new window

Can anyone please help me on this?
I am trying to validate the source data in 3 columns BU - Act - Dept in the Range validation Data [BU - Beginning Act - End Account - Beginning Dept - End Dept] (screenshot attached).
BU is a single column in validation table but Act & Dept columns are range columns. I need to check if the BU - Act - Dept combo exists in Range data. So does Power query has any functionality to validate the source data using a nested join? This needs to be done in Excel power query and not formulas because the validation Range file has more than 1 million records.

Please note that Excel Power query is free excel addin which acts as a back end database for Excel.
hello my peers,

i need your help. I have the below code that works on a single file that copy and pastes the required data into "Cost Savings" sheet in the active workbook.

When I have tried to introduce a loop and perform this code on all files in a fodler i cannot get it to work.

Im a novice so excuse the school boy shout out for help!

Sub Import()

    Dim vFile       As Variant
    Dim wbCopyTo    As Workbook
    Dim wsCopyTo    As Worksheet
    Dim wbCopyFrom  As Workbook
    Dim wsCopyFrom  As Worksheet

    Set wbCopyTo = ActiveWorkbook
    Set wsCopyTo = wbCopyTo.Sheets("Cost Savings")

Application.DisplayAlerts = False

    vFile = Application.GetOpenFilename
    If TypeName(vFile) = "Boolean" Then
        Exit Sub
    Set wbCopyFrom = Workbooks.Open(vFile)
    Set wsCopyFrom = wbCopyFrom.Worksheets(1)

    Application.CutCopyMode = False

    End If
Set oneRange = Range("A15:l1000")
Set aCell = Range("A1")

oneRange.Sort Key1:=aCell, Order1:=xlAscending, Header:=xlYes
    wsCopyTo.Range("A2").PasteSpecial Paste:=xlValues, _
            Operation:=xlNone, SkipBlanks:=False, Transpose:=False

wbCopyFrom.Close False

    Selection.NumberFormat = "#,##0.00"
    Selection.NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"
    Selection.NumberFormat = "0.00%"

Open in new window



Visual Basic for Applications (VBA) enables building user-defined functions (UDFs), automating processes and accessing Windows API and other low-level functionality through dynamic-link libraries (DLLs). VBA is closely related to Visual Basic and uses the Visual Basic Runtime Library, but it can normally only run code within a host application rather than as a standalone program. It can, however, be used to control one application from another via OLE Automation. VBA is built into most Microsoft Office applications.