Application defined or object defined error vba

application defined or object defined error vba

Questions: Excel VBA application-defined or object-defined error ; Dim intX as ; Dim Uname As ; Dim Pword As ; Dim Epin As ; 9 Uname = Cells(intX, 5). Hi Karthi, The reason for your "Run-time error '': Application-defined or object-defined error" is because in your LoadDate() function. The “object-defined error” in any version of VBA (Excel, Access, Word) occurs when you have code that uses an object not defined as part of the native. application defined or object defined error vba

Read these next

Hi to All, I am using below code for check the date, that users input in worksheet. but it gives me, Run-time error """" Application defined or Object defined error. I don't know where i go wrong? I don't know what to do? Please help me out! Code: Option Explicit Dim r As IntegerDim c As IntegerDim IsLeapYear As Integer Private Sub Worksheet_Change(ByVal Target As Range) On Error Resume Nextr = sprers.eu = sprers.eu If sprers.eu = 20 ThenCells(r, c)sprers.eu = vbBlack Cells(r, c)sprers.eu = FalseLoadDateEnd If If sprers.eu = 37 ThenCells(r, c)sprers.eu = vbBlack Cells(r, c)sprers.eu = FalseLoadDateEnd If If sprers.eu = 54 ThenCells(r, c)sprers.eu = vbBlack Cells(r, c)sprers.eu = FalseLoadDateEnd If On Error GoTo 0 End Sub Public Function LoadDate() As Integer Cells(r, c)sprers.eu = vbBlackCells(r, c)sprers.eu = FalseCells(r, c)sprers.eu = vbWhite If Len(Cells(r, c)) = 0 ThenCells(r, c)sprers.eu = vbBlackCells(r, c)sprers.eu = FalseCells(r, c)sprers.eundex = 36End If If (IsNumeric(Cells(r, c)) = False) ThenCells(r, c)sprers.eu = vbRedCells(r, c)sprers.eu = TrueEnd If If Len(Cells(r, c)) <> 8 ThenCells(r, c)sprers.eu = vbRedCells(r, c)sprers.eu = TrueEnd If If Left(Cells(r, c), 4) > """" ThenCells(r, c)sprers.eu = vbRedCells(r, c)sprers.eu = TrueEnd If If Left(Cells(r, c), 4) < """" ThenCells(r, c)sprers.eu = vbRedCells(r, c)sprers.eu = TrueEnd If If Right(Cells(r, c), 2) > ""31"" Then Cells(r, c)sprers.eu = vbRed Cells(r, c)sprers.eu = TrueEnd If If Right(Cells(r, c), 2) < ""01"" ThenCells(r, c)sprers.eu = vbRedCells(r, c)sprers.eu = TrueEnd If If Mid(Cells(r, c), 5, 2) < ""01"" ThenCells(r, c)sprers.eu = vbRedCells(r, c)sprers.eu = TrueEnd If If Mid(Cells(r, c), 5, 2) > ""12"" ThenCells(r, c)sprers.eu = vbRedCells(r, c)sprers.eu = TrueEnd If If Mid(Cells(r, c), 5, 2) = ""04"" ThenIf Right(Cells(r, c), 2) > ""30"" ThenCells(r, c)sprers.eu = vbRedCells(r, c)sprers.eu = TrueEnd IfEnd If If Mid(Cells(r, c), 5, 2) = ""06"" ThenIf Right(Cells(r, c), 2) > ""30"" ThenCells(r, c)sprers.eu = vbRedCells(r, c)sprers.eu = TrueEnd IfEnd If If Mid(Cells(r, c), 5, 2) = ""09"" ThenIf Right(Cells(r, c), 2) > ""30"" ThenCells(r, c)sprers.eu = vbRedCells(r, c)sprers.eu = TrueEnd IfEnd If If Mid(Cells(r, c), 5, 2) = ""11"" ThenIf Right(Cells(r, c), 2) > ""30"" ThenCells(r, c)sprers.eu = vbRedCells(r, c)sprers.eu = TrueEnd IfEnd If If Mid(Cells(r, c), 5, application defined or object defined error vba, 2) = application defined or object defined error vba ThenIf Right(Cells(r, c), 2) > ""29"" ThenCells(r, c)sprers.eu = vbRedCells(r, c)sprers.eu = TrueEnd IfEnd If If Len(Cells(r, c)) = 0 ThenCells(r, c)sprers.eu = vbBlackCells(r, c)sprers.eu = FalseCells(r, c)sprers.eundex = 36End If IsLeapYear = Left(Cells(r, c), 4) LeapYear (IsLeapYear) If IsLeapYear = True ThenCells(r, c)sprers.eu = vbBlackCells(r, c)sprers.eu = FalseElseCells(r, c)sprers.eu = vbRedCells(r, c)sprers.eu = TrueEnd If End Function Public Function LeapYear(IsLeapYear) As IntegerIsLeapYear = Month(DateSerial(IsLeapYear, 2, 29)) = 2End Function Regards, Karthikeyan T
  • Hi Karthikeyan,

    a) Maybe you should check the date of entry using the DateValidation and ConditionalFormating possibility(?)
    b) What instruction generates an error?

    Best Regards
    Andrija Vrcan

  • Thanks for the reply Andrija,
    It generates error from
    Cells(r, c)sprers.eu = vbBlack
    in Public Function LoadDate() As Integer.

  • What are the values of r and c when the error occurs. Are they pointing to a valid cell?

    John Warner

  • They are
    r = sprers.eu
    c = sprers.eu

    it pointing the activecell.

    When the worksheet_change event calls the LoadDate function, there only the error occurs in the first line

    Cells(r, c)sprers.eu = vbBlack

    I dont know where i go wrong

    Regards,
    Karthikeyan T

  • Well then that is your problem, they should be numbers and not strings.

    John Warner

  • Hi Karthikeya,

    I tested your procedure but don't occurs error (?)
    instead of:
    Cells(r, c)sprers.eu = vbBlack
    use the following code lines:
    '--
    On Rrror Resume Next
    sprers.eu
    Cells(r, c)sprers.eu = vbBlack
    MsgBox sprers.eu & " - " & sprers.euption
    MsgBox "r = " & r & " / c = " & c
    On Rrror GoTo 0
    '--
    will be more clear.
    Note: but, your logic is not good, I changed code on my home computer
    and will send you after work.
    PS: It would be good to describe what exactly you want to make checks
    (description)
    Best Regards
    Andrija Vrcan

  • Hi Karthi,

    The reason for your "Run-time error '': Application-defined or object-defined error" is because in your LoadDate() function you haven't defined the variables "r" or "c" at all. In fact your LoadDate function doesn't have any parameters in it. So you need to declare the "r" and "c" variables using the "Dim" statement in the body of your function or you need to need to declare them as parameters to your function.

    For example you could do
    Public Function LoadDate(r as integer, c as integer) as Integer

    Or you could try
    Public Function LoadDate() as Integer

  • Hi Karthi,
    Sorry my last post was submitted before I finished my thought
    Or you could define the 'r' and 'c' variables in the body of your function

    Public Function LoadDate()as Integer
    Dim r as integer, c as integer
    Rest of your code here where you set r & c to something.
    End Function
    Seeing how you submitted two functions and one worksheet event subroutine I'm not sure how you intended the LoadDate function to work with the rest of your program so you will have to decide if declaring them in the function or as parameters to the function is the way to go.

    Note you may want to declare the r & c variables as Doubles seeing how the integer has a max value of 32, There are more rows in a worksheet then 32K.

  • Karthikeyan,

    There are several issues with your code. You might get futher if you specified where you want the Cells() method to work -- for example:

    sprers.eu(r, c)sprers.eu = vbBlack

    In addition, you might want to learn some more efficient coding styles. Rather than making each test execute the same set of export contacts error wm5, simply use a Boolean value to accumulate your results, then have a single conditional to determine if you want to change the cell:


    Public Sub LoadDate
    Dim foundError As Boolean
    Dim testCell As Range

    foundError = False
    Set testCell = sprers.eu(r, c)
    foundError = foundError OR NOT IsNumeric(testCell)
    foundError = foundError OR Len(testCell) <> 8
    foundError = foundError OR Left(testCell, 4) > ""
    foundError = foundError OR Left(testCell, application defined or object defined error vba, 4) < ""
    foundError = foundError OR Right(testCell, 2) > "31"
    foundError = foundError OR Right(testCell, 2) < "01"
    .
    .
    .
    If foundError Then
    Cells(r, c)sprers.eu = vbRed
    Cells(r, c)sprers.eu = True
    Else
    Cells(r, application defined or object defined error vba, c)sprers.eu = vbBlack
    Cells(r, c)sprers.eu = False
    Cells(r, c)sprers.eu = vbWhite
    End If
    End Sub


    However the single biggest problem you have is that you are declaring your procedures as Functions when they should be Subs. Your code is failing on the first statement of LoadDate because there is no assignment statement setting the return value.

    In any event, as Andrija already suggested, you should learn about Data Validation. On the worksheet, select the cells that you want to restrict to dates, then select Data Validation from the menu or ribbon. In the Data Validation window, specify that the cells allow only Date values, and then specify the beginning and ending dates of the allowable range. You can also specify a message to be displayed (as a pop-up) when the user selects one of the cells (for example, a message reminding the user that the cell only accepts dates), and a message to be displayed if the user makes an invalid entry.

    Data Validation will do a far better job (it is more immediate and more definite, as well as more accurate) than code in the Worksheet_Change event.

    Michael S. Meyers-Jouan

  • Hi,
    follows proper and tested procedure; copy/paste code in your
    corresponding worksheet module:
    '--
    Option Explicit

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim n
    Const ciCOL20 As Integer = 20
    Const ciCOL37 As Integer = 37
    Const ciCOL54 As Integer = 54
    '--
    n = 0
    On Error Resume Next
    n = sprers.eu( _
    sprers.eu, Array( _
    ciCOL20, ciCOL37, ciCOL54), 0)
    On Error GoTo 0
    '--
    If n <> 0 Then
    ' Columns 20, 37, 54
    ' validate input
    Call ValidateInput(Target)
    Else
    ' other columns; exit sub
    Exit Sub
    End If
    End Sub
    '--
    Private Sub ValidateInput(rng As Range)
    ' Customize procedure as you need;
    ' add more validation criterias
    '--
    Dim c As Range
    Const ciDTE_LEN As Integer = 10 ' customize date length
    '--
    For Each c In sprers.eu
    If Not IsDate(c) Then
    sprers.eu = True ' Bold
    sprers.eundex = 3 ' Red
    ElseIf Len(sprers.eu) <> ciDTE_LEN Then
    sprers.eu = False
    sprers.eundex = 3 ' Red
    Else
    sprers.eu = False
    sprers.eundex = xlColorIndexAutomatic
    End If
    Next c
    End Sub
    '--
    Best Regards

    Andrija Vrcan

  • Sorry guys, some what i am busy in another work.

    I will try your suggestions.

    Regards,
    Karthikeyan T

  • What are your thoughts on the concept of "quiet quitting"?

    What are your thoughts on the concept of "quiet quitting"?

    IT & Tech Careers

    Whether you were surfing social media, listening to a podcast/news, or just chatting with friends, you have probably heard of the term/concept of "quiet quitting". I know this isn't a new term/concept but it has seen a recent surge in discussion especiall

  • How Old is too Old (Servers, not staff;-)

    How Old is too Old (Servers, not staff;-)

    Hardware

    kind of a re-hash of an old question (), I'm currently removing old kit from our racks & given some of it is now rapidly heading for 13 years + old & has only just been removed from service (Poweredge anybody?) I was wondering what the consensus

  • Snap! Chrome clipboard bug, private cellular, Win11 Start, IT Pro AI art, & more

    Snap! Chrome clipboard bug, private cellular, Win11 Start, IT Pro AI art, & more

    Spiceworks Originals

    Your daily dose of tech news, in brief. Welcome to September ! In some ways, it feels like it's taken forever to get here; in other ways, time is flying by way too quickly. You need to hear this. Google Chrome bug lets sites write to clip

  • Invalid operation for device '0'

    Invalid operation for device '0'

    Virtualization

    Good day fellow Spiceheads,I am struggling with a simple task, a disk expansion on a sprers.eu C drive expands no problem, but the D drive gives me the error as per the headline.I logged a call with VMWare and was advised to disable replication and then try

  • Best practice for SSL certificate for server in DMZ?

    Best practice for SSL certificate for server in DMZ?

    Security

    I have an application server in our DMZ that will be using: sprers.eut would be application defined or object defined error vba best route to provide a SSL certificate for it?  Our company website (sprers.eu) is hosted by a third party and has an SSL certificate sprers.eu

Application-defined or object-defined error

  • Article
  • 2 minutes to read

This message is displayed when an error generated with the Raise method or Error statement doesn't correspond to an error defined by Visual Basic for Applications. It's also returned by the Error function for arguments that don't correspond to errors defined by Visual Basic for Applications, application defined or object defined error vba. Thus it may be an error you defined, or one that is defined by an object, including host applications like Microsoft Excel, Visual Basic, and so on. For example, Visual Basic forms generate form-related errors that can't be generated from code simply by specifying a number as an argument to the Raise method or Error statement. This message has the following causes and solutions:

  • Your application executed an sprers.eun or Errorn statement, but the number n isn't defined by Visual Basic for Applications. If this was what was intended, you must use sprers.eu and specify additional arguments so that an end user can understand the nature of the error. For example, you can include a description string, application defined or object defined error vba, source, and help information, application defined or object defined error vba. To regenerate an error that you trapped, this approach will work if you don't execute sprers.eu before regenerating the error. If you execute sprers.eu first, you must fill in the additional arguments to the Raise method. Look at the context in which the error occurred, application defined or object defined error vba, and make sure you are regenerating the same error.

  • It may be that in accessing objects from other applications, an error was propagated back to your program that can't be mapped to a Visual Basic error.

Check the documentation for any objects you have accessed. The Err object's Source property should contain the programmatic ID of the application or object that sap sm50 error the error. To understand the context of an error returned by an object, you may want to use the On Error Resume Next construct in code that accesses objects, rather than the On Error GoToline syntax.

List trappable errors for the host application

In the past, programmers often used a loop to print out a list of all trappable error message strings. Typically this was done with code such as the following:

Such code still lists all the Visual Basic for Applications error messages, but displays "Application-defined or object-defined error" for host-defined errors, for example those in Visual Basic that relate to forms, controls, and so on. Many of these are trappable run-time errors. Use the Help Search dialog box to find the list of trappable application defined or object defined error vba specific to your host application. Click Search, type Trappable in the first text box, and then click Show Topics. Select Trappable Errors in the lower list box and click Go To.

For additional information, select the item in question and press F1 (in Windows) or HELP (on the Macintosh).

Support and feedback

Have questions or feedback about Office VBA or this documentation? Please see Office VBA support and feedback for guidance about the ways you can receive support and provide feedback.

Excel VBA run-time error Application-defined or object-defined error

Your comment on this question:

1 answer to this question.

Your answer

Your comment on this answer:

Related Questions In Database

Subscribe to our Newsletter, and get personalized recommendations.

Already have an account? Sign in.

webinarREGISTER FOR FREE WEBINARX
webinar_successThank you for registeringJoin Edureka Meetup community for + Free Webinars each monthJOIN MEETUP GROUP

The VBA Runtime Error can be caused by many things. In this article, I’ll show a few different situations and show how to deal with them.

Tutorial Content

VBA code refers to a range that doesn’t exist

This code usually happens when VBA code refers to a cell or range that doesn’t exist. For example, this code is correct.

Sub myFunc()     Set Rng = Worksheets("Sheet1").Range("Z3")     Set Rng2 = Worksheets("Sheet1").Range("ZZ3") End Sub

But when you try to access the following ranges, the program will return an error in both cases.

Sub myFunc()     '>>>Error "Application-defined or Object-defined error"     Set Rng = Worksheets("Sheet1").Range("ZZZ3")     Set Rng2 = Worksheets("Sheet1").Range("myRange") End Sub

The first line returned error because Excel contains only 16, columns and the last column is XFD. In the second case, we don’t have the “myRange” range. In this case, the application also returns the error. After you create the range, the error won’t show up.

You should really pay attention when you are using loops, especially for columns. You can go out of the index without noticing.

You are trying to enter a value that is not a range

The following code will result in an error because the program cannot recognize values inside the Range property as a range.

Sub myFunc()     '>>>Error "Application-defined or Object-defined error"     Set Rng = Worksheets("Sheet1").Range(4)     Set Rng2 = Worksheets("Sheet1").Range("4") End Sub

Select a worksheet explicitly

Sometimes when you choose a worksheet implicitly, you may have error.

Take a look at this code. Here, you selected the active sheet, but when you activate a different sheet this may result in an error.

Set wksSource = sprers.euSheet

Instead, use the code when you explicitly select sheet name.

Set wksSource = sprers.eu("Sheet1")

You are using protected mode

You may also get the error if your worksheet is in the protected mode. Disabling the protection might get rid of this error.

Macro recorded at the worksheet level

Sometimes you may get this error if you record a macro at the worksheet level. You should create a module and enter the code there. In order to do so, go application defined or object defined error vba VBE (Alt + F11) and right click on the module’s icon. Insert a new module and enter your code there.

These situations are the most common for error to occur. I hope it fixed your problem.

Post Views,

How do I fix application-defined or object defined error in VBA?

Sometimes you may get this error if you record a macro at the worksheet level. You should create a module and enter the code there. In order to do so, go to VBE (Alt + F11) and right click on the module&#;s icon. Insert a new module and enter your code there.

What is an object defined error?

VBA Error is a runtime error in VBA which is also known as application-defined or object-defined error and why is that because we have limited number of columns in excel and when our code gives the command to go out of range we get error, there are other situations when we get this error when we refer to a &#;

What is application defined error in VBA?

Application-defined or operation-defined error. This issue may occur if one or more of the cells in an array (range of cells) contain a character string that is set to contain more than characters.

What is object variable or with block variable not set error?

&#;Object variable or With block variable not set&#; is a Visual Basic error message. Roughly, it means that the VBA code used some variable without first initializing it properly.

How do you stop VBA error messages?

Do the following in your VBA editor window (entitled &#;Microsoft Visual Basic for Applications&#;): Click the menu &#;Tools&#; and then &#;Options&#. In the Options&#; &#;Editor&#; tab, uncheck the &#;Auto Syntax Check&#; box. (See screenshot, application defined or object defined error vba, below.)

What is Error Excel?

Excel error occurs when Microsoft Excel fails or crashes while running. This can be caused by many factors. Microsoft Excel-connected files maliciously or wrongly deleted by another program. Error in a macro; or the macro that you are trying to application defined or object defined error vba is corrupt.

What does Runtime Error 91 mean?

Object variable not set
Error: &#;Runtime Error 91&#; is a Visual BASIC error which means &#;Object variable not set&#. This indicates that the object was never created using the &#;Set&#; command before being used. Remedy: Be sure to use the SET statement to create the new oject.

How do I create a VBA function in Excel?

How to Create a Custom User Defined Function Open a new Excel workbook. Get into VBA (Press Alt+F11) Insert a new module (Insert > Module) Copy and Paste the Excel user defined function examples Get out of VBA (Press Alt+Q) Use the functions &#; They will appear in the Paste Function dialog box (Shift+F3) under the &#;User Defined&#; category

How to use the VBA editor in Excel?

First,open the Excel worksheet. You can do that by double-clicking on the Excel file.

  • In the Excel window,click on the “File” option and then click on the “Options” option on the sidebar. This action will open the Excel settings window.
  • In the options window,select the “Customize Ribbon” tab on the sidebar.
  • What is error handling in VBA?

    Error Handling In VBA. The goal of well designed error handling code is to anticipate potential errors, and correct them at run time or to terminate code execution in a controlled, graceful method. Your goal should be to prevent unhandled errors from arising.

    What is application defined error?

    Application defined or object defined error. The Application defined or object defined error vba a mistake objects. Number house receives loaded with the actual Signal number any time an error will be signaled. Your own error handler then are able to use the actual Go overboard objects some other attributes (.Description, as an example) to stdin error 0 ubuntu 9.10 how to handle the problem.

    How to fix the Runtime Code Application-defined or object-defined error

    Last Updated:

    31/08/22 : A Windows 10 user voted that repair method 2 worked for them.


    Recommended Repair Tool:


    This repair tool can fix common computer problems such as blue screens, crashes and freezes, missing DLL files, as well as repair malware/virus damage and more by replacing damaged and missing system files.


    STEP 1:
    Click Here to Downloadand install the Windows repair tool.

    STEP 2:
    Click on Start Scanand let it analyze your device.

    STEP 3:
    Click on Repair Allto fix all of the issues it detected.

    DOWNLOAD NOW

    Compatibility

    Requirements
    1 Ghz CPU, MB RAM, 40 GB HDD
    This download offers unlimited scans of your Windows PC for free. Full system repairs start at $

    Article ID: ACXEN


    Applies To: Windows 10, WindowsWindows 7, Windows Vista, Windows XP, Windows



    Speed Up Tip #7

    Turning Off Indexing in Windows:

    One way to boost your Windows computer's performance is by turning off indexing. By default, the Windows Search indexing service is turned on. By turning it off for all local drives, you will get that increased speed you want.

    Click Here for another way to speed up your Windows PC

    1. Oct 16th, ,  AM#1

      ssabc is offline

      Thread Starter

      Fanatic Member

      Error - Application-defined or object-defined error VBA Excel

      This is killing me! Error - Application-defined or object-defined error.

      I have looked up several references, none seem to work. I would like to not use Range.

      Code:

      Dim wb As Workbook Set wb = ActiveWorkbook Dim i Runtime error microsoft vi Integer i = 1 Do While Not sprers.eu sprers.eu(i, 0).Value = (sprers.eu("JobNo")) sprers.eu(i, 1).Value = (sprers.eu("ShipTo")) sprers.eu(i, 2).Value = (sprers.eu("Product")) sprers.eu(i, 3).Value = (sprers.eu("Detail")) sprers.eu(i, 4).Value = (sprers.eu("Feet")) sprers.euxt i = i + 1 Loop
      Thanks!

      - A 'Hyperactive Member' trying to make a difference in a hyperactive world! And recently, I've been promoted to a 'Finatic Member,' whatever that means!


    2. Oct 16th, ,  AM#2

      ssabc is offline

      Thread Starter

      Fanatic Member

      Re: Error - Application-defined or object-defined error VBA Excel

      This must be clearly rocket science. How to put cells from a database (verified reading correctly) into Excel fields???

      What am I missing?

      Code:

      Public cn As sprers.eution Public rs As sprers.euset Sub GetJobs_Click() Dim connectionString As String ' connectionString = "Provider=sqloledb;Data Source=w;Initial Catalog=M1_SU;User Id=m1view;Password=connect2m1;" connectionString = "Provider=sqloledb;Data Source=sage\SQLEXPRESS;Initial Catalog=JobSheet;Integrated Security=SSPI;Persist Security Info=True;" Set cn = New sprers.eution Set rs = New sprers.euset Dim sql As String sql = "SELECT * From [JobSheet].[dbo].[JobInfo] ORDER BY JobNo " sprers.eutionString = connectionString sprers.eu (connectionString) sprers.eu sql, cn, adOpenKeyset, adLockReadOnly, adCmdText sprers.eurst Dim i As Integer i = 1 Do While Not sprers.eu sprers.eu(i, 0).Value = (sprers.eu("JobNo")) sprers.eu(i, 1).Value = (sprers.eu("ShipTo")) sprers.eu(i, 2).Value = (sprers.eu("Product")) sprers.eu(i, 3).Value = (sprers.eu("Detail")) sprers.eu(i, 4).Value = (sprers.eu("Feet")) sprers.euxt i = i + 1 Loop sprers.eu Set rs = Nothing sprers.eu Set cn = Nothing End Sub

      - A 'Hyperactive Member' trying to make a difference in a hyperactive world! And recently, I've been promoted to a 'Finatic Member,' whatever that means!


    3. Oct 16th, ,  AM#3


    4. Oct 16th, ,  AM#4

      ssabc is offline

      Thread Starter

      Fanatic Member

      Re: Error application defined or object defined error vba Application-defined or object-defined error VBA Excel

      Bingo! Thank you so much!!!

      - A 'Hyperactive Member' trying to make a difference in a hyperactive world! And recently, I've been promoted to a 'Finatic Member,' whatever that means!


    5. Oct 24th, ,  AM#5

      sprers.euin is offline
      New Member sprers.euin's Avatar

      Re: Error - Application-defined or object-defined error VBA Excel

      Hi.
      The problem is solved here, but I am just adding some stuff, application defined or object defined error vba, out of passing interest…

      I am not % sure about this, but I think Excel finds that problem giving Cells(i, 0) syntactically OK, ref!!.
      But it can’t find a range object in row 1 that is one column to the left of column A in a worksheet ref!!
      I think it is that problem that chucks up the error.

      A bit more info, just out application defined or object defined error vba interest… _
      _ I am considering two different things which it is very easy to get mixed up
      _ Excel referencing spreadsheet cells, which is mostly a “relative referencing thing” of one sort or another, even if it 1c7 runtime error not appear to be at first glance, ref!!
      _ … How Excel takes arrays from ranges and pastes arrays into ranges


      First, a quick review of Excel spreadsheets and its conventional doings with arrays…
      Excel, when doing interactions with spreadsheet ranges and arrays, application defined or object defined error vba, has a general convention of considering spreadsheet range values in a 2 D array, the first dimension refers typically to the horizontal row, and the second dimension refers typically to the vertical column. ( Conventionally/ usually the first row and application defined or object defined error vba column is taken as indicia 1, (it does not necessarily always have to be when talking about the arrays associated with a 2 D range Area ) ).
      Application defined or object defined error vba pseudo like
      Arr(row,column) = {1 To n1, 1 To n2}
      As OptionBase1 said, conventionally the first row and first column is taken as indicia 1. Id 184 end-to-end error this is when referencing cells in an entire spreadsheet- There is a small subtlety there ref!!. We are talking there about referencing spreadsheet cells, which is actually a relative thing as explained in ref!!

      **Also you need to be a bit careful when taking in or pasting out an array into a spreadsheet since its handled a bit differently as regard indicia:
      Excel will conventionally make an array filled from a spreadsheet as starting at first and second dimension ( pseudo “row” and “column” ) indicia of 1regardless of where the range is.
      The other way around is similarbut a bit different. You can, application defined or object defined error vba, if you want, have indicia starting at other than 1, but it doesn’t matter where your indicia start, as regards pasting out: If you paste your 2D array out into a spreadsheet, it starts always at top left of the range where you paste it to: Top left is top left regardless of what you application defined or object defined error vba chosen to be the start indicia of the 2D array that you are pasting out.

      Now. How a spreadsheet handles 1 D arrays / relevance to this Thread
      _sprers.eu seems that a lot of computer things in general that give out arrays of 1 dimension, often have their first integer indicia thing set at 0, at least by default, ref##
      I suppose technically speaking a 1 D array has no orientation, but some of us often think conveniently in our human minds of it as looking “horizontal” or like in a row. Excel goes along with that way of thinking sometimes, and in many things to do with a spreadsheetif you pass a 1 D array, (even one with a first indicia thing set at 0 or any other value )then it does the same as it would if you gave it a 2 D array where the first dimension had a dimension of 1, ( Note: I said dimension of 1 there, not indicia ). Saying the same thing in more simple, (not necessarily perfectly technically correct ) terms: In spreadsheet interactions, application defined or object defined error vba, Excel will often take a 1 D array as if it was a 2 D1 “row” array, or 2D 1 “horizontal” array,
      Pseudo {0 to n2} taken as if it were {1 To 1, 1 To n2+1},
      or
      pseudo {0 to n2} taken as if it were {7 To 7, 1 To n2+1}
      or
      pseudo {0 to n2} taken as if it were {9 To 9, 65 To n2+65}
      or
      pseudo {0 to n2} taken as if it were {8 To 8, 0 To n2 }
      etc. etc.
      ( using n2 rather than n is just a personal convention of mine. It helps me keep my brain orientated in the perception that its referring to something to do with a column number in a row, and it either is associated withor is pseudo equivalent to, a second dimension in a 2D array. That thinking is all technically dodgy, I know, but thinking that way usually gets you by, when dealing with the things I am rambling on about )
      ( Unfortunately Excel VBA has some String functions that came from VB which insist on a 1 D array: That can be a Bummer – since those functions wont take any of those 2 D1 “row” arrays instead )

      Coming back to the relevance to the OPs issue, or similar issues…

      In simple terms, if you had a 1 D array with values in it, ( even one starting at indicia 0 ), which you wanted to paste as a row in consecutive cells into a spreadsheet range, application defined or object defined error vba, then you can do it directly in a single code line, pseudo like

      ___MyRnge = My1DArray()

      Instinct might tell you that the last code line sounds a bit dodgy. Maybe it should be, but VBA has seemingly just been written such that it conveniently usually does what you want without any problems, in such a case!
      The code snippet below perhaps explains it a bit better, if you run it from the VB Editor in step ( key F8 ) mode.

      Code:

      ' sprers.eu?ErrorApplication-defined-or-object-defined-error-VBA-ExcelSub OneD_TwoD_Arrays() Rem 0 worksheets info, use arbritrary worksheet for demos. Dim Ws As Worksheet: Set Ws = ActiveSheet ' You don't have to do this. You can forget it and miss out all the Ws. bits But that can catch you out sometimes, when you might end up referrencing a range in a different worksheet to that which you thought you were Rem 1 make arbritrary 1 D array '1_a) long wayDim arr1D_a(0 To 4) AsStringLet arr1D_a(0) = "BigJob": arr1D_a(1) = "Baveria": arr1D_a(2) = "Computer": arr1D_a(3) = "OldOne": arr1D_a(4) = "42" '1_b) quick way using VBA Array functionsprers.eu arr1D_b() AsVariant' The array needs to be dynamic and needs to have Variant type, because_ _. the next code line function returns a field of elements which are housed in elements of Variant typeapplication defined or object defined error vba, so the type must be Variant regardles of what types are withing the elements or we will get type mismatch errorsand _. the working of the function is such that it sends infomation to size the thing taking it: If we have a fixed size array as in _b then Excel will error becuse Excel will insist on wanting to do this sizing of any recieving array, which it can't do if the array is fixed sprers.eu arr1D_b() = Array("BigJob", "Baveria", "Computer", "OldOne", "42") ' This returns us a 5 element array starting at indicia 0 So we have now arr1D1_b(0) = "BigJob": arr1D_b(1) = "Baveria": arr1D_b(2) = "Computer": arr1D_b(3) = "OldOne": arr1D_b(4) = "42" Rem 2 paste arrays into spreadsheet sprers.euontents Let sprers.eu("C1:G1").Value = arr1D_a() ' Top left of where the 5 column, single row is pasted is cell C1Let sprers.eu("D2:H2").Value = arr1D_b() ' Top left of where the 5 column, single row is pasted is cell D2 Rem 3 Excel Spreadsheet array interaction: '3a) usually Excel makes a 2D array from a range with indicia starting at (1, 1)Dim arr2D1Row_a() AsVariant' In the next code line, I am uisng the .Value property applied to a multi cell rectangular single area range. When i do that. Value is programmed to return me an array of values which as far as it knows might be number values or text values, or formulas or blanks or vb errors. To acccomadte the possibility of us distinguisching later what type we have, all elelments in the returned array are Variant because only Variant can "hold" all the different types in it. (a number can go in a number type variable, (Single Double Long Integer) or a Variant type variable; a text can go in a String type or a Variant type etc. etc). So, because Excel is programmed to return an array of Variant type Elements, I must decalre my array, arr2D1Row_a()as variant types, or else i will get a type mismatch error at the next code lineLet arr2D1Row_a() = sprers.eu("D2:H2").Value ' Excel decides the array type in this situation. It will always be a 2D 1 "row" array {1 To 1, 1 To 5} Nothing you can do will influence this'3b) When pasting out, top left is always top left of the range you specify, Excel doesn't care what indicia you've got in the array being pasted outLet sprers.eu("E3:I3").Value = arr2D1Row_a() ' Top left of where the 5 column, single row is pasted is cell E3Dim arr2D1Row_b(7 To 7, 42 To 47) AsString' A 2D 1 "row" array with arbritrary inidia: I can decide this but _ Excel doesn't care what indicia you have chosen when pasting outLet arr2D1Row_b(7, 42) = "BigJob": arr2D1Row_b(7, 43) = "Baveria": arr2D1Row_b(7, 44) = "Computer": arr2D1Row_b(7, 45) = "OldOne": arr2D1Row_b(7, 46) = "42" Let sprers.eu("F4:J4").Value = arr2D1Row_b() ' Top left is always top left, cell F4 in this case. application defined or object defined error vba Excel doesn't care what indicia you have chosen when pasting outEndSub


      I suppose the bottom lines are
      It seems generally that cell referencing in Excel and Excel VBA is based on relative references, even when it might not appear to be at first glance.
      Arrays on the other hand have a fixed indicia to refer specifically to specific elements in the array, but as far as Excel is concerned they are “floating around”, orientation-less, and what I tried to do here is explain a bit about how Excel lets you put them into or take out of a spreadsheet in one go without looping, application defined or object defined error vba can be very useful in making coding more efficient
      ( Couple that knowledge with a good understanding of how the Excel and Excel VBA Index function can work in manipulating arrays in its extended form of having row and column arguments as arrays, …_
      __Index( Arr()Rws(), Clms() )
      _ then you can write some coding that knocks the socks off some other array/ spreadsheet manipulating coding in VBA )

      Alan





      ref## In VBA, you can change the typical start indicia of 1 dimensional producing things from the default 0 to 1
      One way to do that is to write the Username of a certain VB forums member at the top of your code module like this
      In the case of the macro example above, that will make no change what so ever to the results.
      There is a small change to the workings: That change is in the code line that uses the VBA Array function,
      arr1D_b() = Array("BigJob", "Baveria", "Computer", "OldOne", "42")
      Previously it gave me this:
      arr1D1_b(0) = "BigJob": arr1D_b(1) = "Baveria": arr1D_b(2) = "Computer": arr1D_b(3) = "OldOne": arr1D_b(4) = "42"
      sprers.eu
      Name: arr1D_b()sprers.eu Views: Size: KB

      Now it gives me this:
      arr1D1_b(1) = "BigJob": arr1D_b(2) = "Baveria": arr1D_b(3) = "Computer": arr1D_b(4) = "OldOne": arr1D_b(5) = "42"
      sprers.eu
      Name: arr1D_b()sprers.eu Views: Size: KB

      ( I get that Watch Window if I highlight any of the arr1D_b() bits of thee coding and then hit keys Shift+F9.
      Having done that, then after I have stepped past the code line that fills that array, the contents will be shown in that Watch Window after hitting that + symbol )

      **There is no effect on any of the pasting out done in the macro, because top left is always top left of the range you specify, and Excel doesn’t care what indicia you’ve got in the array being pasted out, ( be it a 1 D arrayor even a wildly indicied 2 D array, application defined or object defined error vba, as in the last example in the macro )





      ref !! It is a bit more complicated if you are referencing a range somewhere in a spreadsheet other than at the origin, cell A1, since then you can “go backwards from the origin” of the range you are at. Like
      Range("B10").Cells(0, 0) is like Range("A9")
      or
      Range("B10").Cells(-1, 0) is like Range("A8").
      Something like,
      Range("B10").Cells(0, -1)
      willin this example, give you your Error - Application-defined or object-defined error. In this example its because you are trying to go 2 columns back from column B
      ( You cant really do something like Cells(4, 5) in VBA. Well you canI know, but that is just because VBA is kind and puts in the bit you missed. Effectively its doing something pseudo like
      Worksheets(“MySheet”).Cells(4, 5)
      So its giving you a cell that is at (4, 5) relative to the first cell in a worksheet, in such a case. Cells is a property that must be applied to a range object. If you forget to give it one, Excel takes a guess at what you wanted to apply it to
      And if you want to get really picky, we usually miss something else out. Cells has a few properties and other stuff. We are using the Item Property. We should do this:
      Worksheets(“MySheet”)sprers.eu(4, application defined or object defined error vba, 5)
      Once again, VBA is kind, in this case, and assumes that’s what we meant )


      It seems generally that cell referencing in Excel and Excel VBA is based on relative references, even when it might not appear to be at first glance.
      Arrays on the other hand have a fixed indicia to refer specifically to specific elements in the array, but as far as Excel is concerned they are “floating around”, and what I tried to do here is explain a bit about how Excel lets you put them into or take out of a spreadsheet in one go without looping, which can be very useful in making coding more efficient

      Last edited by sprers.euin; Oct 24th, at AM.


    6. Oct 24th, ,  PM#6

      sprers.euin is offline
      New Member sprers.euin's Avatar

      Re: Error - Application-defined or object-defined error VBA Excel

      The last post was a bit general. Maybe I could add a short more specific application of the ideas to the OPs macro.
      This is the bit to concentrate on

      Code:

      DoWhileNot sprers.eu sprers.eu(i, 0).Value = (sprers.eu("JobNo")) sprers.eu(i, 1).Value = (sprers.eu("ShipTo")) sprers.eu(i, 2).Value = (sprers.eu("Product")) sprers.eu(i, 3).Value = (sprers.eu("Detail")) sprers.eu(i, 4).Value = (sprers.eu("Feet")) sprers.euxt i = i + 1 Loop
      We could do this

      Code:

      Dim arrTemp() AsVariantDoWhileNot sprers.eu Let arrTemp() = Motorola v360 critical error 10 39, sprers.eu("ShipTo"), sprers.eu("Product"), sprers.eu("Detail"), sprers.eu("Feet")) Let sprers.eu("A" & i & ":E" & i & "").Value = arrTemp() sprers.euxt Let i = i + 1 Loop
      Or this,

      Code:

      Dim arrTemp() AsVariantDoWhileNot sprers.eu Let arrTemp() = Array(sprers.eu("JobNo"), sprers.eu("ShipTo"), sprers.eu("Product"), sprers.eu("Detail"), sprers.eu("Feet")) With sprers.euSheet .Range("A" & i & ":E" & i & "").Value = arrTemp() EndWith sprers.euxt i = i + 1 Loop
      Often in VBA we “cut out the middle man”
      In this case that would mean directly assigning the array after its made to the worksheet row. So finally we would have this

      Code:

      DoWhileNot sprers.eu Let sprers.eu("A" & i & ":E" & i & "").Value = Array(sprers.eu("JobNo"), sprers.eu("ShipTo"), sprers.eu("Product"), sprers.eu("Detail"), sprers.eu("Feet")) sprers.euxt Let i = i + 1 Loop
      ( I do note that the OP said he did not want to use Range, but I am not quite sure what he meant by that. )

      I am not too clued up on ADODB stuff, but possibly there might be a way to get out a row array, in which case the thing would be a bit simplified further


      Alan

      Last edited by sprers.euin; Oct 24th, at PM.


    7. Oct 25th, ,  AM#7


    8. Oct 26th, ,  AM#8

      sprers.euin is offline
      New Member sprers.euin's Avatar

      Re: Error - Application-defined or object-defined error VBA Excel

      I have never used that copyfromrecordset thing, Pete. I really don’t know too much about anything ADO, ADODB
      If I understand a quick look at the documentation, ( sprers.eu )that copyfromrecordset thing seems to let you paste out all your “data” in one go to a spreadsheet.
      I assume the “data” is something that crudely to the uneducated mind like mine is something that very approximately could be considered as
      Record = approximately rows
      Field = approximately columns
      Recordset = approximately a set of rows = approx like 2D array of data = approx like spreadsheet range Area of data

      So I will take a guess that what that copyfromrecordset thing does, is
      _a) somehow convert that “data” ( using ADO or god knows what )into something it and/ or VBA recognises as a 2D VBA array, say, arrFromRecordset()
      _b) it works out the range size for you, so that you only application defined or object defined error vba to pass it top left of where you want it.

      I guess then it effectively does something application defined or object defined error vba this: ( This is all pseudo like )

      sprers.eu( sprers.eu(1, 1)sprers.eu(sprers.eu, sprers.eu) ) = arrFromRecordset ()

      ( in this example, I assume you gave the copyfromrecordset thing a top left of A1. So like you gave
      sprers.eu("A1").CopyFromRecordset rs__ )

      In other words
      sprers.eu("A1").CopyFromRecordset rs
      is approximately pseudo like
      sprers.eu( sprers.eu(1, 1)sprers.eu(sprers.eu, sprers.eu) ) = arrFromRecordset()
      where arrFromRecordset() is something we might recognise as a simple VBA 2D array which the copyfromrecordset thing made for us from our ADO “data”

      That makes some sense if you consider the two optional arguments it has of
      maximum rows/records
      maximum columns/fields
      What I mean is, the method is making the range to paste out into. So then it is very simple to allow those options, since you just make the bottom right pseudo
      sprers.eu(maximum rows/records, maximum columns/fields)
      Instead of
      sprers.eu(sprers.eu, sprers.eu)

      So if I have that all approximately correct, then that is probably a very efficient way of getting all your data into a worksheet range, ( assuming Microsoft did a good development job on it… )


      I note again that the OP said he didn’t want to use range. I wonder if he meant he did not want to use the copyfromrecordset thing since it is the Range.CopyFromRecordset method
      I expect only a sub set of the data was wanted, possibly just some “column” data

      What would be useful would be to able to get direct access to that arrFromRecordset()
      I expect we can’t. That is internally made and used internally only
      We can do that indirectly: We could paste it all out using the copyfromrecordset thing.
      Then take that data into an array, arrIn()
      Then Clear that data
      Then paste the subset of rows and columns you want in one go using the Index code line like
      ____Index( arrIn(), Rws(), Columns() )

      I suppose the obvious comment to that is “why not paste it all out and then delete what you don’t want”
      In a lot of cases you probably might as well do that.
      But deleting rows and columns in a spreadsheet can sometimes take a while, even with coding, as interactions with a spreadsheet are often very slow.
      On the other hand the alternative I suggest can be very efficient in comparison.
      Just to clarify what that alternative to deleting stuff would be: It would be this:
      _ take all info in, from the spreadsheetin one go,
      _ then using coding to get your wanted arrOut()via
      _arrout()=Index( arrIn(), Rws(), Columns() )
      _ Then pasting out that array in one go

      ( arrrIn() is effectively arrFromRecordset() - What I am doing here is getting it indirectly, since the copyfromrecordset thing used that, but doesn’t give me direct access to it )

      ( You can “cut out the middle man”, in other words I don’t need to make a arrOut(), application defined or object defined error vba pseudo, I can directly do this instead
      MyWantedDataRange= Index( arrIn(), application defined or object defined error vba, Rws(), Columns() )__ )



      At the end of the day it could be either
      _ all splitting hairs
      _ or not
      Just depends I suppose how much data you are playing with and how often you do it.


      I am just mentioning a few possibilities, discussing alternative, that’s all.
      I am pretty ignorant about data analysis, data bases and stuff.
      ( I had a massive amount of data to get at and organise for an important personal project a few years ago.
      I got some great help in forums with using advanced data basesincluding some ADO, SQL stuff, that I couldn’t begin to understand
      A few years later I re did a lot of it using more simple things including some of the Index( arrIn(), Rws(), Columns() ) ideas and a few other unconventional thingsoften using VBA in unusual ways.
      In some cases it worked a lot better, and most people told me it wouldn’t …. )

      Alan

      Last edited by sprers.euin; Oct 26th, at AM.


    9. Oct 27th, ,  AM#9


    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •  

    Forum Rules




    Click Here to Expand Forum to Full Width


    0 Comments

    Leave a Comment