VBA

11K

Solutions

4K

Contributors

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

There is no single "correct" calculation method for quartiles. Excel features two methods, some math and statistic packages offer some more, but here is presented no less than twenty methods for various purposes using VBA and Microsoft Access.
0
Announcing the Winners!
LVL 13
Announcing the Winners!

The results are in for the 15th Annual Expert Awards! Congratulations to the winners, and thank you to everyone who participated in the nominations. We are so grateful for the valuable contributions experts make on a daily basis. Click to read more about this year’s recipients!

To rank something isn't that difficult. Basically, it is just to sort on the values to rank - points, goals, sales, citizens, whatever - in descending order or (typically for time) ascending order. But how to rank dupes? Five methods exist, and they can all be implemented in pure SQL or using VBA.
2
As you can union records, you can join field values. Presented here, DJoin offers increased speed and flexibility compared to the ancient ConcatRelated and similar functions. Further, it offers better read-out of Multi-Value fields.
1
LVL 60

Author Comment

by:Gustav Brock
crystal, if you are interested in other examples using a collection as cache, you can study my RowNumber function here:

Sequential Rows in Microsoft Access
1
LVL 25
thank you, Gustav, I will ... saved the link to look at later ~
0
Rename the worksheets
Excel is a great tool for business and we use it very often in our daily jobs. Sometimes we have been inherited a spreadsheet with a lot of data in which it makes us difficult to search such info. In this case, we need to think of a way to find or tag our data.
0
Microsoft Access Tutorial
A common question in the Access topic area is how to pass values between forms or reports. These threads frequently evolve into a discussion on how to save values so they can be used throughout the application. This article will discuss some of those techniques.
3
LVL 25
nicely written, Dale. Generally, I prefer database properties because they persist, and use a function to retrieve them that also defines them if they don't exist because maybe stuff was imported. Here is my code to set or get a property:
' set or change a database (or object) property
'~~~~~~~~~~~~~~~~~~~~~ Set_Property
Function Set_Property( _
   psPropName As String _
   , Optional pValue As Variant _
   , Optional pDataType As Long = 0 _
   , Optional obj As Object _
   , Optional bSkipMsg As Boolean = True _
   ) As Byte
's4p ... 130410, 160820, 170721
      
   ' PARAMETERS
   ' psPropName is the (database) property name to set
   ' optional:
   ' pValue is the value for the property
   ' pDataType is the Data Type: dbBoolean, dbLong, dbText, ...
   '   if not passed -- uses defaults
   ' bSkipMsg = True: don't give user feedback
   ' obj = database, field, tabledef, querydef,
   '   or other object with properties
   '   if obj is not specified, then CurrentDb is used
   '
   'EXAMPLE
   ' Call Set_Property("AppTitle", sAppTitle, dbText, db)
   '              where: sAppTitle is defined -- or a literal value
   '  call Set_Property("AllowAutoCorrect", true, dbBoolean, oControl)

   'set up Error Handler
   On Error GoTo Proc_Err
   
   Dim booRelease As Boolean
   booRelease = False
   
   If obj Is Nothing Then
      Set obj = CurrentDb
      booRelease = True
   End If
      
   'assume property is defined
   obj.Properties(psPropName) = pValue
   
proc_Done:
   On Error Resume Next
   If Not bSkipMsg Then
      MsgBox psPropName & " is " _
      & obj.Properties(psPropName) _
      & " for " & obj.Name, , "Done"
   End If

Proc_Exit:
   On Error Resume Next
   If booRelease = True Then
      Set obj = Nothing
   End If
   Exit Function
  
Proc_Err:
   'property is not defined
   obj.Properties.Append obj.CreateProperty( _
      psPropName, pDataType, pValue)
   Resume proc_Done
   Resume
End Function

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
'
' get the value of a database (or object) property
' pass (optional) object to look somewhere other than CurrentDb
' pass (optional) default value
'~~~~~~~~~~~~~~~~~~~~~ Get_Property
Function Get_Property( _
   psPropName As String _
   , Optional obj As Object _
   , Optional pvDefaultValue As Variant _
   ) As Variant
's4p 8-9 ... 130831, 160820, 170721
    
   ' PARAMETERS
   ' REQUIRED
   '  psPropName is the (database) property name to return the value of
   ' OPTIONAL
   '  obj = database, field, tabledef, querydef,
   '   or other object with properties collection
   '   if obj is not specified, then CurrentDb is used
   '  pvDefaultValue is value to return if property cannot be read
   '
   'RETURNS
   ' Null (or pvDefaultValue) if property has no value or is not defined
   ' OR
   ' Value of property
    
   'EXAMPLE
   '  MyValue = Get_Property("MyDatabasePropertyName")
   
   On Error GoTo Proc_Err
   
   Dim booRelease As Boolean
   booRelease = False

   'initialize return value
   If Not IsNull(pvDefaultValue) Then
      Get_Property = pvDefaultValue
   Else
      Get_Property = Null
   End If
   
   On Error GoTo Proc_Exit
   
   If obj Is Nothing Then
      Set obj = CurrentDb
      booRelease = True
   End If
   
   Get_Property = obj.Properties(psPropName)
      
