Search Results

Search found 3613 results on 145 pages for 'outlook vba'.

Page 46/145 | < Previous Page | 42 43 44 45 46 47 48 49 50 51 52 53  | Next Page >

• Running a simple VBA script to test a connection

  - by brohjoe

  I'm trying to test the connection of a GoDaddy SQL Server database. I'm getting an 'invalid connection string attribute.' What's wrong with this script? Dim cnn As ADODB.Connection Dim canConnect As Boolean Public Sub TestConnection() Set cnn = New ADODB.Connection cnn.Open "Provider=sqloledb;Data Source=GoDaddyServer.com;Initial Catalog=dBase1;UserID=userID; Password='password';" If cnn.State = adStateOpen Then canConnect = True cnn.Close End If MsgBox canConnect End Sub

  Read the article

• Function arguments VBA

  - by user1068249

  I have these three functions: When I run the first 2 functions, There's no problem, but when I run the last function (LMTD), It says 'Division by zero' yet when I debug some of the arguments have values, some don't. I know what I have to do, but I want to know why I have to do it, because it makes no sense to me. Tinn-function doesn't have Tut's arguments, so I have to add them to Tinn-function's arguments. Same goes for Tut, that doesn't know all of Tinn's arguments, and LMTD has to have both of Tinn and Tut's arguments. If I do that, it all runs smoothly. Why do I have to do this? Public Function Tinn(Tw, Qw, Qp, Q, deltaT) Tinn = (((Tw * Qw) + (Tut(Q, fd, mix) * Q)) / Qp) + deltaT End Function Public Function Tut(Q, fd, mix) Tut = Tinn(Tw, Qw, Qp, Q, deltaT) - (avgittEffektAiUiLMTD() / ((Q * fd * mix) / 3600)) End Function Public Function LMTD(Tsjo) LMTD = ((Tinn(Tw, Qw, Qp, Q, deltaT) - Tsjo) - (Tut(Q, fd, mix) - Tsjo)) / (WorksheetFunction.Ln ((Tinn(Tw, Qw, Qp, Q, deltaT) - Tsjo) / (Tut(Q, fd, mix) - Tsjo))) End Function

  Read the article

• VBA regex pattern

  - by KeyMs92

  This is probably a simple problem, but unfortunately I wasn't able to get the results I wanted... Say, I have the following line: "Wouldn't It Be Nice" (B. Wilson/Asher/Love) I would have to look for this pattern: " (<any string>) In order to retrieve: B. Wilson/Asher/Love I tried something like "" (([^))]*)) but it doesn't seem to work. Also, I'd like to use Match.Submatches(0) so that might complicate things a bit because it relies on brackets...

  Read the article

• Excel VBA: Sum invoice by client id with copying result to new worksheet

  - by Melkior

  Hi, i have strange problem doing reporting: i have numerous clients with different issued invoices. Problem comes to the point when there are invoices in minus and plus: Column A consists of client unique IDs, Column B invoice number, column C invoice amount A | B | C 0010019991 | 1800149471 | 162.00 | 2010-03-12 0010019991 | 1800136388 | 162.00 | 2010-02-12 0010019991 | 1600008004 | -36.00 | 2010-03-15 0010021791 | 1800132148 | 162.00 | 2010-03-12 0010021791 | 1800145436 | 162.00 | 2010-02-12 0010021791 | 1600007737 | -12.00 | 2010-03-15 0014066147 | 1800119068 | 1,684.80 | 2010-03-12 0014066147 | 1800123702 | 1,684.80 | 2010-02-12 0014066147 | 1600007980 | -1,300.80 | 2010-02-15 0014066147 | 1600007719 | -1,286.40 | 2010-03-15 I need to remove rows with negative invoices in a way that amount is summed with invoices which are not with negative amount. So that final result would look like: A | B | C | D 0010019991 | 1800149471 | 126.00 | 2010-03-12 0010019991 | 1800136388 | 162.00 | 2010-02-12 0010021791 | 1800132148 | 150.00 | 2010-03-12 0010021791 | 1800145436 | 162.00 | 2010-02-12 0014066147 | 1800123702 | 782.40 | 2010-02-12

  Read the article

