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

Hi everyone I need help writing a macro to use Excel in-built InputBox to take in 5 integers (one at a time) and store the inputs into an array. Then calculate the sum of these 5 integers and display a message like this:

1.jpg
Any help is much appreciated! :)
0
Cloud Class® Course: C++ 11 Fundamentals
LVL 12
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

I had this question after viewing conditional copy and paste.
plz look into the Actual file
this code is very slow  plz look into it
Actual-File.xlsb
0
Nothing to do with yellow highlighted colour data, highlighted colour is only for understanding purpose in actual file there is no highlighted colour
what I want is to copy the data from B to A
see the sheet2, I have putted the result to sheet2
copy the data from sheet1  as per given condition and paste to sheet2

i have to do all this by vba only
Sample-file.xlsm
0
I have data in yellow highlighted colour area, nothing to do with it
Now what I want is look into G coloumn
Put B to below A that's it
I have to do all this by vba
see sheet2, I have putted the result to sheet2
but I want the result in the same sheet not in sheet2
highlighted colour is only for understanding purpose in actual file there is no highlighted colour
Sample-File.xlsm
0
Grab text from a word file with Excel VBA
=======================================
My requirement is to grab two texts between two flags placed in word file and write in to excel - 2 columns
Flags can be [mydate] and [/mydate]
and [mytext] and [/mytext]

e.g. [mydate] 2018-07-14 [/mydate] blah blah [mytext] this is my text [/mytext]

It should be repeated until all occurrence in the file.
Sub GrabUsage()
Dim FName As String, FD As FileDialog
Dim WApp As Object, WDoc As Object, WDR As Object
Dim ExR As Range

    Set ExR = Selection ' current location in Excel Sheet

    'let's select the WORD doc
    Set FD = Application.FileDialog(msoFileDialogOpen)
    FD.Show
    If FD.SelectedItems.Count <> 0 Then
        FName = FD.SelectedItems(1)
    Else
        Exit Sub
    End If

    ' open Word application and load doc
    Set WApp = CreateObject("Word.Application")
    ' WApp.Visible = True
    Set WDoc = WApp.Documents.Open(FName)

    ' go home and search
    WApp.Selection.HomeKey Unit:=6
    WApp.Selection.Find.ClearFormatting
    WApp.Selection.Find.Execute "[EventDayMth]"
    

    ' grab and put into excel
    Set WDR = WApp.Selection
    ExR(1, 1) = WDR ' place at Excel cursor

   'repeat
    WApp.Selection.Find.ClearFormatting
    WApp.Selection.Find.Execute "[EventDayMth]"
  
    WDoc.Close
    WApp.Quit

End Sub

Open in new window

0
I had this question after viewing combine sheet1,sheet2,sheet3,sheet4,sheet5 into 1sheet.
This is my actual file but it is bigger than this i have deleted 75% of data bcoz i can't upload the file  above 50mb  here
the current code is unable to handle this much of data and it is very slow so plz look into it and do needful
plz see the actual file in the attachment
Actual-File.xlsb
0
Hi all,

The responses are taken from Question 1 and Question 2 's columns respectively

ok.JPG

I have this code and it works as I want it to, since the messagebox values are correct. However, I was hoping that someone can help me delete and edit the messagebox part of the code as I now want to use the array count to make a barchart/histogram.
Sub GetArrayCount()

Dim Ws As Worksheet
Dim LRow As Long, i As Long
Dim r As New Collection, a
Dim DataRng1 As Range, DataRng2 As Range
Dim MyArr() As Variant

Set Ws = Worksheets("Sheet1")
LRow = Ws.Range("A" & Rows.Count).End(xlUp).Row
Set DataRng1 = Ws.Range("D2:D11" & LRow)
Set DataRng2 = Ws.Range("E2:E11" & LRow)
MyArr = Array("Average", "Excellent", "Good", "Poor")

Application.ScreenUpdating = False

For Each a In MyArr
    r.Add a, a
Next a
For i = 1 To r.Count
    MsgBox Ws.Range("Q2").Value & "Q1 " & r(i) & " - " & Application.WorksheetFunction.CountIf(DataRng1, r(i))
   
Next
For i = 1 To r.Count
 MsgBox Ws.Range("Q3").Value & "Q2 " & r(i) & " - " & Application.WorksheetFunction.CountIf(DataRng2, r(i))