Proc_Exit:
   On Error Resume Next
   If booRelease = True Then
      Set obj = Nothing
   End If
   Exit Function
  
Proc_Err:
   Resume Proc_Exit
   Resume
End Function

Open in new window

1
LVL 55

Author Comment

by:Dale Fye
Crystal,  I have similar code, thanks for posting that here.

About the only thing I store as database properties is AppTitle and AppVersion, beyond that, if I want to persist values from one session to another I will either use the registry, or use a table in the backend.

Dale
0
When converting amounts between different currencies, you need exchange rates. This article demonstrates how to obtain these from no less than nine different sources, either for free or - for intense or demanding use - for a fee.
0
Currency codes can be obtained from many sources. The modules here retrieve them from two sources: ISO itself (the definitive source) and Currency Converter API. Both methods will allow you to maintain a table of current currency codes.
1
Google Maps Distance
Many applications need to compute the road distance between two addresses. Numerous websites perform this function, but to build this feature into your application, you must use an API to call these features via the internet. This article discusses how I did so by using Google Maps API and VBA.
1
Charts are easy to insert manually but sometimes we need to automate the chart insertion and most of the people find it difficult to control the chart creation and its properties via VBA In this article, we will learn about how we can use VBA to insert a Chart and manage its appearance.
10
LVL 1

Expert Comment

by:agwalsh
I could see how this could be really useful if you were regularly entering charts - would save a lot of time :-)
1
LVL 4

Expert Comment

by:Skylar-Barlow
Great work!
1
Generating sequential numbers is quite easy, but making them persistent to form updates, deletes, sorting, and filtering takes a little more. Here we will show how to accomplish this in several ways with either little or no code.
0
JavaScript Best Practices
LVL 13
JavaScript Best Practices

Save hours in development time and avoid common mistakes by learning the best practices to use for JavaScript.

Generating random numbers is quite easy, but making them persistent to form updates, deletes, sorting, and filtering takes a little more work. Here we will show how to accomplish this with a few lines of code.
0
“The Microsoft Jet Database engine stopped the process…” while working with a Microsoft Access database. The database may be saved as MDB or ACCDB file depending on the Access version that you used to create the database file.
0
Leaving sensitive information (like passwords) in clear text scripts is never a good practice, though it's sometimes unavoidable. This set of VBScript functions can be used to obscure critical information making it at least a little more difficult for curious eyes (or worse) to see.
1
LVL 100

Author Comment

by:Lee W, MVP
0
LVL 67

Expert Comment

by:McKnife
Things that need elevation will not work that way unless UAC is off, or you use the built-in administrator, Lee, because for that account, UAC is off.  I wouldn't recommend to use it for this purpose for another reason: startup scripts or immediate tasks do the same much easier.
0
It is not that simple from VBA to display a compressed help file as it should be. API calls and a few tricks are needed. This demo and full code will show you how.
0
With the functions here, you can parse, convert, and format back and forth between feet and inches and fractions and decimal inches - for normal as well as extreme values and with extreme precision.
0
Usually, rounding is performed by some power of 10 - to thousands, hundreds, tens, or integer - or to one, two, or more decimals. But rounding can also be done to a power of two, say, 16 or 64, or 1/32 or 1/1024, even for extreme values.
0
Multi-Purpose Knife
This article presents several of my favorite code snippets.
6
What to do if a split doesn't fit? Or a bunch of invoice lines must be rounded while the sum must match a total? It takes a little, but - when done - it is extremely easy to implement.
0
Using Active Directory Groups in Access and VBA -
If you need to implement application level security in an Access database application or other VBA code, I strongly encourage you to take advantage of Active Directory groups.
1
Python 3 Fundamentals
LVL 13
Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

Manually copying shapes and their assigned macros one by one to a new location can be tedious, but if you use the Excel utility workbook attached to this article, the process will be much quicker and easier.
0
A quick solution showing how to control and open a POS Cash Register Drawer using VBA with MS Access.
0
Microsoft Access
Implementing simple internal controls in the Microsoft Access application.
0
LVL 60

Expert Comment

by:Gustav Brock
Please edit the article to hold the code in code blocks with normal indention to make it readable and allow for copy-paste.
0
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, this article addresses the 'improvement'...
0
Print Preview
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
0
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
0

VBA

11K

Solutions

4K

Contributors

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.