w3hello.com logo
Home PHP C# C++ Android Java Javascript Python IOS SQL HTML videos Categories
  Home » VBA » Page 1
vba for cut and paste is too slow
You hardly ever need to use .Select for anything - you can access an objects properties directly without selecting it first. i.e. : Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 7 And Target.Value <> "" Then Application.EnableEvents = False '// Prevent infinite loop CustomerCollected Application.EnableEvents = True '// Re-enable events End If End

Categories : Vba

Out of stack space when using recursion in function
If you remove the IsEmpty lines, Empty cells will be treated as 0. If you wish to leave them blank and sort around them you will need to impliment additional logic. Your second loop needed to be adjusted. As it stood, the first time call 1: The first loop would give: 1 100 8 9 9 50 100 500 1000 Then the second loop would get to 1 > 100 and exit sub. BUT... its best not to simply remove the ex

Categories : Vba

TransferText export to CSV not working, but TransferSpreadsheet to XLSX working
The answer to this was that there was an error '3441' "Text file specification field separator matches decimal separator or text delimiter", which was only visible when trying to run it in the Direct-window (Ctrl+G). Apparently, when you use a non-English OS this is a problem with the default setting for the delimiters. To solve it, an Export Specification has to be provided. However, there are

Categories : Vba

Outlook 2013 rule with Run a Script to Update subject and forward mail
You are looking for InStr not Contains. http://msdn.microsoft.com/en-us/library/office/gg264811%28v=office.15%29.aspx Option Explicit Private Sub ForwardEmailIndeed_test() ' Open a mailitem first Dim curritem As mailitem Set curritem = ActiveInspector.currentItem ForwardEmailIndeed curritem End Sub Sub ForwardEmailIndeed(Item As Outlook.mailitem) Dim myForward As mailitem Set myForward = It

Categories : Vba

Deleting all Records after a specific string is found in one record in one field
Excel doesn't have tables, it has spreadsheets. Maybe that's nitpicking, but it's a pet peeve of mine when people refer to Excel as a "database". As for the problem at hand, you can import the data using a Primary Key. Stick it into a field called PKey. This will allow you to find where the record is that you want to delete. Then, do something like this: Dim db as Database Dim rec as Record

Categories : Vba

excel inputbox vba cancel function
You need to check if LSearchValue is empty. Use this do loop Do While LSearchValue <> 0 LSearchValue = InputBox("Please enter a value to search for. Enter a zero to indicate finished" & _ "entry.", "Enter Search value") If LSearchValue = "" Then Exit Do '<~~ Or Exit Sub if you want to terminate the sub ElseIf LSearchValue <> 0 Then iHowMany = iH

Categories : Vba

Issues with Runtime Error '2185' while using combo box to search dynamically on continuous form
Googling 'runtime error 2185' yields results about an error that occurs when you're trying to access a control's properties without the control having focus (would have been nice to include the error message in your question). It seems weird that the control wouldn't be having focus, since it's that control raising the Change event you're handling here. So I'd just wrap the code in an error hand

Categories : Vba

Open excel file in outlook share group mail box
You get to a shared mailbox like this. http://www.slipstick.com/developer/working-vba-nondefault-outlook-folders/ "You can use the mailbox owner's display name, alias, or email address when resolving the recipient." Dim objOwner As Outlook.Recipient Set objOwner = MyNameSpace.CreateRecipient("email address") objOwner.Resolve If objOwner.Resolved Then 'MsgBox objOwner.Name Set My

Categories : Vba

VBA Run-Time Error When Trying to set cell value to forumla
Since your Function is named WorksheetFormula try replacing cell.Formula = formula_1 with WorksheetFormula = formula_1 Public Function WorksheetFormula(ByVal target As Range, ColumnHeader As String, formula_1 As String, columnFormula As String, cell As Range) columnFormula = target.Offset((14 - target.Row), 0).Value Select Case collumnFormula Case "% Discount"

Categories : Vba