Next
Application.ScreenUpdating = True
End Sub

Open in new window



I have attached an example file. Any help is much aprreciated! :)
Book2.xlsm
0
I would like to get some assistance with dynamically changing a form's **multi-select** property through VBA (without having to close/re-open the form).

Current process:
- I have one listcontrol ("ListBoxActions") with 4 tabs on a form
- In the listbox's property sheet, the value for **multi-select** = "None"
- Upon clicking tab 1, 3, and 4, I would like to have the multi-list property set to "1" (single).
- Alternatively, upon clicking tab 2, I would like to dynamically change the multi-list property to "2" (extended).

I have tried two approaches (see below):
           Me.ListBoxActions.MultiSelect = 2   ' Extended

Open in new window


and/or

           Forms("frmLogin").Controls("ListBoxActions").MultiSelect = 2   ' Extended

Open in new window


Unfortunately, neither of the two methods work.   That is, once I click on tab 2, I only can select a single value (vs. multiple values) from the listbox.  

Below is the full code for the listbox.   What am I missing?   How should the VBA be modified to allow dynamic switching of the multi-select property?

Thank you,
EEH

Private Sub TabControlObject_Change()

    Select Case Me.TabControlObject.Value
               
        Case 0
           MsgBox "1st tab"       
           'Me.ListBoxActions.MultiSelect = 1
           Forms("frmLogin").Controls("ListBoxActions").MultiSelect = 1 'Simple
           Me.ListBoxActions.RowSource = "SELECT tblTabControlRowSources.RowSource_Page_1 FROM tblTabControlRowSources WHERE

Open in new window

0
I have created a userform and for question 2 I need help writing a subroutine to:
•      Create an array and initialise it
•      Store the count of “Poor”, “Average”, “Good” and “Excellent” in the array
•      Plot a graph to show the number of count for each rating.

Attached is an example file that I have to work on. Any help is much appreciated! :)
Logsheet-8.xlsm
0
On the sample file attached I have data arranged in columns A thru E on Sheet1.  The column E with field name "Deletion Check" is used to check the status of each row for deletion, if it says "Delete" then that row should be deleted in its entirety. I tried three different VBA procedures and those can be seen in modules to achieve the deletion of all those rows that meet the criteria but all procedures raised error.

On Sheet2 I placed the original sample data so after testing the code data set can be reverted back to its original state again.  One quick note here is that initially I was using the procedure "deleteRowswithSelectedText() " without any issues, the way I was using this code from the button was that I was highlighting the column E then running it and it was working pretty good up until this morning but for reasons that are beyond me it stopped working and after that any other code trial generated error even after I tried on brand new files.  

So if any expert can offer a working code either by modifying one of the existing code blocks or adding entirely new code would be much appreciated.
EE_ROWDELETE.xlsm
0
Cloud Class® Course: SQL Server Core 2016
LVL 12
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

I had this question after viewing Copy the data and paste the data as per given condition(See the  Sample file).
My file is big and this code is unable to do that it works best with less data file so plz look into it
i have attached my actual file
Actual-File.xlsb
0
I am using the command DoCmd.Outputto  and I need to know how to complete the command to identify the individual's name (I have it in a text box on the form + "_DailyReport_" & Todays Date

Docmd.OutputTo acOutputQuery, "qPrintDaily", acFormatXLSX,  Me.User & "_DailyTaskReport_" & Date()

Tried doing it this way but keep getting errors.  How can I make this work?
0
I have got a form that is loading records to a table by endusers.  Each time a new record is created I would like the new record StartTime field to reflect the previous record EndTime value.  At the end of the day all records are loaded to the main table for reporting purposes soooooooooooooo all of the todays records are for today's date.

Currently, the EndTime is reflected in the StartTime field and I need it to be reflected every time the user enters a new record.  How can I code that to always put the EndTime of the previous record in the new record StartTime field?
0
Experts:

I need some help (in Access) where selecting a list box value will open a query with the selected list box value being the ** field name ** (not the query criteria).

Please find attached sample database which contains the following objects:
1. tblFactors (with fields "Age", "Gender", and "Race")
2, Query2 -- executing the query will result in six (6) records with fieldname = [Listbox Field].   Thus, the query should only be executed via the "frmLogin"
3. frmLogin -- includes a listbox with "Age", "Gender", and "Race" being hardcoded

