[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x

VBA

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

Hello Experts,

Please Guide me how to Protect My Excel VBA Project that Nobody can steal my codes and Working.

Excel Builtin Project Protection is Useless that can easily hack with Hex Editor.

Please input your Expert Opinion and Expertise.

Regards,
0
Keep up with what's happening at Experts Exchange!
LVL 11
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Hi Experts,

I have a field on a form that is Text 7 characters.  The value that must be entered has a pattern of ###.#:1.  e.g. 098.3:1.

The right 2 characters will always be ':1' as this is a ratio.

How do I specify the Pattern Matching (on the form and also using VBA in an Event) to ensure that the left 5 characters are always entered and in the specified format of 3 digits a decimal point and one digit with the right two characters always ':1'.

Thanks,
Bob C.
0
I have a table of data  The table contains data that I need to calculate the distance between two points,. I have the  Latitude, and Longitude of each location  I have a formula that works  well in excel but it is  time consuming as there are  thousands of records to check.   the data looks like the following

table: tbldata
fields  used:  ID(datatype autonumber/integer), LS (datatype text) , Lat(datatype long), Log(datatype Long), status(datatype text),

What is needed is a  way to create a new table,  in the original table are three types of records.
active, pending , and closed  records.

I need  to know the distance between each active record and all other active, pending, and closed records.

the final table  that would be created would look like this.

tblnewdata:  TargetID, TargetLS, TargetLat, TargetLog, CheckID, CheckLS, CheckLat, CheckLog, Distance

the formula I used in excel was as follows.

=6371*ACOS(COS(RADIANS(90-D7))*COS(RADIANS(90-D8))+SIN(RADIANS(90-D7))*SIN(RADIANS(90-D8))*COS(RADIANS(E7-E8)))/1.609

D7 is the Target Latitude
D8 is the Check Latitude
E7 is the Target Longitude
E8 is the CheckLongitude

I had problems running that equation in VBA  it seemed to have trouble with Radians, the other trig elements seem to be fine ie. Acos, Cos, Sin but I did not get it to work yet.

I tried to place the  lats and log in the formula and created a button to run the equation but, it didn't work.

from what I researched it is a nested…
0
team       subteam       user             priority
T              t2                       u1                1
T              t2                       u2                1
T              t2                       u2                1
T                 t2                       u2                1
T             t2                       u1                1
T            t2                       u1            1
T            t2                       u2                1
T             t2                       u1                1
T            t2                       u1                1
T              t2                       u2                1

consider above data I get by applying an autofilter for priority 1 for users u1,u2 belonging to team 2, what I need is a count of the populated rows in column ''priority'' in Excel VBA code. I tried using Subtotal function but I don't want the count generated via subtotal function.
0
I need to show a count in cell J the amount of duplicate records in WORK_ORDERS and show ony unique records, no Dulpicates. I have attached an example of the raw data and one set manually ot the way i need it to look.
Capture.PNG
Capture2.PNG
0
What is the syntax in Word 2010 to attach a macro button to a graphic instead of text?

I've inserted a macro button as a field in the document which works correctly as:

{MACROBUTTON SomeMacroName MyText}

Instead of displaying 'My Text', I'd like to be displaying a built-in macro icon (such as one might attach to a QAT or custom macro in the ribbon) or to clip art or to a graphic stored on the network.

Thx
0
Sample 1Please see image for a details, can I do that by codes or any simple conditions? Any help please Thanks.
SAMPLE.csv
0
I  need to populate templates while using a access form. THis VBA coding was done by an ex-employee, I have made changes to it so that it can use latest data to populate templates. All the queries referred in the code are working fine but somehow procedure creates empty workbooks (no population of data).  Here is some of the coding for reference (I can send you the whole code if you email me at tarrysingh15@gmail.com):
ublic Function ExportRequest() As String
   On Error GoTo err_Handler
   
   ' Excel object variables
   Dim appExcel As Excel.Application
   Dim wbk As Excel.Workbook
   Dim wks As Excel.Worksheet

   Dim sTemplate As String, sWorkbook As String
   Dim TabName As String
   Dim CourtNum As Byte, FY As String, FYnum As Integer
   Dim dbs As DAO.Database
   Dim qdf As DAO.QueryDef, qdf1a1 As DAO.QueryDef, qdf1a2 As DAO.QueryDef, qdf2 As DAO.QueryDef, qdf3 As DAO.QueryDef, qdf4 As DAO.QueryDef, qdf5 As DAO.QueryDef, qdf6 As DAO.QueryDef
   Dim qdf7a As DAO.QueryDef, qdf7b As DAO.QueryDef, qdf7c As DAO.QueryDef ', qdf7d As DAO.QueryDef
   Dim qdf8a As DAO.QueryDef, qdf8b As DAO.QueryDef, qdf8c As DAO.QueryDef, qdf8d As DAO.QueryDef, qdf8e As DAO.QueryDef
   Dim qdf9a As DAO.QueryDef, qdf9b As DAO.QueryDef, qdf9c As DAO.QueryDef, qdf10 As DAO.QueryDef
   Dim rst As DAO.Recordset
   Dim lRecords As Long
   Dim iRow As Long, fundRow As Long, costcenterRow As Long, iCol As Long
   Dim tctfCol As Byte, ntctfCol As Byte
   Dim CourtName As String
  …
0
First, I won't speak about the list of references provided by the system, neither OCX or DLLs (in work environment, you don't always have the luxury to register these), but about references on VBA projects embedded in office documents (wich you can't late bind).

Since browsing the references collection and trying to fix broken references isn't reliable, removing one work, adding a new one work, but refreshing a broken one fail (delete and reload from a valid path), how do you deal with them ?

My last solution is to take adventage of the references lookup process, the application will resolve references by looking:
- The absolute path provided by the references collection.
- The application directory.
- The Windows system directory.
So, I wrote a little MS Access database (more or less "a launcher") that will extract any documents the application will need and launch the application.

Pros:
The launcher ensure that references will be always valid.
Cons:
End users will need to always use the launcher

Discuss.
0
Hellow Experts,

Please kindly guide if there is some way that we can upload file to Google Drive Directly like CDO email.

Regards,
0
Prep for the ITIL® Foundation Certification Exam
LVL 11
Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

I have around 40 files and I need to send each file to a different person using lotus notes.  I want to have the functionality of the code here: https://www.rondebruin.nl/win/s1/outlook/amail6.htm but I need it to work in lotus notes.
0
I'm trying to copy inlineshapes(picture), that picture have shapes over it, using vba to excel.
How can i do it.
0
Situation:

I am preparing to sunset a very old Access 2003 FE/SQL Server 2008 R2 BE data application in the next 6 months. Part of the process will be to convert the data to PDF/A for importation into a new application and to store it for long-term offline archiving.

Problem:

A large amount of this data is stored as embedded OLE objects: Word/Excel 2003 and PDF. It is accessed through a series of ODBC linked tables connected to the SQL BE.

I have been using the S. Lebans ExtractInventoryOLE tool to successfully iterate through sets of related OLE objects and dump them to a folder hierarchy in their native format using Access 2003 but it does not appear to work with Access 2016. I want to get rid of any dependency on Access 2003 ASAP so I would prefer this to work in Access 2016.

The SSGetContents.dll declaration statement that the Lebans solution uses does not seem to be working in Access 2016, even with PtrSafe applied. This is all being done in a 32 bit environment.

Question(s):

1) Is the Lebans DLL and related code compatible with Access 2016?