• Pass a range into a custom function from within a cell

  - by Luis

  Hi I'm using VBA in Excel and need to pass in the values from two ranges into a custom function from within a cell's formula. The function looks like this: Public Function multByElement(range1 As String, range2 As String) As Variant Dim arr1() As Variant, arr2() As Variant arr1 = Range(range1).value arr2 = Range(range2).value If UBound(arr1) = UBound(arr2) Then Dim arrayA() As Variant ReDim arrayA(LBound(arr1) To UBound(arr1)) For i = LBound(arr1) To UBound(arr1) arrayA(i) = arr1(i) * arr2(i) Next i multByElement = arrayA End If End Function As you can see, I'm trying to pass the string representation of the ranges. In the debugger I can see that they are properly passed in and the first visible problem occurs when it tries to read arr1(i) and shows as "subscript out of range". I have also tried passing in the range itself (ie range1 as Range...) but with no success. My best suspicion was that it has to do with the Active Sheet since it was called from a different sheet from the one with the formula (the sheet name is part of the string) but that was dispelled since I tried it both from within the same sheet and by specifying the sheet in the code. BTW, the formula in the cell looks like this: =AVERAGE(multByElement("A1:A3","B1:B3")) or =AVERAGE(multByElement("My Sheet1!A1:A3","My Sheet1!B1:B3")) for when I call it from a different sheet.

  Read the article

• VBA How to find last insert id?

  - by Muiter

  I have this code: With shtControleblad Dim strsql_basis As String strsql_basis = "INSERT INTO is_calculatie (offerte_id) VALUES ('" & Sheets("controleblad").Range("D1").Value & "')" rs.Open strsql_basis, oConn, adOpenDynamic, adLockOptimistic Dim last_id As String last_id = "select last_insert_id()" End With The string last_id is not filled. What is wrong? I need to find te last_insert_id so I can use it in an other query.

  Read the article

• Printer setup via VBA in Excel

  - by Gina

  I am trying to assign a cell in Excel for the user to type the printer name where they want the print out to go and then use that value in the Application.ActivePrinter = (use the cell value) Even though I have done the programming assigning a name to the cell and using it in a variable it is giving me an error. I have set my variable as string, text, object and variant already and it's not working. Do you know what code should I use to be able to do this?

  Read the article

• Excel VBA to check autofilter for data

  - by cav719

  I need help checking for autofiltered rows not including the header. I want it to give a message box "No records found." then exit sub or continue with copy paste if there are rows beyond the header row. I know I need an If/Else entry after the filter to check for data but I'm having trouble figuring how to check. This code is being done from a UserForm I created. Here is my script: Private Sub Searchbycompanyfield_Click() If CompanyComboBox1.Value = "" Then MsgBox "Please enter a Company to begin search." Exit Sub End If ActiveSheet.Range("$A:$H").AutoFilter Field:=1, Criteria1:=EQDataEntry.CompanyComboBox1.Value, Operator:=xlOr Cells.Select Selection.Copy Sheets("Sheet2").Select Range("A5").Select ActiveSheet.Paste Call MessageBoxYesOrNoMsgBox End Sub Any help would be greatly appreciated.

  Read the article

• Excel VBA ComboBox2 doesn't get the right content

  - by Marc

  Hi, I'm having a problem with the content of a combobox. On my userform, there are 3 comboboxes. Depending on the chosen item from combobox1, combobox2 should display either set 1 or set 2. The same will be happening with the content of combobox 3, which depends upon the combination of chosen items from combobox 1 and 2. However, I'm running into problems with combobox 2, which is always populated by set 2, even if I select the item in combobox 1 that should generate set 1 in the second combobox. This is the code I used: Private Sub UserForm_Initialize() With ComboBox1 .Clear .AddItem "In contrast" .AddItem "Eng?" .AddItem "Trillers" .AddItem "Natuur(lijk)" .AddItem "Muziektrafiek" End With If ComboBox1.Value = "In contrast" Then GoTo LineComboBox1Set1 End If If ComboBox1.Value = "Eng?" Then GoTo LineComboBox1set2 End If If ComboBox1.Value = "Trillers" Then GoTo LineComboBox1set2 End If If ComboBox1.Value = "Natuur(lijk)" Then GoTo LineComboBox1set2 End If If ComboBox1.Value = "Muziektrafiek" Then GoTo LineComboBox1set2 End If LineComboBox1Set1: With ComboBox2 .Clear .AddItem "Op verkenning" .AddItem "Gehoord? Gezien?" .AddItem "On stage" .AddItem "Creabende" .AddItem "Ingeblikt" End With LineComboBox1set2: With ComboBox2 .Clear .AddItem "Op verkenning" .AddItem "Gehoord? Gezien?" .AddItem "On stage" .AddItem "Creabende" .AddItem "Ingeblikt" .AddItem "Speak up" .AddItem "In de kijker" End With Can anyone help me on this one? Thanks a lot in advance!! Kind regards, Marc

  Read the article

