Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    Lou_Reed is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2015
    Posts
    1,664

    Excel VBA code will not compile

    In the VBA code associated with the Excel spreadsheet the VBA sub does not compile. It says that cell is not defined.

    i got this VBA code off the internet and I would expect it to be correct. Anyway, it does not compile because of that error or bug.

    This code is to go down a selected column in the Excel spreadsheet and add a apostrophe to each cell making the number now a text value.

    That way it should import into Excel since it is all one type of cell - text. Hence no "Type Conversion Failure" error messages.

    But at this point it will not even compile. I must define cell, but i would expect t to be a reserved word and not need to be defined.

    Any help appreciated. Thanks in advance.



    Respectfully,

    Lou Reed
    Attached Files Attached Files

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    dont set cell.formula = & cell.value
    set: cell.value = "'" & cell.value

  3. #3
    Lou_Reed is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2015
    Posts
    1,664
    i made the changes. They are in the attached zip file that is the Excel spreadsheet.

    It still says that cell is undefined!

    I am not sure what it means. Cell isclearly not defined. But I have seen no
    example where cell is defined.

    Any help appreciated. Thanks in advance.

    Respectfully,

    Lou Reed
    Attached Files Attached Files

  4. #4
    Lou_Reed is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2015
    Posts
    1,664
    I am sorry to have to ask this question again, but i am stuck on this. The VBA code will not compile. It keeps saying cell variable is not defined. I just do not know what to do with it
    to get rid of the error.

    The db and the screen capture of the error are in the previous post.

    Any help appreciated. Thanks in advance.

    Respectfully,

    Lou Reed

  5. #5
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    what don't you understand about a variable being undefined?

    Where have you defined cell? what are you defining it as? as a Dim? Static? Const? Integer? string?

    You might find this link helpful

    https://msdn.microsoft.com/en-us/vba...property-excel

    Or remove option explicit - it will then compile - but may well generate errors further down the line

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Declare the variable:

    Code:
    Dim c As Range
    For Each c In Selection
        c = "'" & c.Value
    Next c
    or set formatting:

    Selection.NumberFormat = "@"
    Last edited by June7; 12-09-2017 at 01:56 PM.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    Lou_Reed is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2015
    Posts
    1,664
    I understand a lot about programming. I understand that a variable must be defined before you use it. I got this three line code from a website. I assumed it would work. It choked where I pointed out. I am not good at understanding VBA and there was the problem.

    Sometimes variables do not have to be defined before they are used such as in FORTRAN or if the variable is actually a reserved word. That was what I was getting at.

    I what I saw and where I got the 3 line VBA code, the narrator never defined cell. Not once. So that is how I wrote it.

    Thanks very much for the suggestion. I appreciate it.

    I used to program for a living, I think I know a lot about it, but VBA (especially for Excel) is very new.

    Respectfully,

    Lou Reed

  8. #8
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    I understand that a variable must be defined before you use it.
    with VBA, if your module has Option Explicit at the top then variables must be defined. If it does not have Option Explicit at the top, they do not need to be defined - but can lead to other issues. It is recommended you use Option Explicit (for any VBA, not just Access and Excel) as a default.

    You module has option explicit at the top, so variables need to be defined.

    If you are going to copy scraps of code from the internet, it may not be set up in the same environment e.g. the author of this code may not have been using option explicit, or it was just 'free typed' to illustrate some point with potentially all sorts of errors when just copy and pasted. You should not expect it to be correct.

    And please be aware that Excel is not Access, the cells in themselves are not typed as text or number as such.

  9. #9
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Did more experimenting. Found that declaring the variable as Range allowed not using .Value property. Without declaring the variable had to use .Value with the left side c.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  10. #10
    Lou_Reed is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2015
    Posts
    1,664

    Where is data

    I made the code changes that were suggested. The VBA code does compile.

    I have used cntrl-t to activate it. I get the following error shown in the

    jpeg file attached in zipped form.

    I have attached the MS Access 2010 db as in zipped form also.

    Now it seems that It cannot find the data. That is incorrect as I have selected it before I pressed cntrl-t.

    I am not sure what is wrong.

    Any help appreciated. Thanks in advance.

    Respectfully,

    Lou Reed
    Attached Files Attached Files
    Last edited by Lou_Reed; 12-11-2017 at 08:55 AM. Reason: correction

  11. #11
    Lou_Reed is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2015
    Posts
    1,664
    Okay, I just changed the way to activate the VBA code from cntrl-t to a form control button. That seems to work.

    I do not know why the previous option did not, but I have something that works.

    It changes only the number to the format 'number and leaves all of the text alone. So then I just import the Excel sheet into MS Access. It works with no errors, but I think that something is wrong.

    The table does not look like the excel worksheet, some numerical values are missing. Since there were no errors what is going on. It seems since all worksheet values transferred with no errors, nothing would be left out. But something seems left out.

    Any help appreciated. Thanks in advance.

    Respectfully,

    Lou Reed
    Attached Files Attached Files

  12. #12
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Really simplify download if all those files were in one zip. And I don't mean zips inside a zip.

    Worksheet is not structured for import to relational database - subtotal rows and multi-row headers and mid-sheet headers and sub-headers that need to be data in a group of records. The result is messy.

    I did the import and every row is retrieved. However, Site Count items 1 through 49 are at the end of the import. Very weird.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  13. #13
    Lou_Reed is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2015
    Posts
    1,664
    In an earlier project, I simple deleted all but one row in the title area and designated the first row as a title row. But, there was only one title area in that project. I thought that adding these (numbers to text in the current spreadsheet) could cause a problem with the current spreadsheet's import to MS access db. I guess I was correct.

    I can remove all but one title in each title section (delete all rows but one). As I said this is how I did it in an earlier project. It seemed to work then.

    Some of the rows in the spreadsheet are blank will that have an effect?

    This is a more complicated spreadsheet than one I had earlier so I will treat it differently. As I said the titles are multiline and the are in many places.

    I thought that if everything was text then it would just import in to MS Access and that is what I did.

    I had a button on the spreadsheet that essentially converts all numbers to text, by adding an ' in front of each number in each occurrence. This make it a text file and then I can import with no problems. Or can I?

    I thought that if everything was text the import function in MS Access would import with no issues.

    Once it is in MS Access, I can convert to fields back to what they were before importation.

    Will this work.

    Any help appreciated. Thanks in advance.

    Respectfully,

    Lou Reed

  14. #14
    Lou_Reed is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2015
    Posts
    1,664
    What is wrong with changing every number in a Excel file cell to text by adding ' in front of each number. Then importing that Excel file into MS Access 2010 and converting everything that was changed back to its original format.

    Respectfully,

    Lou Reed

  15. #15
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    It worked for me. The apostrophes in front of numbers did not carry over. Blank cells were not an issue.

    No conversion is necessary and not even possible unless you first remove the alpha data from the field.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 08-10-2015, 11:53 AM
  2. Replies: 3
    Last Post: 05-09-2013, 09:17 AM
  3. Compile Error: Can't find out what's wrong with the code.
    By vickan240sx in forum Programming
    Replies: 4
    Last Post: 05-24-2012, 06:25 PM
  4. Compile error: code or function not defined
    By GeorgeBrown in forum Access
    Replies: 1
    Last Post: 09-19-2011, 10:25 AM
  5. Replies: 23
    Last Post: 03-26-2009, 06:50 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums