What is CHR, txt,etc??

[romij29]

Can anyone please explain to a VB Newbie what a Chr is and when it is used??Also how do I know which numbers to use if selecting a specific colour.To make it easy here's a piece of code that's got me baffled!!

Cells(65, txtPeriod + 1).Formula = "=" & Chr(65 + txtPeriod) & 9 & "/" & Chr(65 + txtPeriod) & 4
Cells(66, txtPeriod + 1).Formula = "=" & Chr(65 + txtPeriod) & 10 & "/" & Chr(65 + txtPeriod) & 4
Cells(67, txtPeriod + 1).Formula = "=" & Chr(65 + txtPeriod) & 19 & "/" & Chr(65 + txtPeriod) & 9
Cells(68, txtPeriod + 1).Formula = "=" & Chr(65 + txtPeriod) & 20 & "/" & Chr(65 + txtPeriod) & 10

txtPeriod was placed as an argument ByRef(txtPeriod) where period is each month and has a column p1, p2....p12

This bit refers to formulas in specific cells..What does "txtPeriod + 1" and "Chr(65 + txtPeriod)" mean exactly??

Also I ve a colour index code supposedly to colour a certain area on a spreadsheet in excel. a piece of code for you:

Selection.Interior.ColorIndex = 34

How do I know which number fits which colour??

Thanks

 

[xlbo]

1st question:

Go to the VBE and follow:
F1
type "Chr"
press return
Read help file

2nd question:
Open a new worksheet
paste the following code into a new module
run it
The row number is the colorINDEX for that colour

Sub Show_Color_Indices()
For i = 1 to 56
 cells(i,1).interior.colorindex = i
next i
end sub

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[romij29]

Dont have VB help files installed on my system.
what is txt in relation to my code above?

Thanks.

 

[PHV]

Dont have VB help files installed
Complete your msoffice install.

 

[xlbo]

Well - if you are programming VBA, it would be particularly helpful to get them installed wouldn't it ??

To answer the question "txt" refers to nothing - it is most likely a prefix to a variable - most programmers use standard prefixes to variables to help recognise the type of variable eg

I would infer txtPeriod as either a string (txt = text) or a textbox (txt = textbox)

As further examples:

lblPeriod, I would infer as a label (lbl = label)

cbPeriod, I would infer as a combobox (cb = ComboBox)

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[romij29]

Thanks Geoff,

the color index thing worked like a charm..Thanks a mill.!!

 

[Tigerlili3]

From VBA Help file:
"Chr Function -- Returns a String containing the character associated with the specified character code.

Syntax

Chr(charcode)

The required charcode argument is a Long that identifies a character.

Tip from me: The numeric codes that represent the characters can be found in an ascii table at

http://www.asciitable.com/.

You would use the number that appears in the Dec column to insert that character that appears in the Chr column. For example, Chr(13) is a carriage return (like pressing enter). Chr(32) is a space. Chr(65) as in your example, happens to be the letter A.

I hope this helps!

~Debra

 

[mrmovie]

the txtPeriod really looks like it should be declared as an integer.
it looks like it is used within Cells() which likes to have integers and Chr() which also likes integers

 

[xlbo]

Personally, I would assume that txtPeriod is a textbox where someone can enter the Period they want to see data for. The result is then used to create a simple X / Y formula with the Chr() function returning an incrementing letter of the alphabet from A onwards...

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[Tigerlili3]

So far from what I can tell, the first line of the formula is creating a relative reference to cell A9 and dividing that by the relative reference to cell A4. The txtPeriod is what makes it relative. Whatever that is equal to will affect the calculation the formula is making. It depends what txtPeriod gets set to. Once you determine that, you be golden.

 

[mrmovie]

i guess this might come down to a hungarian naming convention discussion!! and if the coder wanted to be more explicit using

(ByRef txtPeriod As Integer)
or
(ByRef intPeriod As Integer)
or
(ByRef Period As Integer)

or stick with the (ByRef txtPeriod) but do some handling of what has been passed

if "hello world" is passed then i would suggest

Chr(65 + txtPeriod) would throw an error

if you dont want to rely on vb implicity converting txtPeriod something like

If IsNumeric(txtPeriod) Then
'might be required.
End If

 

[romij29]

Thanks a lot,Debra!!You gave me a precise explanation and you'v made my day a lil more wothwhile.Thanks for everything and have a happy new year!!!

Kojo

 

[romij29]

The txtPeriod, I think refers to successive columns relating to periods(P1,P2,....P12).
First column gives a description of profit and loss items then the successive cols ,the numerical data for each month.
The whole code is as follows:

Public Sub By_Region(ByRef txtPeriod)