• Excel VBA pass array of arrays to a function

  - by user429400

  I have one function that creates an array of arrays, and one function that should get the resulting array and write it to the spreadsheet. I don't find the syntax which will allow me to pass the array of arrays to the second function... Could you please help? Here is my code: The function that creates the array of arrays: Function GetCellDetails(dict1 As Dictionary, dict2 As Dictionary) As Variant Dim arr1, arr2 arr1 = dict1.Items arr2 = dict2.Items GetCellDetails = Array(arr1, arr2) End Function the function that writes it to the spreadsheet: Sub WriteCellDataToMemory(arr As Variant, day As Integer, cellId As Integer, nCells As Integer) row = CellIdToMemRow(cellId, nCells) col = DayToMemCol(day) arrSize = UBound(arr, 2) Range(Cells(row, col), Cells(row + arrSize , col + 2)) = Application.Transpose(arr) End Sub The code that calls the functions: Dim CellDetails CellDetails = GetCellDetails(dict1, dict2) WriteCellDataToMemory CellDetails, day, cellId, nCells Thanks, Li

  Read the article

• How to use VBA to colour pie chart

  - by Timon Heinomann

  I have the following code in which the code tries to create a bubble chart with pie charts as the bubbles. As in this version colour themes are used to create a different colour in each pie chart (bulbble) in the function part I have the problem that it works depending on the paths to the colour paletts. Is there an easy way to make the function in a way that it works independently of those paths either by coding a colour for each pie chart segment or by using standardize paths (probably not possible, not preferable). Sub PieMarkers() Dim chtMarker As Chart Dim chtMain As Chart Dim intPoint As Integer Dim rngRow As Range Dim lngPointIndex As Long Dim thmColor As Long Dim myTheme As String Application.ScreenUpdating = False Set chtMarker = ActiveSheet.ChartObjects("chtMarker").Chart Set chtMain = ActiveSheet.ChartObjects("chtMain").Chart Set chtMain = ActiveSheet.ChartObjects("chtMain").Chart Set rngRow = Range(ThisWorkbook.Names("PieChartValues").RefersTo) For Each rngRow In Range("PieChartValues").Rows chtMarker.SeriesCollection(1).Values = rngRow ThisWorkbook.Theme.ThemeColorScheme.Load GetColorScheme(thmColor) chtMarker.Parent.CopyPicture xlScreen, xlPicture lngPointIndex = lngPointIndex + 1 chtMain.SeriesCollection(1).Points(lngPointIndex).Paste thmColor = thmColor + 1 Next lngPointIndex = 0 Application.ScreenUpdating = True End Sub Function GetColorScheme(i As Long) As String Const thmColor1 As String = "C:\Program Files\Microsoft Office\Document Themes 15\Theme Colors\Blue Green.xml" Const thmColor2 As String = "C:\Program Files\Microsoft Office\Document Themes 15\Theme Colors\Orange Red.xml" Select Case i Mod 2 Case 0 GetColorScheme = thmColor1 Case 1 GetColorScheme = thmColor2 End Select End Function The code copies a single chart again and again on the bubbles. So I woudl like to alter the Function (now called Get colourscheme) into a fucntion that assigns a a unqiue rgb colour to each segment of each pie chart

  Read the article

• Using "wildcards" in a vlist array to delete rows in Excel

  - by KMinner

  Good Morning All, I'm trying to setup a vba macro to delete all user IDs out of a spreadsheet that do not start with designated prefixes (e.g. US, A1, VM, etc). The below block of code was found on the Code Library and looks to be what I need but there is one problem: When I enter in UserID prefixes into the vlist fields, it treats them as absolute rather then a part of the string that I want to keep. Is there a way to incorporate wildcards into a vlist? Sub Example1() Dim vList Dim lLastRow As Long, lCounter As Long Dim rngToCheck As Range, rngFound As Range, rngToDelete As Range Application.ScreenUpdating = False With Sheet1 lLastRow = Get_Last_Row(.Cells) If lLastRow > 1 Then vList = Array("US", "A1", "EG", "VM") 'we don't want to delete our header row With .Range("A2:A" & lLastRow) For lCounter = LBound(vList) To UBound(vList) Set rngFound = .Find( _ what:=vList(lCounter), _ lookat:=xlWhole, _ searchorder:=xlByRows, _ searchdirection:=xlNext, _ MatchCase:=True) 'check if we found a value we want to keep If rngFound Is Nothing Then 'there are no cells to keep with this value If rngToDelete Is Nothing Then Set rngToDelete = .Cells Else 'if there are no cells with a different value then 'we will get an error On Error Resume Next If rngToDelete Is Nothing Then Set rngToDelete = .ColumnDifferences(Comparison:=rngFound) Else Set rngToDelete = Intersect(rngToDelete, .ColumnDifferences(Comparison:=rngFound)) End If On Error GoTo 0 End If Next lCounter End With If Not rngToDelete Is Nothing Then rngToDelete.EntireRow.Delete End If End With Application.ScreenUpdating = True End Sub

  Read the article