2) If not, can Access 2016 VBA natively read OLE fields and export or print the embedded documents to PDF/A format?

3) If not, are there any commercial API solutions that will provide me with the tools that I can use to do this? I still need to be able to write code around the extraction and conversion routines so a batch solution without program control may not do the trick.
0
I am doing the same sort repeatedly in the same worksheet, only the rows are changing. I am basically sorting the worksheet section by section (each section being a group of contiguous rows).

The repeated sort is actually 4 similar sorts. All four are sorts of columns D thru R and are sorts by cell color.
1. Sort columns D thru R by cell color (light red, 255, 199, 206) putting light red at the top. 2. Sort columns D-R by cell color (dark red, 192, 0, 0)  puts dark red at top.
3. Sort same columns and put light green (198, 239, 206) on bottom.
4. Sort again (same columns, D-R) and put dark green (79, 98, 40) on bottom.

Caveat: some of the columns, D-R, will not have the given color. I need to check to see if the given color is present in column and if it is not then change to one of the other colors or no color.

 With each sort the rows change, but the rows are always contiguous.

This is what I now have:

Sub ColorSort()
'
' ColorSort Macro
'
    Range("A2341:Y2368").Select
    ActiveWorkbook.Worksheets("Sheet24 (4)").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet24 (4)").Sort.SortFields.Add Key:=Range( _
        "D2341:D2368"), SortOn:=xlSortOnCellColor, Order:=xlAscending, DataOption _
        :=xlSortNormal
    ActiveWorkbook.Worksheets("Sheet24 (4)").Sort.SortFields.Add(Range( _
        "E2341:E2368"), xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue. _
        Color = RGB(255, 199, 206) ' 192, 0, 0 dark red, 198, 239, 206

Open in new window

0
In Microsoft Excel 2013 (and 2010, 2007, 2003, '97) when I closed an XLSM file, it was removed from the Excel VBA Project explorer.

However, in Microsoft Excel 2016, a closed XLSM file remains in the VBA Project explorer.  In fact, even when there is only ONE XLSM file open in Excel, multiple instances of that XLSM file all having the SAME name can appear in the VBA project window!

This can cause serious problems . . . when I'm coding in one instance, when I *think* I'm coding in another.

I have no special Add-Ins.  This problem was introduced solely by upgrading from Excel 2013 to 2016.

How can I remove projects from the VBA Project explorer that are associated with XLSM files that are closed?  Or, better yet, how can I force Excel to automatically remove them from the Project explorer when I close them?

Thank you in advance.
0
Experts:

I have come across multiple posting which dynamically update a tab/sheet name based on a given cell value.   Unfortunately, I have not come across a solution what would address my specific requirement.

Attached XLS contains VBA code where a change in cell A1 updates Sheet1's tab's name from (currently) "Alaska" to the new name.

Here's what I need some help with:
1. I need the same functionality for Sheet1 to also work for additional worksheets.
2. While I could copy the VBA from Sheet1 into each of the other three worksheets, I would have to manually update the cell reference to A2, A3, A4 for Sheet2 through Sheet4, respectively.  I don't want that!
3. Next, current Sheet1 (Alaska) will actually become the master sheet.   That is, I want the values in column A (of Sheet1) drive the values in other worksheets of the spreadsheet.
4. Finally, when adding a new sheet (e.g., "Sheet 5"), I would prefer to automatically have the dynamic capability to change the sheet's name based on my simply adding a new value into A5 (Sheet1) ** WITHOUT ** having to go into the VBA for Sheet5 and make required modifications.

Can the above requirements coded into Excel?  If so, how?

Thank you,
EEH
Dynamic-Worksheet-Renaming.xlsm
0
I have this Access 2010 VBA code that works on computer with Access 2010, but got error on computer with Access 2013 or 2016:

DoCmd.OpenReport "rptArchiveRequest", acViewPreview
DoCmd.OutputTo acOutputReport, "rptArchiveRequest", acFormatPDF, "C:\ArchiveRequest.pdf", False

Open in new window

The error reads:
"Run-time error '2282':
The  format in which you are attempting to output the current object is not available"

If I replace acFormatPDF with acFormatRTF then it works on all versions of Access. But I do need PDF format.
Also, the Intellisense doesn't List Properties/Methods for OutputFormat parameter (on Access 2013 and Access 2016 computers).

Do you know what causes it and how to resolve this error?

Thank you.
0
I know I can get the value of a workbook-scoped Named Range from anywhere. Range("MyRange").Value

But, I have a Worksheet_Change where I have a need to get the value in that Named Range. It crashes.
I can get the value in the Immediate Window, however.

I solved it by explicitly referencing it Sheets("Runtime").Range("MyRange").Value
That worked!

But why? It's a workbook-scoped Named Range!

Also tried making the Worksheet_Change procedure not a Private one. Same problem.

Thanks for any insights on this.
0
Hello,

Please see the attached sample file.  The existing code works which splits out rows into separate workbooks based on an ID in column C.  Part of this code looks at specific IDs and if found groups them together and places them in a workbook called Ions.  

I am seeking assistance on modifying the code to replicate that process so if the macro also identifies rows with 000999 and 000998 then it places them into a workbook called Ions 2 and if it identifies rows with 000997 and 000996 then it places them into a workbook called Ions 3.

Thanks!
EE-Test-FileV2--1-.xlsm
0
Free Tool: IP Lookup
LVL 11
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

I use the code objExcel.Application.Windowstate = -4140 to minimize Excel from Access VBA.

What is the code to maximize?

Thanks.
0
is there a easy way to export text from my excel cells to specific format in a word document.

this should be done automatically when i update excel cells it should update the word.

is there any easy way you know ?
0
how to import multiple sheets from excel into MSAccess using vba.  Each sheet should keep its name
0
I have an excel macro that closes the current work book, then performs some unrelated logging operations.

It works great, but I want to improve the password checking logic.  (This is a much simplified version that illustrates my problem.)


sub  CloseAndSend
With ActiveWorkbook
path = .fullname

  If .ProtectWindows Or .ProtectStructure Then
    MsgBox "This workbook is password protected, please tell the auditors where they can get the password."
  End If
.close
... bunch of unrelated code that plays with outlook

End With

Open in new window


The above code does not catch passwords assigned via Excel's "Password to open" function.  (E.G. assigned with File > Save As > Tools > General options  > password to Open > "mypassword".      

Does anybody have a vba NeedsPasswordToOpen(Fullpath) function that can detect this?   The function should not generate any msgboxes, it should simply return True or False.  

rberke
0
Need to change title case & sentence case to each paragraph in word document if paragraph style is "title_text". But in the title case below conjunction words should be in lower case. Is it possible in C#.net or vb using json.

conjunction words:
that, in, was, a, to, the, then, and, an, it, for

Examples:
Before changes:

In a site looking for the poem. It found quoted poem.

After change to title case:

In a Site Looking for the Poem. It Found Quoted Poem.

After change to sentence case:

In a site looking for the poem. It found quoted poem.
0
Hi,

There are two columns in excel say , A and Z. What i want to do is to check if each cell in column Z matches with the cell in column A and if it matches with the certain cell then replace the offset value of that cell in  column A with the offset  cell value which matches in column Z using excel macros. Please help me out with the problem. Thanks in advance.
0

VBA

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.