i = 2
intRow = 2
Application.ScreenUpdating = False
strPath = Workbooks("Control.xls").Sheets("Control").Range("M2")
strPath2 = Workbooks("Control.xls").Sheets("Control").Range("M3")

'Loops through cells in column A until cell = blank
Do Until Workbooks("Control.xls").Sheets("Control").Range("A" & i) = ""
'assigns a variable to a named range ,this variable
'will refer to the range whenever used in the following code
strWkb = Workbooks("Control.xls").Sheets("Control").Range("J" & intRow)
strFcast = Workbooks("Control.xls").Sheets("Control").Range("D" & i)
o Until Workbooks("Control.xls").Sheets("Control").Range("A" & i) = ""
'assigns a variable to a named range ,this variable
'will refer to the range whenever used in the following code
strWkb = Workbooks("Control.xls").Sheets("Control").Range("J" & intRow)
strFcast = Workbooks("Control.xls").Sheets("Control").Range("D" & i)

'If cell in column C matches the variable assigned above, the
'macro will open a specified workbook and continue to next loop
If Workbooks("Control.xls").Sheets("Control").Range("C" & i) = strWkb Then
Application.StatusBar = "Processing " & strWkb
Workbooks.Open Filename:=strPath & strWkb
Workbooks.Open Filename:=strPath2 & strFcast
Application.DisplayAlerts = False

'The macro will try to find a matching cost centre from
'the trading accounts workbook and copy the period range
'and paste it to the assigned column in forecast model

Do While Workbooks("Control.xls").Sheets("Control").Range("C" & i) = strWkb
strCinema = Workbooks("Control.xls").Sheets("Control").Range("A" & i)
strCinema2 = Workbooks("Control.xls").Sheets("Control").Range("B" & i)
Workbooks(strFcast).Worksheets(strCinema2).Unprotect password:="protect"
Workbooks(strWkb).Sheets("P&L").Activate
Columns("A:A").Select
Selection.Find(What:=strCinema, After:=ActiveCell, LookIn:=xlValues, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False).Activate
ActiveCell.Offset(0, 0).Range("C3:C61").Copy

'The following code assigns the selected column to
'equal the period entered in the form + 1
'In the forecast models the first column has the
'account names and therefore the first data column
'will be 2
Workbooks(strFcast).Worksheets(strCinema2).Activate
Cells(4, txtPeriod + 1).Activate
ActiveCell.PasteSpecial (xlPasteValues)
Range(Chr(65 + txtPeriod) & "1:" & Chr(65 + txtPeriod) & "80").Select
'sets a color format for the selection
Selection.Interior.ColorIndex = 34
Selection.Locked = True
Cells(65, txtPeriod + 1).Formula = "=" & Chr(65 + txtPeriod) & 9 & "/" & Chr(65 + txtPeriod) & 4
Cells(66, txtPeriod + 1).Formula = "=" & Chr(65 + txtPeriod) & 10 & "/" & Chr(65 + txtPeriod) & 4
Cells(67, txtPeriod + 1).Formula = "=" & Chr(65 + txtPeriod) & 19 & "/" & Chr(65 + txtPeriod) & 9
Cells(68, txtPeriod + 1).Formula = "=" & Chr(65 + txtPeriod) & 20 & "/" & Chr(65 + txtPeriod) & 10

i = i + 1
Loop
intRow = intRow + 1
Workbooks(strWkb).Close
Workbooks(strFcast).Worksheets("Summary by Period").Activate
Range(Chr(65 + txtPeriod) & "1:" & Chr(65 + txtPeriod) & "67").Select
ActiveSheet.Unprotect password:="protect"
Selection.Interior.ColorIndex = 34
ActiveSheet.Protect password:="protect"

Else
i = i + 1
End If
Loop
If strWkb = "" Then
Application.CutCopyMode = False
Application.StatusBar = False
Workbooks("Control.xls").Sheets("Control").Activate
Exit Sub
End If

Application.StatusBar = False
Application.CutCopyMode = False
End Sub

As you can see,

txtPeriod has not been set as a variable or integer, but as an argument by reference(ByRef).Is this sound??
Thanks

 

[mrmovie]

only if what ever is calling

By_Region(ByRef txtPeriod)

has already checked that txtPeriod is in fact an integer, or in your case can implicitly be converted to one

 

[mrmovie]

as a final note on this sub declaration i would go so far to say the defining it as

Sub By_Region(ByRef txtPeriod)

is unsound for a few general reasons

1. VBA is type specific(right word?) i.e. you can delare your variables and specify the type. therefore one should do so.

2.declaring the Type makes things clearer for the reader to understand what is going on.