• excel vba: writing to mysql database

  - by every_answer_gets_a_point

  i would like to write a macro that will write to a mysql database. can someone please get me started on this?

  Read the article

• Export sheet from Excel to CSV

  - by Mike Wills

  I am creating a spread sheet to help ease the entry of data into one of our systems. They are entering inventory items into this spread sheet to calculate the unit cost of the item (item cost + tax + S&H). The software we purchased cannot do this. Aan invoice can have one or more lines (duh!) and I calculate the final unit cost. This is working fine. I then want to take that data and create a CSV from that so they can load it into our inventory system. I currently have a second tab that is laid out like I want the CSV, and I do an equal cell (=Sheet!A3) to get the values on the "export sheet". The problem is when they save this to a CSV, there are many blank lines that need to be deleted before they can upload it. I want a file that only contains the data that is needed. I am sure this could be done in VBA, but I don't know where to start or know how to search for an example to start. Any direction or other options would be appreciated.

  Read the article

• Weird bug on powerpoint vba

  - by asksuperuser

  I have a "mynote" textbox on a slide. If I execute: Sub test() If ActiveWindow.Selection.SlideRange.Shapes("mynote").Visible Then MsgBox "ok" End If end sub It works. But If I attach a shape with this macro: Sub test(oShape As Shape) If ActiveWindow.Selection.SlideRange.Shapes("mynote").Visible Then MsgBox "ok" End If end sub It doesn't work (no error message, no "ok" message)

  Read the article

• Excel VBA / SQL Union

  - by Edge

  Hi, I am trying to Join 2 seperate columns from 2 different sheets to make a longer column from which i can then use a Vlookup from. Sheet1 A, B, C, D, E, F, G Sheet2 A, B, C, D, E, F, G I want to Join(Union) Columns B from sheet1 and C from sheet2 together and find the Distinct values of the new list. I have been working on this for weeks. Thanks

  Read the article

• how do i declare a global variable in VBA

  - by Nimrod

  I wrote the following section: Function find_results_idle() Public iRaw As Integer Public iColumn As Integer iRaw = 1 iColumn = 1 and i get the error messege: "invalid attribute in Sub or Function" do you know what i did wrong? i tried instead of "Public" to use "Global" but got the same problem. I tird to declare the function itself as "Public" but no good... what do i do wrong? thanks.

  Read the article

• WHERE IN Query with two recordsets in Access VBA

  - by Henry Owens

  Hi All, My first post here, so i hope this is the right area. I am currently trying to compare 2 recordsets, one of which has come from an Excel named range, and the other from a table in the Access database. The code for each is: Set existingUserIDs = db.OpenRecordset("SELECT Username FROM UserData") Set IDsToImport = exceldb.OpenRecordset("SELECT Username FROM Named_Range") The problem is that I would like to somehow compare these two recordsets, without looping (there is a very large number of records). Is there any way to do a join or similar on these recordsets? I can not do a join before creating the recordsets, due to the fact that one is coming from Excel, and the other from Access, so they are two different DAO databases. The end goal is that I will choose only the usernames that do not already exist in the access table to be imported (so in an SQL query, it would be a NOT IN(table)). Thanks for any assistance you can lend! Regards, Bricky.

  Read the article

• Way for Excel VBA to use shadowed chart event handler

  - by Apprentice Queue

  I have a embedded chart and I want to modify the behavior on the mouseDown event but only when it occurs on a dataSeries (xlSeries). But for all other elements (axis, plot area, gridline, etc), I want it to fall back on the default mouseDown behavior. Is this possible? Thanks.

  Read the article