Current Process:
1. Open up frmLogin
2. Click on, e.g., "Age"
3. Step 2 results in Query2 being executed... it now shows 6 records and all of them show "Age"... this doesn't work for me!

Envisioned Process:
1. Open up frmLogin
2. Click on, e.g., "Age"
3. Step 2 results in Query2 being executed... it **should** now should the field [Age] and six records (i.e., 25, 41, ..., ..., 24).

My questions:
1. How should the VBA (in frmLogin) be modified to pass the listbox value as a field name into the query?   How should be VBA be modified to show, e.g., both [Age] and [Race] in the query assuming both were selected in frmLogin?

2. Question #1 is the most important one at this time.   However, ideally, I also would like to have a solution that accounts for having, potentially, up to 150 field in tblFactors.   If so, I would like the listbox to dynamically include all available fields in the listbox.   Is that possible?…
0
Dear Experts:

I got pdf file names along with their full path in Column A of the current worksheet, such as ...
Column A
Header = Path Names
G:\Company\DMD\PM4\product_scissors.pdf
G:\Company\DMD\PM4\product_pincers.pdf
G:\Company\DMD\PM4\product_123.pdf
G:\Company\DMD\PM4\product_456.pdf
G:\Company\DMD\PM4\product_5xy.pdf
etc.
retrieve user names
I wonder whether an Excel Macro is able to retrieve information from the security tab of the file properties of the individual files.
 
 security tab file properties

In specific, I would like to retrieve all the entries that are listed as group or user names on the security tab.
These entries are to be retrieved and enter into Column B, separated by semicolons.

I hope this is feasible. Help is very much appreciated.

Thank you very much in advance.

Regards, Andreas
0
Attached file stores the actual data for my question,  On Sheet1, Column A has Product Category section and under this section I have 5 rows of drop downs with selections via data validation, under Product Name combo boxes were placed.  

The goal here is that after user makes a selection in the first box in the product category section combo box associated with this selection next to it will only show the related products per this selection.  I have completed thus far okay but the combo box depending on the selection is showing the blanks as well.  To get around that issue I created dynamic named ranges using OFFSET function and added them to the list range property of combo boxes.  

Named ranges that I created are ProdBox1 through ProdBox5 and they can be seen in properties of combo boxes as well as via the name manager.  Sources for these ranges are columns F, H, J, L, N columns on Sheet3.  Even though I used OFFSET function to be able to display the dynamically changing ranges to avoid showing blanks on combo boxes  they still do show blanks depending on the selection.  

I believe what is getting in the way here is the way that I show the selection made on Sheet3 in columns E, G, I, K, M.  I did that just so I could apply my INDEX based formula to display the respective value for the repeating name in the list. I simply copied down the selection hopefully enough number of times to capture the material name for each occurrence and obviously this is not a reliable …
0
I'd like to be able click a QAT shortcut to run a macro which opens the current Outlook message in editable mode. And inserts the text "[Edited]" so I can see it has been.

I had a solution for Outllok 2013  Outlook Emails Opening As Editable which no longer works. Sue Mosher's solution at http://www.outlookcode.com/threads.aspx?forumid=3&messageid=31310 also seems kaput (for me).

Any suggestions?
0
I had this question after viewing if a sheet has duplicates rows then put the summary in sheet2.
When i am runing the code(macro) i am getting error message
Run time error '-2147417848  (80010108)':
Automation error
The object invoked has disconneceted from its clients

plz see my sample file and  i am runing the same code  in this file but i am getting error message
sample-file.xlsm
0
I have this exsiting code that works great to import a single sheet from a workbook.  I wanted this code to be slightly tweaked to be able to select my own workbooks; basically two different workbooks, essentially select two different workbooks and combine the sheets of each one into one.

Also the naming for each sheet is always different and the existing code it's looking for the sheet that is called "page" which will never exist in the workbooks I select, so that part of the code would need to change.

See code below:

Sub ImportSheet()
    Dim sImportFile As String, sFile As String
    Dim sThisBk As Workbook
    Dim vfilename As Variant
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Set sThisBk = ActiveWorkbook
    sImportFile = Application.GetOpenFilename( _
    FileFilter:="Microsoft Excel Workbooks, *.xls; *.xlsx", Title:="Open Workbook")
    If sImportFile = "False" Then
        MsgBox "No File Selected!"
        Exit Sub
         
    Else
        vfilename = Split(sImportFile, "\")
        sFile = vfilename(UBound(vfilename))
        Application.Workbooks.Open Filename:=sImportFile
         
        Set wbBk = Workbooks(sFile)
        With wbBk
            If SheetExists("page") Then
                Set wsSht = .Sheets("page")
                wsSht.Copy Before:=sThisBk.Sheets("BEFORE")
            Else
                MsgBox "There is no sheet with name :page in:" & vbCr & .Name
            End If
  …
0
Upgrade your Question Security!
LVL 12
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

When I open a .TXT file in Notepad it reads correctly.

Using VBA, when I read the .TXT file using a TextStream I get characters like: Â+áÏ"ûý

I have tried changing the TriState values and I continue to get these strange characters.

Any suggestions?  Thanks for the help!
0
Hi guys,

I'm new to macros and I'm enhancing this report of mine for efficiency. I was able to create the 1st two modules (for formatting and some computations). This is for the 3rd module and for the final step.

The scenario goes like this:
1. I have a source file (Working file.xlsx). This would be the result from the formatting by the 1st two macros that I have.
2. Column I will be the basis of the number of new workbooks that will saved. So, column I will be filtered and whatever is in it will be saved as a new workbook.
3. The filename is custom for every filtered value. Let's say the values in column I are A, B, and C, the filenames are Apple.xlsx, Banana.xlsx, Cherry.xlsx. (wondering if I could use a lookup table for this one)
4. I'm using Excel 2016, 64-bit.

Hope you could help me with this one. Thanks in advance!
0
I want to convert from sheet1 to sheet2. Sheet1 could have more data.
MMM.xlsx
0
I have an excel spreadsheet that inserts a security code and security description into a table. Clicking a Command Button executes a SQL Server stored procedure for the insert. The issue I am having is that some securities contain a singe quote (Test's) and error when passed to the stored procedure. I want to pass two single quotes (Test''s) to the stored procedure even though the security is entered as a single quote in the cell.

Example: Test’s will give me an error when passed to the stored procedure. Test’’s does not error when passed to the stored procedure.

I have attached a very basic security loader spreadsheet, stored procedure and table creation.

SecurityLoader.xlsm

Table-and-Stored-Procedure-Creation.sql

Code I wish to modify for the single quote to double quote transformation:

Private Sub CommandButton1_Click()
    Dim conn As New ADODB.Connection
    Dim iRowNo As Integer
    Dim sSecurityCode, sSecurityDesc As String

      With Sheets("Securities")
           
        'Open a connection to SQL Server
        conn.Open "Provider=SQLOLEDB;Data Source=YourSQLServer;Initial Catalog=TestDb;Integrated Security=SSPI;I"

        'Skip the header row
        iRowNo = 2
   
        'Loop until empty cell in CustomerId
        Do Until .Cells(iRowNo, 1) = ""
            sSecurityCode = .Cells(iRowNo, 1)
            sSecurityDesc = .Cells(iRowNo, 2)

             conn.Execute "testdb.dbo.uspSecurities '" & sSecurityCode & "', '" & sSecurityDesc & "'"…
0
Hi Experts!

Kindly assist me once me on this , after laboring transferring the 120 tables with the help of SSMA tool all the tables and data type were linked and handled properly,I did the connection by creating DSN File (Example :AccountsFileDSN) via SQL Server odbc 13.1 AND ensured that the READWRITE option was selected in the string.

(1) The second and third sub forms were  grayed and I cannot capture or edit anything, the first sub-form is working normally no issues, all queries, reports and macros VBA are working normally, the performance is also excellent.


How do I make the  second & third sub-forms work as well?


The same forms are working normally in MS access 2016 even now people are using them without problems. Please note this MS Access 2016 to SQL Server 2016 that is where the issue is.

Thank you in advance

Regards

Chris
0
I had this question after viewing Modification of the vba code( it's different).
I think due to symbol my excel is working slow
So now what i want is
Instead of = use E
Instead of < use L
Instead of > use G
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.