Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
|
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old May 5th, 2011, 10:09 AM
Friend of Wrox
 
Join Date: Jan 2011
Posts: 103
Thanks: 7
Thanked 0 Times in 0 Posts
Default

Hi

Thanks

so in the loop i should put:

Code:
 PPNewIR.cells(Nrow, 11).Value = fTryThis
I ran the macro and i got an error of "argument not optional"
 
Old May 5th, 2011, 10:13 AM
Friend of Wrox
 
Join Date: Sep 2010
Posts: 171
Thanks: 0
Thanked 14 Times in 14 Posts
Default

yes but you need to pass "fTryThis" the range, the single cell as a range, that has the formatting information you're figuring out. If I recall, you named your range "cell", so it would be something like
Code:
 PPNewIR.cells(Nrow, 11).Value = fTryThis(cell)
 
Old May 5th, 2011, 10:23 AM
Friend of Wrox
 
Join Date: Jan 2011
Posts: 103
Thanks: 7
Thanked 0 Times in 0 Posts
Default

Hi

Ok done that but now i get an error saying "sub or function not defined"

Do i need to declare the function with variables or should the fuction go before the sub routine?
 
Old May 5th, 2011, 10:29 AM
Friend of Wrox
 
Join Date: Sep 2010
Posts: 171
Thanks: 0
Thanked 14 Times in 14 Posts
Default

this part:
Code:
Function fTryThis(Target As Range) As String

    'reset iDigitsAfterDecimalPoint
    iDigitsAfterDecimalPoint = 0
    
    'get the formatting style for the cell
    stFormatStyle = Target.NumberFormat
    
    'if the format style is "general", return the default string and exit the function
    If stFormatStyle = "General" Then
        fTryThis = "%10.0f%%"
        Exit Function
    End If
    
    'if the format style is "comma", find the number of digits after decimal and return the string
    'this is what the NumberFormat string looks like for a "comma" format:
        '   _(* #,##0.00_);_(* (#,##0.00);_(* "-"??_);_(@_)
    If InStr(stFormatStyle, "#,##") Then
        'find the period
        iStart = InStr(stFormatStyle, ".")
        'find the ending 0
        iEnd = InStr(iStart, stFormatStyle, "_")
        'subtract the two to get the number of digits after the period
        iDigitsAfterDecimalPoint = iEnd - iStart - 1
        'return the string
        fTryThis = "%10." & iDigitsAfterDecimalPoint & "f%"
        'exit the function
        Exit Function
    End If
    
    'this looks at the last character of the NumberFormat string and if it's a percentage sign, then sets a Boolean to true (this will be used later to put an extra "%" sign on the return string if need be
    If Right(stFormatStyle, 1) = "%" Then
         bIsPercentage = True
    Else
         bIsPercentage = False
    End If
    'find the decimal point position:
    iStart = InStr(stFormatStyle, ".")
    
    If iStart = 0 Then 'there is no period
        iDigitsAfterDecimalPoint = 0
    Else 'there is a period
        iDigitsAfterDecimalPoint = Len(stFormatStyle) - iStart
    End If
    
    'if it's a percentage format with digits after period, subtract one to account for the "%" sign
    If bIsPercentage And iStart > 0 Then
        If bIsPercentage Then iDigitsAfterDecimalPoint = iDigitsAfterDecimalPoint - 1
    End If
    'Now you should have all the pieces you need so just put it together:
    stResult = "%10." & iDigitsAfterDecimalPoint & "f%"
    If bIsPercentage Then stResult = stResult & "%"
    
    'return the string
    fTryThis = stResult
End Function
needs to be put in all by itself. You're missing the top line of it I think.
And this part:
Code:
Option Explicit

'I declared these variables at the module level so your code doesn't have to allocate memory everytime the function is called
Dim stFormatStyle As String
Dim iStart As Integer
Dim iEnd As Integer
Dim iDigitsAfterDecimalPoint As Integer
Dim stResult As String
Dim iDecimals As Integer
Dim bIsPercentage As Boolean
needs to be put in at the tip-top of the module you're working in.
 
Old May 5th, 2011, 10:38 AM
Friend of Wrox
 
Join Date: Jan 2011
Posts: 103
Thanks: 7
Thanked 0 Times in 0 Posts
Default

Hi

Ok it the macro ran but when it got to the line below it gave an error of "Object required"

Code:
PPNewIR.cells(Nrow, 11).Value = fTryThis(cell.Value)
I have decalred the variables at the top after the Option Explict line and the function is underneith the sub, is this right?
 
Old May 5th, 2011, 10:40 AM
Friend of Wrox
 
Join Date: Sep 2010
Posts: 171
Thanks: 0
Thanked 14 Times in 14 Posts
Default

yes that is correct, but you're passing the value of the cell, not the range object:
Code:
'incorrect
PPNewIR.cells(Nrow, 11).Value = fTryThis(cell.Value)

'correct
PPNewIR.cells(Nrow, 11).Value = fTryThis(cell)
 
Old May 5th, 2011, 10:46 AM
Friend of Wrox
 
Join Date: Jan 2011
Posts: 103
Thanks: 7
Thanked 0 Times in 0 Posts
Default

Hi

Ok tried that and got an error of "ByRef argument type mismatch"
 
Old May 5th, 2011, 10:50 AM
Friend of Wrox
 
Join Date: Sep 2010
Posts: 171
Thanks: 0
Thanked 14 Times in 14 Posts
Default

is your "cell" variable pointing to a single cell?
 
Old May 5th, 2011, 10:50 AM
Friend of Wrox
 
Join Date: Sep 2010
Posts: 171
Thanks: 0
Thanked 14 Times in 14 Posts
Default

actually can you post all the code so i can look at it?
 
Old May 5th, 2011, 10:54 AM
Friend of Wrox
 
Join Date: Jan 2011
Posts: 103
Thanks: 7
Thanked 0 Times in 0 Posts
Default

Not sure as i defined it as a range and the loop goes through teh range provided and cell variable is used to defined each cell it comes to.

I hope that makes sense. The line below is where the cell variable (highlighte din red) is defined first. It is then used throughout the loop to define the activecell

Code:
For Each cell In PPWBForm.Range("F4:U644")





Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel Cell Format sumith ASP.NET 2.0 Professional 0 January 5th, 2010 05:34 AM
Checking an ICQ number SKYDOS BOOK: Beginning Regular Expressions 3 November 9th, 2008 03:55 PM
Checking cell value is integer ozPATT Excel VBA 2 October 5th, 2005 06:43 AM
Checking for upper,lower case, number and symbol dumbdumb SQL Server 2000 1 March 18th, 2004 03:51 AM
Checking for a number aware Classic ASP Basics 5 June 5th, 2003 09:57 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.