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

I have an Excel sheet that I am trying to find and hide rows where cells in column A contain the symbol <.
The problem is that the cell values are defined as one long string, and it is not finding the symbol by it's self.
For instance, in the following data:

1 Random Text
2 Also Random Text
3 <p>Another Random Text Also</p>
4 More Random Text
5<p>Last Random Texts</p>


The code is supposed to resolve this into:

1 Random Text
2 Also Random Text
4 More Random Text


This is what I have so far:
Sub Button1_Click()
Dim N As Long, I As Long, j As Long
N = Cells(Rows.Count, "A").End(xlUp).Row
j = 2
For I = 2 To N
If InStr(1, cell, "<", 1) then
ActiveCell.EntireRow.Select
Selection.EntireRow.Hidden = True
Else
ActiveCell.EntireRow.Select
Selection.EntireRow.Hidden = False
EndIf
Next I
End Sub

Open in new window

0
Want Experts Exchange at your fingertips?
LVL 9
Want Experts Exchange at your fingertips?

With Experts Exchange’s latest app release, you can now experience our most recent features, updates, and the same community interface while on-the-go. Download our latest app release at the Android or Apple stores today!

how are the steps to put this in any file i need
DATADATE--10-RR.xlsm
0
I have a template that is used monthly and each time its used, we create a new one for the upcoming month.
On several worksheets, we need to unprotect it, clear data from the entry fields, and then re-protect the workbook before saving.  I want to set up a button that will allow the end user to click it and have the macro perform unprotecting the worksheet, clearing the data and then re-protecting all with one click.  I don't mind the password showing in the code because I don't want the end user to have to enter a password to do this.

Do you have some code for this?  Here is an example of the data clearing code I will be using.  I just need to know how to attach code at the beginning and end to initially unprotect the worksheet and then protect it again after the data clearing has been done.

 Range("A36:A37").Select
    Selection.ClearContents
    Range("C34:C37").Select
    Selection.ClearContents
    Range("C40:C43").Select
    Selection.ClearContents
    ActiveWindow.SmallScroll Down:=39
    Range("C73:C76").Select
    Selection.ClearContents
    Range("C79:C82").Select
    Selection.ClearContents
    ActiveWindow.SmallScroll Down:=24
    Range("C92:C95").Select
    Selection.ClearContents
    Range("C98:C101").Select
    Selection.ClearContents
    ActiveWindow.SmallScroll Down:=-75
   
End Sub
0
I have set up my connection to an Oracle Database in Excel and the Command Text in the definition tab of the Properties window says 'SELECT * FROM "PS_OWNER"."PS_JOB"' which returns too much data for an excel spreadsheet. So my question is, can I use VBA to return the data the same way I return it in SQL Developer?
0
I need help with a Word Macro which will search for the word "apple" in a word document and change the font color of all the instances of the word ("apple") to green.
Thank you for your help.
0
Hi. I am running the following event procedure which worked in another part of my database but is now returning a value of "-1" to my field in this case "ProjectTitle" which is a text field. My code is below:
 

Private Sub ProjectTitle_AfterUpdate()

    Me!ProjectTitle = True
    DoCmd.RunCommand acCmdSaveRecord
        
    Dim strProjectTitle As String
    strProjectTitle = Me.ProjectTitle
    Me.Requery
    With Me.RecordsetClone
        .FindFirst "ProjectTitle = " & Chr(34) & strProjectTitle & Chr(34)
        Me.Bookmark = .Bookmark
    
    End With
    
  

End Sub

Open in new window


Anybody know why?
0
I can't seem to find this in my search. I simply have a table or range in my excel spreadsheet and I'm looking for a VBA code to insert the data into SQL Server.  I don't want to use any integrated services or an OPENROWSET in SSMS since this will be a spreadsheet in a shared folder.
0
is any way if im in the mid of the sheet (have a lot of records)
to move  fast to the last row of data with an button !!
AND TO  THE TOP AS NEEDED !

