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

Columns("B:H").Select
    Selection.Delete Shift:=xlToLeft

I used above code to select columns from B to H and delete it. But its getting select from A to I and deleting all from A-I.

Please advise me code correction.
0
On Demand Webinar: Networking for the Cloud Era
LVL 9
On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Hi

I need to create an Excel CSV file.  This CSV file has a column of large numbers .....  example 2100000000003   .

I wish for the numbers to be represented in full view.   I need to see the full number so that I know that each number is unique.
 Excel insists on changing the  number to scientific notation.  Of course, they all look the same then.

The application accepting this CSV file will read the numbers as text,

Question... How do I save the CSV file so that I open it again,  I will see the full number instead of the scientific notation representation.

Thank you.
0
Hi all,

I'm trying to convert an Excel macro to work in Google Sheets. I have neither Excel VBA nor Javascript skills, so I'm hoping someone can help.

Here is the original Excel macro code:

===========================
Sub RoundRobin()
    Dim x, y, Counted, Number
    Set wsSheet3 = ThisWorkbook.Worksheets("Sheet1")
   
    Number = wsSheet3.Range("B2").Value
    x = wsSheet3.Range("A" & Rows.Count).End(xlUp).Row
    For Each c In wsSheet3.Range(wsSheet3.Range("A2"), wsSheet3.Range("A" & Rows.Count).End(xlUp))
        Counted = 1
        y = wsSheet3.Range("D" & Rows.Count).End(xlUp).Row
        For i = 2 To x Step 1
            If c.Value <> wsSheet3.Range("A" & i).Value And Counted <= Number / 2 Then
                wsSheet3.Range("D" & y + Counted).Value = c.Value
                wsSheet3.Range("E" & y + Counted).Value = wsSheet3.Range("A" & i).Value
                Counted = Counted + 1
            ElseIf c.Value <> wsSheet3.Range("A" & i).Value And Counted <= Number Then
                wsSheet3.Range("E" & y + Counted).Value = c.Value
                wsSheet3.Range("D" & y + Counted).Value = wsSheet3.Range("A" & i).Value
                Counted = Counted + 1
            End If
        Next i
    Next
End Sub
===========================

I need to convert that to Javacript that can be fired off in Google Sheets. Can someone help?

Thanks in advance.
0
I'm trying simplify my example quite a bit here, any help would be greatly appreciated. So lets suppose I have 3 columns by 7 rows. Column A (A1) is named Plan Group, Column B (B1) called Transaction ID, and Column C (C1) called Total Amount Paid. The rows contain the data for all different Plan Groups (from A2:C6). Lets say the Plan Groups are called Microsoft (A2 & A7), Apple (A3), Netflix (A4), Home Depot (A5), At Home (A6).

My goal is to:

1.) Write a macro that will look for a specific Plan Group like Apple, and bring the data over to a new workbook, then save the new workbook as an xlsx based on the name of the Plan Group cell (A2 & A7).

