hi, thanks for you help! my background is in compiled languages (C, C++ etc) and I am new to powershell. i need to write a recursive function but am having issues with the syntax. this is my code:
function myfunction {
param(
[Parameter(Mandatory = $true)]
[int]
$ColumnNumber
)
if ($ColumnNumber -gt 26){
return [char]([int][Math]::Floor($ColumnNumber,26))+myfunction($ColumnNumber-26)
}
return [char]$ColumnNumber
}
when i try to run this, i get the following:
At C:\Users\tonyg\Google Drive\Work\PowerShell\Untitled3.ps1:11 char:39
+ $ColumnString = $ColumnString+myfunction($ColumnN ...
+ ~
You must provide a value expression following the '+' operator.
At C:\Users\tonyg\Google Drive\Work\PowerShell\Untitled3.ps1:11 char:39
+ $ColumnString = $ColumnString+myfunction($ColumnN ...
+ ~~~~~~~~~~~~~~~~~~~~~~
Unexpected token 'myfunction' in expression or statement.
+ CategoryInfo : ParserError: (:) [], ParentContainsErrorRecordException
+ FullyQualifiedErrorId : ExpectedValueExpression
so, i see it does not like me calling my function on the right side of the ā=ā. i have looked around and the examples of recursion i see on the internet are not calling the function this way.
is there a way to do what i am trying to do or is there a āpowershellā way to do this?
What should be the actual purpose of your function? Did you notice that your error message does actually not fit to the code you posted? At least the line in the error message does not exist in your function.
In PowerShell we call functions like this:
myfunction -ColumnNumber 234
ā¦ no parenthesis needed.
Another issue might occur when your function returns a [char] but it expects an [int] as input. I wouldnāt expect this to run in a recursive function.
Next ā¦ if Iām not wrong [MATH]::(Floor) only takes one argument. Iād expect you to get more than just the error you posted.
sorry for the typo, this is what i meant [char]([int][Math]::Floor($ColumnNumber/26)) ā¦on this topic, this is the only way i could find to get the ādivā of two numberā¦i find it surprising that powershell has a mod (modulo) function 5%2=1 but no divā¦ie 5div2=2 ? am i missing something?
but on your main point, the error was a different issue. with the code below, i get no errors, however the function returns nothingā¦i was expecting a string of [char] to be built up in the iterations.
the purpose of this code is to translate worksheet column numbers into column lettersā¦like column 27 is column AA etc.
PowerShell is made for administrators - not for programmers. Why make it harderr than necessary to divide 2 numbers? Simply write it as you would on paper. 5/2 and the result will be 2.5. Neat, huh?
Hmmm ā¦ I still did not get it. You may post some sample input and the desired output.
With the cast operator [char] you convert a number to its charachter equivalent. But that actually starts to make sense with numbers like 33 and above. Below that you only get non printable charachters backspace, line feed, horizontal tab, form feed, escape and so on.
And there still the issue I already mentioned in my first answer. If you want to recursively call you function the return value should at least be of the same type as the expected input vaule. ā¦ Iād expect that schould be the same in C or C++.
Olaf, i donāt think you understand the difference between div and divide. 5/2=2.5 5div2=2ā¦i need div, not divide.
on your main point, i think i realized the same thing, i needed to add 65 to the [char] to get A.
my code is working better now, however, it is not completeā¦for numbers > 52, it is broken, working on that now.
updated code:
function myfunction {
param(
[Parameter(Mandatory = $true)]
[int]
$ColumnNumber
)
if ($ColumnNumber -gt 26){
return [char](([int][Math]::Floor(($ColumnNumber-1)/26)+64))+[char](myfunction($ColumnNumber-26))
}
return [char](($ColumnNumber)+64)
}
Now I got it. Thanks for the clarification. Anyway you already found a way ā¦ [int][Math]::Floor(5 / 2) ā¦ of course PowerShell does not provide that much builtin functions like general purpose programming languages. You may find more info here:
I start to have an idea what youāre trying to do. I just donāt get what it is with the number 26 in your code. Could you please provide some sample input and the according desired output?
ahhh, -join are you doing that to return a string instead of a charā¦perfect. i was just sitting down to figure that outā¦i was going to try and cast it as [string] after casting it as [char]
oh but this isnāt rightā¦yes, i have not been very descriptiveā¦in a spreadsheet, the columns are lettered A ā blah but a lot of excel powershell functions return column number (not the letter) so i have the need to convert the column number to it letterā¦so column 26 is Z, column 27 would be AA column 52 would be AZ etc. column 676 would be ZZā¦so i think there is a flaw in my recursionā¦that is what i will look at next.
hi, i am so glad you brought this up. i was aware of this module, however, i can find no real documentation with it (aside from the one video the author provides). all the examples of this are how to create spreadsheets from data, but none highlight the other functionalityā¦like Get-ExcelColumnName.
do you know where this other functionality is documented?
btw, i looked at the source for Get-ExcelColumnName and now, i want to complete this using recursion as a a matter of prideā¦(not a good reason butā¦)
Documentation and even the comment-based help is lacking. Iām sure the project would welcome contributers to improve the situation.
Once I saw what you were trying to do, I figured the module might already have the functionality so I just did Get-Command -Module ImportExcel and looked for something relevant.