How to stop a sub which calls upon multiple subs
Instead of using Exit Sub Throw a custom exception and catch it in your master_sub. Something like Sub sub1() Dim MyMsg as Variant MyMsg = msgbox("Do you wish to continue?", vbYesNo) If MyMsg = 7 then 'If user clicks "No" Throw new MyException Else 'Do stuff End if End Sub master_sub() Try call sub1 call sub2 call sub3 Catch ex as MyException 'Do

Categories : Vba

Adding calculated field using VBA
Looking at your code, you haven't added the calculated field to the pivot table as a data field. You need to add the following line of code after you've created the field: .PivotFields("TOGrowth%").Orientation = xlRowField

Categories : Vba

MS Project and VBA IFF FORMULA
You were close. The formula you need is this: IIf([Number2]=1,[Duration4],[Duration5]). Note that the function is called Inline If, therefore the abbreviation is IIf, not IFf.

Categories : Vba

Getting Numerics from end of a String in VBA
Function onlyDigits(s As String) As String Dim retval As String Dim i As Integer retval = "" ' For i = Len(s) To 1 Step -1 If Mid(s, i, 1) Like "#" Then retval = Mid(s, i, 1) & retval Else Exit For End If Next onlyDigits = retval End Function

Categories : Vba

VBA - Add different ships on the worksheet
This isn't exactly what you asked for but maybe this van get you going. For this code you will need to place a simple button somewhere and press it if you selected the cell you want to place your ship. Option Explicit Sub battleship() Dim length As Integer Dim color As String length = InputBox("Please give the length of your ship") color = InputBox("Please give the color of your ship")

Categories : Vba

Excel VBA Combo Box List
use Year() and Date() to get the current year and then loop to add the items CurrentYear = Year(Date) With Sheet1.YearCombo .clear For yr = 2010 To CurrentYear .AddItem yr Next yr End With

Categories : Vba

isnumeric VBA what am i doing wrong here with this function?
If I understand correctly, this should work: Sub test() Dim RX As Range Set RX = Application.InputBox(prompt:=" Enter the range for X", Type:=8) If WorksheetFunction.Count(RX) <> Application.CountA(RX) Then MsgBox (" Please use only numbers") Exit Sub End If End Sub COUNTA and COUNT are worksheet functions that you can use in VBA. COUNTA returns the number of non-blank cells. COU

Categories : Vba

How to fill the shape with color under a given condition
You need to access OLE objects and their properties a bit differently from the way you work with regular shapes on slides. Instead of TextBox1.Text, use ActivePresentation.Slides(2).Shapes("TextBox1").OLEFormat.Object.Text

Categories : Vba

How to add not equals to in VBA
I would not advise to anyone to operate on "Active" objects in VBA. It is really tricky. Try to assign all your Workbooks, Worksheets and Ranges to actual Objects. Like for example: Dim wb as Workbook Set wb = ActiveWorkbook 'good to use it in button code. Dim ws as Worksheet Set ws = wb.Sheet(1) Dim rng as Range Set rng = ws.Range("A1") You should not get an error if you make sure that Obje

Categories : Vba

MS Access use VBA to Delete a Module from access file
From Ozgrid.com - How to delete a code module This code will remove a code module. Sub DeleteThisModule() Dim vbCom As Object MsgBox "Hi, I will delete myself " Set vbCom = Application.VBE.ActiveVBProject.VBComponents vbCom.Remove VBComponent:= _ vbCom.Item("Module1") End Sub So you need a reference to Visual Basic For Applications Extensibility and also you need to Trust A

Categories : Vba

Calculate rental Costs in excel-vba
You need to make this easier , not "smarter" Just add more columns! Then you have 3 simple rules (english code) if days >= 30 then price = Col K 'lookup for car days = days - 30 end if if days > 7 then price = price + ((days - 7) * Col J) 'lookup for car days = days - (days - 7) end if if days > 0 then price = price + hlookup( days, C:I .....)

Categories : Vba