Obviously I have around 145 different Plan Groups in real life (a lot with multiple rows but separate transaction ID's) in which will change a little monthly.
0
I have a macro that compares people on one sheet with people on another. If two people's dates of birth match, all of their info is copied to a new sheet.

Now, i want to automate it further so that on the new sheet (found), if two people's whos D.O.B.'s match, their info is copied to a foruth sheet.
test-V3.xlsm
0
Hi,

Trying to combine the text from columns T and S into column H - ideally leaving a space between the two narratives?

Thanks so much

Pippa

'Copy Comments
Set ProvRng = SrcSh.Range("t10:t" & LR)
For Each Rng In ProvRng
    NextRow = TrgSh.Range("H" & Rows.Count).End(xlUp).Row + 1
    TrgSh.Range(TrgSh.Cells(NextRow, "H"), TrgSh.Cells(NextRow + 11, "H")).Value = Rng
Next Rng

Open in new window

0
Please look at the following VBA code that relates to the attached spreadsheet. The relevant section of this sub is the second paragraph where the first line comment is 'Delete all data in the "TME" tab older than one week. The other paragraphs of this sub work fine as far as I can tell.

This section of the sub refers to the attached sheet, which is the TME tab of a much larger workbook. As you see, the intent of this section of the sub is to delete all records on the TME sheet older than one week from TODAY (the date in column I, Row 1 or relative cell 1,9). For reference, the formula in I1 is =(TODAY()-MOD(TODAY()-1,7))-3. This formula works fine - no need to change it.

The problem is that this second paragraph of the sub below does not actually do any deleting of rows. It also does not stop execution for any errors, but proceeds right on to the next section of the sub without doing anything. I want it to delete all rows from row 2 of the attached sheet and continue deleting until the date in column A is one week older than the date in cell I1.

What needs to be changed to this section of the sub to get it to properly delete the required rows?

Thanks!

Glenn S.
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
Sub sub1()
'Import the daily data from the Cognos extract
Dim rng As Range, region As Range, col As Range
Dim rcount As Long, r As …
0
Hello Experts!

I know a long time question has been how to lock or hide or password protect an Access table.  Since I use a table to hold permissions (username and password), I've been searching for a way to prevent someone from creating a blank access database, then importing tables so they can then see users' passwords.  

It looks like this dude found the answer:
https://www.experts-exchange.com/questions/25193938/Locking-down-an-Access-2007-ACCDE-Database-objects-Tables-Queries-Data.html#a26822729

One of Data-Man's responses: "none of my tables show up in the ‘Import Objects’ window even if the user set the option to view system and/or hidden objects"  is exactly what I'm looking for.

Anyone know how he did it?  Encryption/decryption vba code perhaps?  If so, can anyone send me a sample database?

Thanks All!
0
Hi,
I have an EventsName field , and start date , end date and items lists fields.
I have make a crosstab which display for me the EventsName in a columns and give me as a value a sum of the items  in each EventsName.
as i know there is NO way to edit these value in crosstab.
i make a subform and put the crosstab as a source object . (because the columns in the crosstab is changing dynamically)
is there a way i can connect the main form with the subform of the crosstab that can help me give information from the subform and send to the main form which help editing ?
0
I'm working on a macro that inserts a style separator before the first period in every heading level 2 paragraph. It is also set up to skip any paragraph that is not heading 2. Currently it only works as many times as you specifically trigger it, but I would like it to continue looping until the last style separator is inserted in the last heading 2 paragraph. I can't figure out how to loop this without creating an infinite loop. I'm new to the macro writing game so please excuse my code:

      
 If Selection.Style = "Heading 2" Then
            Selection.MoveUntil Cset:=".", Count:=100
            Application.Run MacroName:="LWmacros.basHNum.InsertStyleSeparator"
            ActiveWindow.Selection.GoTo wdGoToHeading, wdGoToNext

ElseIf Selection.Style <> "Heading 2" Then
             ActiveWindow.Selection.GoTo wdGoToHeading, wdGoToNext

End If 

Open in new window



 I'm not sure what sort of condition I would need to set for the Do Loop for it to end after the last heading level 2 paragraph is found.
0
Free Tool: ZipGrep
LVL 9
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

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

Hi!
I need to programmatically populate a Table with the filenames of a specific folder; a sort of a dir command. How do I do it? Any help will be  really appreciated
0
Have this data need query P4
exact macth
with find button  in column S
 then create the P4 output sheet

SHOW Column  d, E, F, G, H ,I, J,K,L,M,N,O,p.q.s.

13 set
show selectED in yellow BACKGROUND
n4mKKK_2A4.xlsm
0
need clean the data in form
29050695_form_A.xlsm
0
after select week number
29050658--1-BB.xlsm
0
need have an week  in c9 form data
Adriana--8-A.xlsm
0
I have an existing Access 2010 database with a lot of VBA code.  I'd like to be able to add a couple very simple webforms so that users can submit data to the database but I am not sure how to accomplish this.  I know a hybrid access database can be built but I guess I'm concerned that I would have to start with a "web database" and I dont want to recreate this whole thing. Is there a way for me to add a simple web form for use through my company intranet without recreating the whole database?
0
Hello Experts,

I have a query within Access, which I want to have in VBA.

As a query it works fine:
SELECT [2b QD_Key_Tab].Key, [2b QD_Key_Tab].Unrestricted, [2a ZD_Key_Tab].Unrestricted, Round(([2b QD_Key_Tab].Unrestricted-[2a ZD_Key_Tab].Unrestricted),2) AS DifUnrestricted (....more columns :) ) INTO Gesamtabfrage
FROM [2b QD_Key_Tab] LEFT JOIN [2a ZD_Key_Tab] ON [2b QD_Key_Tab].Key = [2a ZD_Key_Tab].Key;

