CODEWITHSUNDEEP
excelvba
Miraç Mert Pelister
Miraç Mert Pelister

Reputation: 13

User defined function returns value error which should be string

I'm trying to create a file where there is a calendar in each sheet and cells represent the time interval on that day. I have multiple worksheets identical to each other, however the first worksheet is the one to display info. Other worksheets are for each group member to mark if they are not available (NA) or remotely available (R) for that time.

I want my function to check every worksheet for the same cell coordinate (because every sheet is identical and cells represent the time on that day), if they have any R or NA written in them. If not, it should output AA for all available on the original cell where the function is written.

If there are some people remotely available, then it should output AA(!) for all available but some remotely, and show who are the ones that are remotely available in the comment of that cell.

If there are any NAs then the output should also be NA and show who are the ones that are not available as a comment on the original cell.

I keep getting #VALUE! error and I have no idea why.

Here is my code:

Function Checkdate(a) 'a is the selected cell, which is the same cell as the function is written
    Dim ac
    ac = a.Column
    Dim ar
    ar = a.Row
    Dim names
    names = Array("person1", "person 2", "person 3")
    Dim avlb As String 'avlb is the availabiltiy situation and later will be the result of the function
    avlb = "AA"
    Dim rnames As String 'the string variable where names will be stored who are remotely available
    rnames = ""
    Dim nanames As String 'the string variable where names will be stored who are not available
    nanames = ""
    For Each Name In names
        If Worksheets(Name).Cells(ar, ac).Value = "R" Then
    avlb = "AA(!)"
            rnames = rnames & " " & Name
        End If
        If Worksheets(Name).Cells(ar, ac).Value = "NA" Then
            avlb = "NA"
        nanames = nanames & " " & Name
        End If
    Next Name
    Checkdate(a) = avlb
    If avlb = "AA(!)" Then
        a.AddComment = "Remotely available:" & rnames
    ElseIf avlb = "NA" Then
        a.AddComment = "Not available:" & nanames
    Else
        a.Comment.Delete
    End If
End Function

Upvotes: 1

Views: 102

Answers (1)

Siddharth Rout
Siddharth Rout

Reputation: 149325

Indeed it gives a #VALUE! error as well, however it also adds a blank comment. EDIT: the main code works if i remove the comments! but i also need to add a comment to show who are the ones not available, how can i do so? – Miraç Mert Pelister 8 mins ago

The problem is with this

If avlb = "AA(!)" Then
    a.AddComment = "Remotely available:" & rnames
ElseIf avlb = "NA" Then
    a.AddComment = "Not available:" & nanames
Else
    a.Comment.Delete
End If

Try this: (Thanks to @T.M for drawing my attention towards it.)

The correct way to add a comment is

With Rng
    .AddComment
    .Comment.Text Text:="Your text"
End With

Incorporating it in your code.

If avlb = "AA(!)" Then
    With a
        '<~~ Check if cell already has a comment
        If Not .Comment Is Nothing Then .Comment.Delete 
        .AddComment
        .Comment.Text Text:="Remotely available:" & "sid"
    End With
ElseIf avlb = "NA" Then
    With a
        If Not .Comment Is Nothing Then .Comment.Delete
        .AddComment
        .Comment.Text Text:="Not available:" & "sid"
    End With
Else
    If Not a.Comment Is Nothing Then a.Comment.Delete
End If

Upvotes: 1

Related Questions