PDA

View Full Version : Function help



mshbhwn98
03-05-2015, 03:48 AM
Hi,

I'm pretty new to vba and I'm teaching myself about functions. I have written a sub that takes the contents of a column of cells and puts them into a comma delimited string like so

' creates a comma delimited string from a table of comma delimited strings
Private Sub CreateAnalysisString()
Dim AnalysisString As String
Dim i As Integer
' Initialise Variables
i = 1
AnalysisString = ""
With wksModeller
' Run through all analysisIDs until the bottom of the range
Do Until Not (Intersect(.Range("rngStartCur").Offset(i, 0), .Range("rngFinishCur")) Is Nothing)

' If the cell is blank do nothing
If .Range("rngStartCur").Offset(i, 0).Value = vbNullString Then
Else
If AnalysisString = "" Then
' The first part of the string does not need a ,
AnalysisString = .Range("rngStartCur").Offset(i, 0).Value
Else
' The string needs commas separating it after the initial part of the string
AnalysisString = AnalysisString & "," & .Range("rngStartCur").Offset(i, 0).Value
End If
End If

i = i + 1

wksModeller.Range("C1").Value = AnalysisString

Loop
End With
End Sub


I now want to try putting it into a function to gain a better understanding of how vba works. I did this with the following code


' creates a comma delimited string from a table of comma delimited strings
Private Function CreateAnalysisString(rngStart As Range, rngFinish As Range) As String
Dim AnalysisString As String
Dim i As Integer
' Initialise Variables
i = 1
AnalysisString = ""
With wksModeller
' Run through all analysisIDs until the bottom of the range
Do Until Not (Intersect(.Range(rngStart).Offset(i, 0), .Range(rngFinish)) Is Nothing)

' If the cell is blank do nothing
If .Range(rngStart).Offset(i, 0).Value = vbNullString Then
Else
If AnalysisString = "" Then
' The first par of the string does not need a ,
AnalysisString = .Range(rngStart).Offset(i, 0).Value
Else
' The string needs commas separating it after the initial part of the string
AnalysisString = AnalysisString & "," & .Range(rngStart).Offset(i, 0).Value
End If
End If

i = i + 1

Loop

End With
CreateAnalysisString = AnalysisString
End Function

Sub test()
Dim z As String

z = CreateAnalysisString(.Range("rngStartCur"), .Range("rngFinishCur"))
MsgBox z
End Sub


I am getting a Compile error: Ambiguous name detected:CreateAnalysisString - but I dont know what's wrong with it. Could someone guide me in teh right direction please?

Kind regards

Aflatoon
03-05-2015, 03:54 AM
It's just that you can't have a function and a subroutine with the same name if they are in the same module. I suggest you rename one of them.

mshbhwn98
03-05-2015, 04:04 AM
ha ha. Thank you so much. I'll remove the sub and see how that goes.

Thanks again