• vba excel copy subtable from sheet to sheet

  - by user429400

  I realize that this is probably a duplicate, but I've been searching for an hour and I can't to get the syntax right. I have a sheet with several tables. There is at least one empty column and one empty row between one table to the other. I know the start row and start column of each table, and I know that each table has 3 columns. I don't know how many rows it has. I want to write a sub that receives: table start row table start column and copies the table into another sheet (let's say that the destination is sheet2 starting at A1). I know I can do it with a loop, but I suspect there is a better syntax right? (The main issue here is that I need to find the number of rows each table has) Thanks. Li

  Read the article

• vba: a forever loop

  - by I__

  Sub something(tecan) On Error Resume Next Dim arr As New Collection, a Dim aFirstArray() As Variant Dim i As Long aFirstArray() = Array(Dir(tecan & "*.ESY", vbNormal)) aFirstArray(0) = Mid(aFirstArray(0), 1, 4) Do While Dir <> "" ReDim Preserve aFirstArray(UBound(aFirstArray) + 1) aFirstArray(UBound(aFirstArray)) = Mid(Dir, 1, 4) Loop On Error Resume Next For Each a In aFirstArray arr.Add a, a Next For i = 1 To arr.Count Cells(i, 1) = arr(i) 'open_esy (tecan & arr(i) & "*") Next Erase aFirstArray For i = 1 To arr.Count arr.Remove i Next i here is how i call this sub: something (tecan1) something (tecan2) on the first call it works and does what it is supposed to but on the second call it gets stuck in this loop: Do While Dir <> "" ReDim Preserve aFirstArray(UBound(aFirstArray) + 1) aFirstArray(UBound(aFirstArray)) = Mid(Dir, 1, 4) Loop why does it get stuck in the loop?

  Read the article

• VBA Add to Array and Use Previous Value

  - by MattHead93

  I'm trying to write some code that will take a value WeekNum, and add it to an array Week(1 To 51), and then associate a value from a Textbox TargDef. Once this has been added to the array, I want to look up the value of the array for the previous WeekNum and add it to a value ProdTarg. I've created this much so far: Dim Week(1 To 51) Dim Count As Integer If TargDef < 0 Then Count = WeekNum Week(Count) = Abs(Val(TargDef)) If Val(Week((Count) - 1)) = 0 Then ProdTarg = Val(ProdTarg) Else ProdTard = Val(ProdTarg) + Val(Week((Count) - 1)) End If End If I am currently receiving the error "Subscript out of Range" for the line If Val(Week((Count) - 1)) = 0 Then Any help will be greatly appreciated!

  Read the article

• VBA-Excel return multidimensional array from a function

  - by alesdario

  I'm trying to write a function which returns a multidimensional array. The problem is that the size of the array isn't defined. My array is initialized in the function below my_list() Dim my_list() As String Public Sub Load_My_List() Dim last_column As Integer last_column = some_helper.Get_Last_Column(somw_worksheet) 'my array is resized in this point ReDim my_list(1 To last_column - 1, 1) Dim i As Integer i = 1 For index= 2 To ultima_colonna my_list(i, 0) = some_worksheet.Cells(2, index).value my_list(i, 1) = index i = i + 1 Next index End Sub So, how can i write a function which returns my_list ? Something like the function below generate a mismacthing type error Public function Get_My_List as String() Get_My_List = my_list End Function and how can i call this function properly? I think that something like Dim test() as String test = Get_My_List will doesn't work

  Read the article

• vba Loop over a non-contiguous range

  - by Jeffrey

  I have a non-contiguous range on rows (example address of myRange: $2:$2,$4:$205,$214:$214) and I would like to access a specific row and column within the range. I have tried the following: 'Get the value of the 2nd row, 1st column within the range myRange.rows(2).Cells(, 1).Value However, this is giving me the value of the 2nd row in the WorkSheet, and NOT in the range - meaning it is giving me address $3$1 - and not $4$1 Can someone please explain how I can access the values within in my range? (It may have to do with different areas) Thank You

  Read the article

• VBA form_error vs on error

  - by dmr

  I am trying to set up error handling for a MS-Access application. The standard method to do this seems to be with an On Error statement in each subroutine/function. It seems simpler to me to use the Form_Error function to catch all the runtime errors in that form as opposed to an On Error statement for each sub/function called by an event on that form. (Obviously, for code in modules, there is no Form_Error function and therefore the only method is the On Error statement) What are the pros and cons of using On Error vs Form_Error?

  Read the article

< Previous Page | 42 43 44 45 46 47 48 49 50 51 52 53  | Next Page >