Posts by Philosophaie

Important Notice


Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

  • Put in Excel Spreadsheet from a Function

    I am trying to put arr1 onto Sheet1 but it is giving me this error:


    Quote

    Sub or function not defined.


    In module:


    Code
    Function func(ByRef arr As Variant) As Variant
    Dim arr1(8) As Variant
    For k = 1 To 8
        arr1(k) = arr(7 - k)
    Next k
    Call printarr(arr1)
    func = arr1(8) & arr1(7) & arr1(6) & arr1(5) & arr1(4) & arr1(3) & arr1(2) & arr1(1)
End Function


    In Sheet1:


    Code
    Sub printarr(ByRef arr As Variant)
    With Sheets("Sheet1")
        For k = 1 To Len(arr)
            .Cells(7, k + 3) = arr(k)
        Next k
    End With
End Sub


    Code
    =func(D3:K3)


    'In cell 5,3

  • Printing an array from a function

    I want to create a function in a module that will read an array from sheet1 preform an operation on it then return the result to the spreadsheet in a defined amount of cells in this example 8.


    In Module1:

    Code
    Function func1(ByRef arrayA())
    Dim arrayB(8)
    For k = 0 To 7
        arrayB(k + 1) = arrayA(k) * 2
    Next k
    func1 = arrayB
End Function


    In sheet1:


    Code
    =func1(D3:K3)


    The input is D3:K3, arrayA.


    How does the resultant arrayB get populated onto the spreadsheet?

  • WorkSheet_Change not updating

    Re: WorkSheet_Change not updating


    You have change a cell not a whole row or something else.


    I have put in "Sheet1" in VBA it just:


    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    MsgBox ("InsertOrDelete")
    End Sub


    and nothing happens when I right click on the row #s just to the right of columnA and Select "Insert".


    You try.

  • WorkSheet_Change not updating

    When you delete or insert a row (right click the the row #s on the left before the first column):

    Sub WorkSheet_Change(Byval Target As Range)
    'code
    End Sub


    does not detect that insert or delete row so nothing changes and Access is not updated.


    Is there some way of detecting when the insert row or delete row has been activated to do something that will make WorkSheet_Change notice.

  • create a Recordset and have it delete all records in an Access table

    In Excel VBA I would like to create a Recordset and have it delete all records in an Access table keeping the fields.


  • Transferring data from Excel to Access

    In VBA Excel transferring data from Excel to Access.


    I want to know how to increment the [ID] for each value of the range. [ID] is the Primary Key.


    I have tried "NULL".


    I have tried adding "n=n+1" after the "for each" and "thisSQL = "INSERT INTO... VALUES (" & n & ",..."


    Code
    For Each value In Range("D5:D" & Cells(Rows.Count, "D").End(xlUp).Row)      accDateSub = Cells(value.Row, 1)      accDescSub = Cells(value.Row, 2)      accSub = Cells(value.Row, 3)      thisSQL = "INSERT INTO [Table1] ([ID], [Date], [Desc], [Data]) VALUES ("[U]What goes here?[/U] ", #" & Format(CDate(accDate), "MM/DD/YY") & "#, '" & accDesc & "', " & accData & ");"      conn.Execute CommandText:=thisSQLNext

  • Troubleshoot a INSERT INTO statement in Excel

    Re: Troubleshoot a INSERT INTO statement in Excel


    I ran it with Debug.Print thisSQL. Got a "Object Required" error


    Here are my Fields:
    ID, DateSub, DescSub, Sub, RunningTotal, Add, DescAdd, and DateAdd.
    Eight in total.
    Could someone show me where my syntax is Wrong.


    Code
    thisSQL = "INSERT INTO [a Chase Table] ([ID], [DateSub], [DescSub], [Sub], [RunningTotal], [Add], [DescAdd], [DateAdd]) VALUES (" & n & ", #" & Format(accDateSub, "yyyy/mm/dd") & "#, '" & accDescSub & "', " & accSub & ", " & accRunTotal & ", " & accAdd & ", '" & accDescAdd & "', #" & Format(accDateAdd, "yyyy/mm/dd") & "#);"


    Is it possible the syntax for the Dates are wrong?

  • Troubleshoot a INSERT INTO statement in Excel

    Everything is functioning correctly except to "INSERT INTO" statement. Could someone help troubleshoot it with me.