I tried a VBA code:
Dim strGesamtabfrage As String
strGesamtabfrage = "INSERT INTO Gesamtabfrage SELECT [2b QD_Key_Tab].Key, [2b QD_Key_Tab].[Unrestricted], [2a ZD_Key_Tab].[Unrestricted] as [2a ZD_Key_Tab].[Unrestricted], Round(([2b QD_Key_Tab].Unrestricted-[2a ZD_Key_Tab].Unrestricted),2) AS DifUnrestricted, (...more columns :) )" & _
"FROM [2b QD_Key_Tab] LEFT JOIN [2a ZD_Key_Tab] ON [2b QD_Key_Tab].Key = [2a ZD_Key_Tab].Key"
DoCmd.RunSQL (strGesamtabfrage)

And its not woking :(. I don't really understand why. I have similiar select statements done before using a VBA code and it works.
Access says ;): The query must based on a table or query. But both tables are existing.

Can anybody help :(?
0
1-when hit button one time need show 1 with red background (Format)
2- when hit button 2 time need show 2 with red background (Format)

here the file
to add the code

Thanks in advanced
buton.xlsx
0
I use VBA to create custom toolbars for MS Office applications.  For Word 2007, 2010, 2013, and 2016, I've always deployed global templates as a .dotm file in the user's AppData\Roaming\Microsoft\Word\STARTUP folder.  For Excel and PowerPoint, either .xlam or .ppam in the AppData\Roaming\Microsoft\AddIns folder.  

Clients are asking me whether these same tools can be made to work in Office 365 where the applications are cloud-based rather than installed on the local machine.

Do global templates and Add-ins work with the cloud-based versions of O365 applications?  Can they be deployed as-is, or do they require modification, or do they need to be recreated in an entirely new language?  Can anybody point me to some documentation on this topic?

Thanks
0
Introducing Priority Question
LVL 9
Introducing Priority Question

Increase expert visibility of your issues by participating in Priority Question, our latest feature for Premium and Team Account holders. Adjust the priority of your question to get emergent issues in front of subject-matter experts for help when you need it most.

need add an calendar to E9 when i click it
FORM_WORBOOK_N3_1_JJ.xlsm
0
Guys I need help.I need a calendar to my textboxes that in case I will select that textbox it will pull  up the calendar userform.I need it to those date textboxes.I'll be glad if you can help me with this.
hr-form.xlsm
0
Hi Folks,
We have some vba code that opens xls files using an instance of Excel (late binding).
This worked fine with Office 2010 (and prior versions) but with not with Office 2016.
There are no error messages, just a blank workbook.
Navigating to, and opening the file directly from Excel or Explorer works fine.
The code doesn't use any of the optional arguments, and neither 'format' or 'origin' seem applicable.
Any suggestions would be welcome.
thanks
0
I want to be able to create a macro which copy's data from select cells and pastes then into another sheet. I want to be able to keep the existing data from the previous day.  By the end of the week i will have 5 days all in the same sheet.

current macro:

Sub Test()
'
' Macro2 Macro
'

'
    Range("B3:F15").Select
    Selection.Copy
    Sheets("Sheet2").Select
    Range("A2").Select
    ActiveSheet.Paste
    Range("H6:I6").Select
End Sub
0
Can the MS Word Merge field formula be calculated using VBA

Thanks
Imran
0
Greetings,
Can a formula used in MS word merge field be calculated using VBA
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.