+ Reply to Thread
Results 1 to 17 of 17

Variable and Data Type Confusion

  1. #1
    Registered User
    Join Date
    01-30-2015
    Location
    Durham, England
    MS-Off Ver
    Microsoft 2007
    Posts
    31

    Variable and Data Type Confusion

    Hello everyone,

    I am struggling to understand the difference between different data types and when to use them. I have been playing with the CLng() option because my code kept popping up with an "Error Code '6' Overflow" window.

    However, depending on where I place the CLng it seems to make a massive difference to the output of my calculations and in some cases if I don't use CLng() the output = 0.

    I was wondering if you could check my code and see if I am dong the right thing by adding it to pretty much everything or if there is a more accurate/correct method to prevent "Error Code '6' Overflow" .

    I have attached my sheet incase that makes it easier for you because lots of my data comes from the sheet!

    Best wishes

    Sarah

    Please Login or Register  to view this content.
    Attached Files Attached Files

  2. #2
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Variable and Data Type Confusion

    Why are you using the type conversion function?

    Do you have numbers stored as text?
    If posting code please use code tags, see here.

  3. #3
    Registered User
    Join Date
    01-30-2015
    Location
    Durham, England
    MS-Off Ver
    Microsoft 2007
    Posts
    31

    Re: Variable and Data Type Confusion

    No I don't think I do have numbers stored as text (I hope note). I am using CLng() because I THINK when I take a value from a cell and multiply it with another cell or number the result is automatically stored as a single variable and so I keep getting "Error Code '6'" as the numbers I am creating from the calculations are too large.

    But I am not sure how to make the whole thing automatically ok with large numbers.

  4. #4
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: Variable and Data Type Confusion

    You realise Longs have no decimal places right?

  5. #5
    Registered User
    Join Date
    01-30-2015
    Location
    Durham, England
    MS-Off Ver
    Microsoft 2007
    Posts
    31

    Re: Variable and Data Type Confusion

    Is there something that I could replace all the CLngs with to allow for decimal places?
    And is having CLng() or the alternative decimals phrase wrapped around every term the only way of doing this?

  6. #6
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: Variable and Data Type Confusion

    You could do a find and replace for Cdbl() - which allows for decimals

  7. #7
    Registered User
    Join Date
    01-30-2015
    Location
    Durham, England
    MS-Off Ver
    Microsoft 2007
    Posts
    31

    Re: Variable and Data Type Confusion

    Thank you, that works well in some places but comes up with an error when I replace it in others, do you know why?

  8. #8
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Variable and Data Type Confusion

    What errors are you getting and where are you getting them?

    PS Are you sure you actually need to use a conversion function?

    PPS Have you tried setting the data type of the variables?

    The only variable you've actually set the type of is AT1, the rest will be typed as Variant by default.
    Please Login or Register  to view this content.

  9. #9
    Registered User
    Join Date
    01-30-2015
    Location
    Durham, England
    MS-Off Ver
    Microsoft 2007
    Posts
    31

    Re: Variable and Data Type Confusion

    Setting these variables doesn't seem to make a difference because I think the issue is where I am taking long numbers from my sheet (attached)
    Please Login or Register  to view this content.
    "Error Code '6' Overflow" pops up when I change certain CLng() in to CDbl(). For example below for aluminium layer I swapped a few CLng to CDbl and that was fine but for air bubble layer it pops up with error code. (Even though it is literally the same code 'copy and pasted' but with different cells utilised in the calculations). The cells utilised in Air bubbles use a decimal point so I thought that line of code would be more likely to need CDbl()

    Please Login or Register  to view this content.
    If I change the air bubble cell with a decimal place for some reason it pops up with "Error Code '6' Overflow" further down.

    Please Login or Register  to view this content.

  10. #10
    Registered User
    Join Date
    01-30-2015
    Location
    Durham, England
    MS-Off Ver
    Microsoft 2007
    Posts
    31

    Re: Variable and Data Type Confusion

    If I replace every CLng in my code with CDbl it comes with "Error Code '5' Invalid argument or procedure"

    Everything as CLng works fine/ no error code, but the outputted numbers look slightly off. and changing the odd thing to CDbl() changed the outputted numbers by quite a bit so I am worried that my numbers are not accurate unless I get the right CLng() CDbl() in the right places.

  11. #11
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Variable and Data Type Confusion

    What long/big numbers are you referring to?

    The largest number I can find in the workbook is 3262.

    PS Overflow can also occur if you try and divide by zero.

  12. #12
    Registered User
    Join Date
    01-30-2015
    Location
    Durham, England
    MS-Off Ver
    Microsoft 2007
    Posts
    31

    Re: Variable and Data Type Confusion

    In certain places in my code I am taking those numbers such as 3262 and multiplying them by each other. Or other things my code does is take those numbers to the power of negative decimal numbers

  13. #13
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Variable and Data Type Confusion

    Have you checked for division by zero?

    By the way, I can't test the code with the attached workbook as it fails straight away on these lines.
    Please Login or Register  to view this content.

  14. #14
    Registered User
    Join Date
    01-30-2015
    Location
    Durham, England
    MS-Off Ver
    Microsoft 2007
    Posts
    31

    Re: Variable and Data Type Confusion

    I have checked for division by zero and there isn't any. Also, my original work book is about 3 times to big for the forum allowance so it wont let me attach it. I cut bits out in the one I attached to this thread and maybe that has made a difference when you are testing it.

  15. #15
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Variable and Data Type Confusion

    The problem with the attached workbook is that it's an xls workbook, the code appears to be written for an xlsm workbook.

  16. #16
    Registered User
    Join Date
    01-30-2015
    Location
    Durham, England
    MS-Off Ver
    Microsoft 2007
    Posts
    31

    Re: Variable and Data Type Confusion

    Does this attachment work ? if I attach the coding the file is too big, is it possible to copy and paste my code from this thread into a new button on the file I attached?
    Attached Files Attached Files

  17. #17
    Registered User
    Join Date
    01-30-2015
    Location
    Durham, England
    MS-Off Ver
    Microsoft 2007
    Posts
    31

    Re: Variable and Data Type Confusion

    Hello, I was wondering if someone could take a look at this code for me and see if I am saving my variables as the wrong type for what they should be. Sometimes I convert a number to decimal with CDbl() and it brings up an error code even though the number is a decimal

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Need Data type to declare a Variable
    By Karthik Sen in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-24-2014, 07:47 PM
  2. Input box to needs different type of variable type (RC vs. A1?)
    By Niedermee in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 08-18-2014, 05:00 PM
  3. Replies: 0
    Last Post: 09-03-2013, 11:26 AM
  4. Replies: 1
    Last Post: 08-08-2012, 02:39 PM
  5. Variable type
    By arnab0711 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-27-2010, 03:57 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1