ALSO HAVE TO SAY THE TOP FOR MY DATA BEGING IN ROW 46
(NOT ROW 1)
DATADATE.xlsx
0
Hi,
I have been trying to link each page in my multipage userform to a different worksheet. I am stuck here. I have tried Select Case and If Statements with no luck. I would really appreciate any help with this.
Thank You,
Steve
0
The code below opens Google Chrome

When I run it with Google Chrome already open, it adds an extra tab and it works as expected.

If Chrome is not yet open when I run it, it opens Chrome (as expected) however then the SetForegroundWindow line doesn't work, it doesn't bring google to the front.

(Google Chrome needs to be installed on your computer for it to work and the program uses windows location, ie you'd need to edit the folder "chromePath" for Mac and Linux)

'findwindow
Public Declare Function FindWindowEx Lib "user32" Alias "FindWindowExA" (ByVal hWnd1 As Long, ByVal hWnd2 As Long, ByVal lpsz1 As String, ByVal lpsz2 As String) As Long
Const CB_FINDSTRINGEXACT = &H158
Const CB_SETCURSEL = &H14E
Private Declare Function SendMessage Lib "user32" Alias "SendMessageA" (ByVal hWnd As Long, ByVal wMsg As Long, ByVal wParam As Integer, ByVal lParam As Any) As Long
Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As Long
Private Const CB_GETCURSEL As Long = &H147
Private Const CB_GETLBTEXT As Long = &H148
Private Const CB_INSERTSTRING As Long = &H14A

'setforeground window
Public Declare Function SetForegroundWindow Lib "user32" (ByVal hWnd As Long) As Long

'getactivewindow
Public Declare Function GetActiveWindow Lib "user32" () As Long






Function IsFile(ByVal fName As String) As Boolean
'Returns TRUE if the provided name points to an existing file.
'Returns FALSE if not 

Open in new window

0
Enroll in July's Course of the Month
LVL 9
Enroll in July's Course of the Month

July's Course of the Month is now available! Enroll to learn HTML5 and prepare for certification. It's free for Premium Members, Team Accounts, and Qualified Experts.

Hello Experts -

     For some reason the code that I have is not fully minimizing outlook when the macro is engaged.  When I click the button, I'd like just the macro its self to remain and to minimize Excel completely in the background.  Where am I going wrong with my code?

Sheet 1:
Private Sub btnFrmopen_Click()
ActiveWindow.WindowState = xlMinimized
UserForm1.Show vbModeless

End Sub

Open in new window


Module 1:
Private Declare Function GetForegroundWindow Lib "User32.dll" () As Long

Private Declare Function GetWindowLong _
  Lib "User32.dll" Alias "GetWindowLongA" _
    (ByVal hWnd As Long, _
     ByVal nIndex As Long) _
  As Long
               
Private Declare Function SetWindowLong _
  Lib "User32.dll" Alias "SetWindowLongA" _
    (ByVal hWnd As Long, _
     ByVal nIndex As Long, _
     ByVal dwNewLong As Long) _
  As Long

Private Const WS_THICKFRAME As Long = &H40000
Private Const GWL_STYLE As Long = -16

Public Sub MakeFormResizable()

  Dim lStyle As Long
  Dim hWnd As Long
  Dim RetVal
  
    hWnd = GetForegroundWindow
  
    'Get the basic window style
     lStyle = GetWindowLong(hWnd, GWL_STYLE) Or WS_THICKFRAME

    'Set the basic window styles
     RetVal = SetWindowLong(hWnd, GWL_STYLE, lStyle)

End Sub

Open in new window


Your suggestions are greatly welcomed! Thank you.
0
Need help with code.   I used Conditional formatting to illistrate the color coding for duplicates. I would like vba to indicate duplicate in any column then is it possible to indicate which columns by header are duplicated.   Example A4221- DMEBASE, DEMBASKX.

Format for the example is a quick glance of which codes and the column by headername where it is found.  

Conditional formatting can be removed if VBA is handling it all.

Thank you..
DME---CPT-match-Report.xlsm
0
LIKE SHOW HERE !
 YYYYYYYYYYYYYYYYBBBBBBBBBBBBBBB.png
0
to count the columns

also need repair the clean button in INDATA sheet
and clean button in TEMPDATA sheet
n4hT.xlsm
0
Need buttons

P4ALLW,, PAIRALLW, RGALLW
n4hPA--1---2-.xlsm
0
need buttons  

P4WK2,  PAIRWK2,  RGWK2
0
to this file buttons

P4 WK1

PAIR WK1

RG WK!
n4hPA--1-.xlsm
0
There is a tool that we use for data grab.
Built in Excel (VBA).
When I try to open it on my 64 bit (Excel 2013) machine I'm getting compile error.
Please see attached screen shot.

Thanks,
MichMichCompile Error screen shot
0
need to add titles as data  in TEMPDATA sheet

here the file
n4hPA.xlsm
0
[Webinar] How Hackers Steal Your Credentials
LVL 9
[Webinar] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

I have a spreadsheet where I want to be able to automatically generate 3 additional columns if the day=Sun.

 What the spreadsheet does is track attendance and on Sundays I need to track Spanish Sunday School, Spanish Service, English Sunday School, and English Service. All other days I only have to track a single item.

 Since days of the week move based on the date, ie Sunday the first is not always the case it may be Sunday the 2nd, the creation of the new columns would move as well.

 I have attached a sample of the spreadsheet and month 1 and the first Sunday shows what I am trying to accomplish.

 Is that possible? And if so how would I go about it.
Attendee-List-Template-2.xlsx
0
need copy data to

3PALLW, PAIRAALW  and RGAALW

to destination file
n3allH--14-G.xlsm
0
Hi! I'm having some trouble with some code I am trying to put together. I am very much a beginner, so this could be a really simple problem. Basically, I have a subform that uses information from the parent form and and another subform to create an email. I am not having any issues generating the email, but what I want to do is create body text using a text field in the subform only if a related field is "true" (it's a yes no field).  Right now I have

  l_Msg.HTMLBody = "<!DOCTYPE html><body style=""font:normal normal normal 11pt/11pt calibri;"">" & _
  "<div>Hi" & " " & Me!PitchCastingFirstName & "!<br><br>" & _
  "I'd like to submit" & " " & UCase((Parent!FirstName) & " " & Nz(Parent!LastName)) & " for the role of " & Nz(Me!PitchRole) & "." & _
  " " & Parent!ClientPitchParagraphTbl_subform.Form!PitchParagraph & _
  "</div></body></html>" _