3.declaring the type shows up errors easier and quicker in your code when you get things wrong

4.declaring the type reduces memory usage (?)

5.declaring types in the sub/function statement forces the calling procedure to 'send' the correct parameters

Specifically in your case.

Your By_Region sub does no 'checking' on what the Type of txtRegion is. It could be an Array, a "hello world" string, a Worksheet object, it could be anything. It is then blindly used as if it is an Integer. This could cause unexpected results. Perhaps when VBA tries to convert a "hello world" string to an integer it always returns -1, not what you wnat i guess.
If it is the case that the procedure which has passed something as txtPeriod has already checked the Type and the value of txtPeriod. I would presume so. If that is the case then there is no harm in saying 'txtPeriod As Integer'

an example

'this would produce a runtime error
iR = "hello"
iC = "world"
ActiveSheet.Cell(iR, iC).Select

'this should also produce a runtime error in my opinion
iR = "hello world"
module1.By_Region(iR)

ByRef\ByVal:
Seeing as your By_Region sub doesnt modifiy the value of txtPeriod then i would suggest passing ByVal might be better? saying that there are performance gains and losses to be had between passing ByRef and ByVal. Seeing as we are dealing with a small amount of data the performance differences will be minimal so i would say for readability and perhaps i would go for ByVal

txtPeriod, intPeriod or Period.
seeing as txtPeriod is the label used internally in By_region and within By_Region one way or another you are expecting a number i would suggest intPeriod would be better. some people are starting to move away from hungarian as becomes a night mare when you Cast(right word?) variable to another Type, should you then create a new variable with the different first 3 letters?? anyway

i would go for

(ByVal intPeriod As Integer)

 

[romij29]

From what I gather txtPeriod relates to the first column in the excel spreadsheet where they are Profit and Loss item descriptions(such as Turnoer ,Profit etc.). The successive cols are numerical figures relating to a specific month(P1.P2...P12).
I am assuming that "txtPeriod + 1" relates to thenext column(P1)which is the col after the first,right?So how does that ensure that data from the source worksheets are pasted into successive columns(3,4,5..etc),meaning P3,P4,...P12 one after the other everytime maco is run.???

BACKGROUND INFO:Macro uses an excel worksheet called "Control"(as macro suggests from first couple of lines) to direct it to open workbooks and in what order.
In the worksheet is a cell with a file paths leading to a specific month workbook(such as P12,maybe).This month is changed in the workbook control before the macro is run each month.
Therefore if say i change the file path to go to maybe P11, macro is supposed to pick up a column from a worksheet and dump into destination excel worksheet in a column labelled P11.
Therefore does macro interpret that "txtPeriod + 1" caters for the successive cols after P1???

thanks.

 

[romij29]

Forecast 2004

p1 p2 p3 p4 p5 p6 p7
Turnover 47 46 28 58 34 52 31

Admissions 24 32 32 31 31 21 58

Profit 548 542 321 218 954 54 784

Example of my destination worksheet.

txtPeriod refers to the first col with Turnover,
I am assuming that txtPeriod + 1 refers to the p1 column.Does macro interpret txtPeriod+1 to be p2,p3,p4,etc as well everytime macro is run monthly??

Thanks

 

[mrmovie]

'uses of txtPeriod in By_Region
'#####################################
Cells(65, txtPeriod + 1).Formula

Cells(integerRow, integerCol).Formula
Cells().Formula is expecting you to provide 2 integers to tell it which cell you are interested in.
i.e. Cells(1,1) would be 1st row, 1st col

txtPeriod + 1
you are telling vba you wish to ADD the numeric value of txtPeriod to 1
so if txtPeriod = 2 the result would be 3
so you would get

Cells(65, 3).Formula = "blaa"


'#####################################
Chr(65 + txtPeriod)

Chr() is expecting an number between the ()
So, things like Chr(34), Chr(32), Chr(10)

65 + txtPeriod means you want to ADD(+) the numeric value of txtPeriod to 65. so if txtPeriod = 2 then 65+2=67.
Chr(65) happens to be A so Chr(67) will be C

the C or D or E etc is then used in a Range declaration

Range(Chr(65 + txtPeriod) & "1:" & Chr(65 + txtPeriod) & "80").Select

so this would give use
Range(C1:C80).Select

'#################################################
if you are in any doubt about what txtPeriod really is then put

Msgbox CStr(txtPeriod)

at the top of the By_Region sub.

i hope this helps.
ps, if it all works at moment i wouldnt worry about it too much

 

[romij29]

Thanks for all your help, Mr. Movie

 

[romij29]

Can seem to debug (step-in) my program above. Don't know what the problem is. Any help??
thanks