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 4th, 2011, 10:22 AM
Friend of Wrox
 
Join Date: Sep 2010
Posts: 171
Thanks: 0
Thanked 14 Times in 14 Posts
Default

hey jeskit.
this code:
Code:
Right(stFormatStyle, 1)
The right function looks at a string (in this case, stFormatStyle is my variable and it's holding the formatting code for the cell, like "0.00%") and takes however many characters starting from the right-hand side of the string. You can click on help and then type in Right and it should pop up what the right() function information.

the line
Code:
iPeriodPosition = InStr(stFormatStyle, ".")
should equal 0 if there is no period in the formatting style. Give me a while, (today's my birthday) and i'll see if i can put together a finished piece of code for you :)
 
Old May 4th, 2011, 10:28 AM
Friend of Wrox
 
Join Date: Jan 2011
Posts: 103
Thanks: 7
Thanked 0 Times in 0 Posts
Default

Hi

Thanks! Happy birthday by the way!
 
Old May 4th, 2011, 10:49 AM
Friend of Wrox
 
Join Date: Sep 2010
Posts: 171
Thanks: 0
Thanked 14 Times in 14 Posts
Default

instead of giving me this:

If its a percentage then it should return to column I "%10.(whatever decimal it is, should include 0"f%%"

Can you give me all the specific strings that can be returned? I.e.:

If its a percentage with 2 digits after the decimal point, then it should return to column I "%10.2f%%"

I'm still not sure exactly what you're looking for as far as what this function is going to return for results
 
Old May 4th, 2011, 10:53 AM
Friend of Wrox
 
Join Date: Sep 2010
Posts: 171
Thanks: 0
Thanked 14 Times in 14 Posts
Default

also, what are all the possible format styles the cell may have?
 
Old May 4th, 2011, 10:56 AM
Friend of Wrox
 
Join Date: Jan 2011
Posts: 103
Thanks: 7
Thanked 0 Times in 0 Posts
Default

Hi

Ok.

The strings i would like it to return are:

if its a percentage and depending on the number of decimal places for example "%10.2f%%"
if its a number and depending on the number of decimal places For example "%10.2f%%"
and for anything else "%10.0f%%"

The tsring which i need it to return it basically same for all instances except for when its a percentage. When its a percentage it should return 2 % signs at the end instead of 1

Does this help?
 
Old May 4th, 2011, 11:04 AM
Friend of Wrox
 
Join Date: Sep 2010
Posts: 171
Thanks: 0
Thanked 14 Times in 14 Posts
Default

thanks btw on the happy birthday. no one was here when i went out to kitchen, so have some time, lol.

ok, so basically if it's formatted as a percent, 2 "%" signs on the end, all else the same.

As far as the number of digits after the decimal, would a number with 4 look like this?

"%10.4f%%"

And for your "anything else" part, NO MATTER WHAT, it should look EXACTLY like this:

"%10.0f%%"
 
Old May 4th, 2011, 11:56 AM
Friend of Wrox
 
Join Date: Jan 2011
Posts: 103
Thanks: 7
Thanked 0 Times in 0 Posts
Default

Yep that is correct!
 
Old May 4th, 2011, 12:01 PM
Friend of Wrox
 
Join Date: Sep 2010
Posts: 171
Thanks: 0
Thanked 14 Times in 14 Posts
Default

okay, here are the rows and sample format styles i used:

Row Number Formatting style FormatStyle string returned
1 45 General General
2 45.00 comma _(* #,##0.00_);_(* (#,##0.00);_(* "-"??_);_(@_)
3 45.00 number 0.00
4 45.000 number 0.000
5 45% percent 0%
6 45.00% percent 0.00%
7 45.000% percent 0.000%

Here's the Function:
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

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
and here are the results:

Row Function result
1 %10.0f%%
2 %10.2f%
3 %10.2f%
4 %10.3f%
5 %10.0f%%
6 %10.2f%%
7 %10.3f%%

Does that do it?

Last edited by mtranchi; May 4th, 2011 at 12:05 PM..
 
Old May 5th, 2011, 04:12 AM
Friend of Wrox
 
Join Date: Jan 2011
Posts: 103
Thanks: 7
Thanked 0 Times in 0 Posts
Default

Hi

Yep that'll work.

However i am slightly confused. Does this all go in the module with the loop or does it go in a seperate function?
 
Old May 5th, 2011, 09:41 AM
Friend of Wrox
 
Join Date: Sep 2010
Posts: 171
Thanks: 0
Thanked 14 Times in 14 Posts
Default

i set it up as a separate function that you pass a single cell as a range and that returns a string. You'll notice that the variables i use are declared at the module level (not in the function itself). I did this so your computer doesnt' have to keep allocating and deallocating memory for every cell you use it on.

So basically should be able to copy the variables into the very top of your module (right under "Option Explicit"), and the function somewhere amongst your subs and functions. Then in the loop that you need the info, just call it. :)





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.