Access VBA giving incorrect results when comparing two numbers
Man... I can't believe I didn't see this at first. I tested your code and got similar results as you. After looking at it closer I noticed that the if statement was wrong. In short you need to use Option Explicit and this error would have been caught. Option Explicit ensures that all variables are declared. In not an error is thrown. You statement contains a null If checkDigit <> calculat

Categories : Vba

Excel VBA format a series of months with commas and a "and" at the end
Starting with data like this: Run this code: Sub months() Dim wks As Worksheet Dim rng As Range Set wks = Worksheets("Sheet4") Set rng = wks.Range("B3:M3") Dim count As Integer count = WorksheetFunction.CountA(rng) If count < 1 Then Exit Sub Dim result As String For Each cell In rng If Not IsEmpty(cell) Then result = result & c

Categories : Vba

Exporting Received Email daily Tally from Outlook to File using Excel VBA
While in development remove "On Error GoTo" to more easily see the lines with errors. You need not focus on the current errors until you can process all the subfolders. Try this: Private Sub LoopFolders_Test() 'Application Variables Dim olApp As Outlook.Application Dim objNS As Outlook.Namespace Dim myolItems As Folder Dim Start As Date Dim EndTime As Date Set olA

Categories : Vba

Run-time error 91
The meaning of the error is: 91 - Object variable not set It is obtuse the chain of objects involved. This is a Recorded Macro that does the type of thing you are trying to do. If in doubt unwind a complex statement into simplier ones. Also always do a Msgbox Vartype(whatever):Msgbox IsEmpty(whatever):msgbox IsNull(whatever). This allows you to check assumptions on what something is. Range

Categories : Vba

In batch file, how to get focus on a specific window (opened file)
You can't. If you wanted to use vbscript or jscript you could do what you want in a command prompt in an unreliable way (but it will work most circumstances). Excel has it's own forms. Put column headers in a row. Put selection in same row. Alt + D, O. Plus you can make Excel only allow entries on some cells, like a invoice form. Right click cell, Properties, Protection, Unlock. Then Alt + T,

Categories : Vba

VBA Adding Pivot Fields From Array
Remove the last line of your code then click record macro and add the fields manually. You should be able to clean up the recorded code easily and then add it to what you already had. Hope that helps

Categories : Vba

Outlook VBA - Msg.SaveAs "Path" issue
Firstly I ma not sure why you have 5 If statements with the same condition. Wjy not roll them into one? Secondly, You are calling Move, then try to us the original message. You cannot do that - the old item is gone. You need to use the new one retruned by Move: If InStr(1, Msg.Subject, Tracking) > 0 Then MkDir Diretorio set Msg = Msg.Move(olConc) Msg.SaveAs Diretorio & "" & "Ca

Categories : Vba

VBA record will not update with the correct information
Your code uses lstAllGroups.ListCount to access the list box rows by index number. However, .ListCount starts counting from 1 and the list box row index values start from 0. So that means the equivalent of lstAllGroups.Column(1, lstAllGroups.ListCount) refers to a row which does not exist in the list box. Here is an Immediate window example which hopefully clarifies the situation. My list box

Categories : Vba

How use the combobox keydown event without selecting the listed item
Edited Answer: Now having built my own sheet and worked with these ideas, ironically Application.EnableEnable only helps in certain situations because the Combobox_Change() event still fires with events disabled (or seems to be the case, at least). The basic idea that I found involved manipulating the KeyCodes and setting flags. My example below involves using a ComboBox called TempCombo and runn

Categories : Vba

VBA script using contains not working
You should be using InStr. If InStr(1, rpt.field3.Text, "99", vbTextCompare) Then rpt.field2.visible = false Else rpt.field2.visible = true End If See MSDN InStr Function documentation for more information

Categories : Vba

Sum variables in VBA
Did you maybe mean ... ? Select Case UCase(TimeType) Case "TOTAL" tempTime = WorksheetFunction.Sum(strSplit(2), strSplit(5), strSplit(8), strSplit(11), strSplit(14), strSplit(17), strSplit(20), strSplit(23), strSplit(26), strSplit(29), strSplit(32), strSplit(35), strSplit(38) Anyway, the problem is that you're summing also strSplit(38) which is 1721 even if you wrote in your sample which i

Categories : Vba

Calculate in Function
You can put an IF condition: Function TT(FormuleTekst) teg = Formuletekst IF teg = "/" Then TT = 500/50 Else TT = 500*5 End Function For MS-Excel, you can use an empty cell (e.g A15) to put the formula and can return the same to TT. Something like this: Function TT(FormuleTekst) teg = FormuleTekst TT = "=" & 500 & teg & 50 ActiveSheet.Range("A15").Value = TT TT = ActiveSheet.Range(

Categories : Vba

Recurring Email with Voting buttons VBA
Is Outlook running at the time your code is executed? Try to add the a call o Namespace.Logon: Set olApplication = WScript.CreateObject("Outlook.Application") set olNS = olApplication.GetNamespace("MAPI") olNS.Logon Set olMail = olApplication.CreateItem(0)

Categories : Vba

Automation error when getting ReadyState of InternetExplorer object
Instead of Set ie = New InternetExplorerMedium just use Set ie = New InternetExplorer or, for late binding: Set ie = CreateObject("InternetExplorer.Application") This makes the error go away. I'm not sure why you would use InternetExplorerMedium in the first place. Quoting the small print in the documentation: Remarks Windows Internet Explorer 8. On Windows Vista, to create an

Categories : Vba

unable to find record in recodset
You should do like this because i think you are not getting any records in recordset if not rcs.EOF then rcs.FindFirst "ItemNo = " & itemNo 'error here, runtime error 3251 'operation is not supported for this type of object If rcs.NoMatch = True Then MsgBox "nomatch" Else MsgBox "OK" End If end if Update Can you try usi

Categories : Vba

open folder, manipulate files in excel, save excel files in new directory
Why don't you use File System Object. Something like: Sub ReadAllfiles() Dim fso As Scripting.FileSystemObject Dim sFile As Scripting.File Dim subFldr As Scripting.Folder Dim wbName As String Dim fldrPath As String Dim fname As String Dim fldrDesc As String Dim wbTxt As Workbook Dim ws As Worksheet Dim wbDesc As Workbook fldrDesc = "C:UserYourdestinatio

Categories : Vba

why can't i programmatically copy a locked .mdb but i can copy it through explorer?
Microsoft explains it pretty simply in their KB article. - Create a module and type the following lines in the Declarations section: Option Explicit Declare Function apiCopyFile Lib "kernel32" Alias "CopyFileA" _ (ByVal lpExistingFileName As String, _ ByVal lpNewFileName As String, _ ByVal bFailIfExists As Long) As Long - Type the following procedure: Sub CopyFile(SourceFile As String,

Categories : Vba

Code is Crashing when items are not found
Set a range variable and search for the string Test the range if it found something If the range is found, format with your format conditions Dim strSearch As String Dim rngTemp As Range strSearch = "Implementation Record Type: ^Parent Record" On Error Resume Next Set rngTemp = Columns(1).Find(What:=strSearch, LookAt:=xlPart) On Error GoTo 0 If Not rngTemp Is Nothing Then 'Your format co

Categories : Vba

How do I check for the presence of a valid email (digital) signature attached to an Outlook MailItem (email message)?
Outlook always represents signed/encrypted messages as regular IPM.Note MailItem objects. It goes as far as returning a fake IMessage MAPI interface from the MailItem.MAPIOBJECT property. You can see this in OutlookSpy - select a signed message, click IMessage button on the OutlookSpy ribbon. PR_MESSAGE_CLASS will be IPM.Note. Select the PR_ENTRYID property, right click, select IMAPISession::Open

Categories : Vba




© Copyright 2018 w3hello.com Publishing Limited. All rights reserved.