Open in new window


I want Parent!ClientPitchParagraphTbl_subform.Form!PitchParagraph to only be used if the related field "active" is set to "true".  Do I have to create a string or is there a simple declaration?

Thanks in advance.
0
Hi all,

I have an excel VBA userform that shows employee details for about 30 employees.  The userform works fine.  I also have two buttons that goes to the previous and to the next record, this works fine too.  What I would like to add, to the userform, is a search feature like a text box.  Enter the record id number in the text box and afterupdate the information for that record id will be display.  Something like the record id entered in the search textbox will find the record id in column A and make the cell, in the worksheet, the active cell.  I can figure out the rest of the coding from here.

Another thing I want to add is a combo box, when accounting is selected, the next and previous record buttons to move through all records that have accounting in the department field.

Thank you in advance
0
This procedure --which I attached to a command button-- isn't working.

I'm really trying to make it work with an IF Then Else to see if it can be done. (see attached workbook.

Private Sub cmdAdd_Click()
Dim rowcount As Double
Beginning:
rowcount = 9
If rowcount = Empty Then
    Cells(rowcount, 1).Value = frmEmployeeInfo.txtEmpName.Value
    Cells(rowcount, 2).Value = frmEmployeeInfo.lstEmpDepartment.Value
    Cells(rowcount, 3).Value = frmEmployeeInfo.txtEmpEarnings.Value
    
    Else
    rowcount = rowcount + 1
End If

End Sub

Open in new window

forms-in-excel.xlsm
0
How do you terminate a macro when it's stuck in some sort of loop. All I hear is the error "ding". I can't press any button in the debug toolbar.  I just get error dings.
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.