在 VBA 中返回布尔值

Question

提问by William

I have a piece of code that is supposed to check for the presence of a sheet by name. If the sheet exists, there are some copy and past functions, if the sheet does no exist, then it will create of and the same copy past functions will follow. I cannot get the correct boolean value to return to the main sub. The boolean value is always registering as false (which I know is the default value). I have tried several different methods to correct the issue and I am still having problems. I could really use some help, it is probably an easy fix.

我有一段代码应该按名称检查工作表的存在。如果工作表存在，则有一些复制和过去的功能，如果工作表不存在，那么它会创建并遵循相同的复制过去功能。我无法获得正确的布尔值以返回主子。布尔值始终注册为 false（我知道这是默认值）。我尝试了几种不同的方法来纠正这个问题，但我仍然遇到问题。我真的可以使用一些帮助，这可能很容易解决。

Sub BreakOutCategories()

    Dim catSheet As Worksheet
    Dim catName As String
    Dim Range1 As Range
    Dim gRange As Range
    Dim toSheet As Worksheet
    Dim CheckSheet As Boolean
    Dim CreateSheet As Boolean
    Dim i As Long

        Set catSheet = Sheets("MasterList")
        Set Range1 = catSheet.Range("A1", catSheet.Range("A1").End(xlDown))

            For Each gRange In Range1

            i = 0
            catName = gRange.Value

            CheckMySheet (catName)

                If CheckSheet = True Then

                    toSheet = Sheets(gRange.Value)

                    gRange.Offset(0, 1).Copy
                    toSheet.Range("A1", toSheet.Range("A1").End(xlDown)).Offset(1, 0).Paste
                    gRange.Offset(0, 1).Copy
                    toSheet.Range("E1", toSheet.Range("E1").End(xlDown)).Offset(1, 0).Paste

                    gRange.Offset(0, 2).Copy
                    toSheet.Range("B1", toSheet.Range("B1").End(xlDown)).Offset(1, 0).Paste
                    gRange.Offset(0, 2).Copy
                    toSheet.Range("F1", toSheet.Range("F1").End(xlDown)).Offset(1, 0).Paste

                ElseIf CheckSheet = False Then

                    CreateMySheet catName

                    toSheet = (gRange.Value)

                    gRange.Offset(0, 1).Copy
                    toSheet.Range("A1", toSheet.Range("A1").End(xlDown)).Offset(1, 0).Paste
                    gRange.Offset(0, 1).Copy
                    toSheet.Range("E1", toSheet.Range("E1").End(xlDown)).Offset(1, 0).Paste

                    gRange.Offset(0, 2).Copy
                    toSheet.Range("B1", toSheet.Range("B1").End(xlDown)).Offset(1, 0).Paste
                    gRange.Offset(0, 2).Copy
                    toSheet.Range("F1", toSheet.Range("F1").End(xlDown)).Offset(1, 0).Paste

                End If

            Next gRange

End Sub
Public Function CheckMySheet(ByVal catName As String) As Boolean

    Dim theSheet As Worksheet
    Dim CheckSheet As Boolean

        For Each theSheet In ThisWorkbook.Sheets

            If theSheet.Name = catName Then

                CheckSheet = True
                Exit For

            End If

        Next theSheet

End Function
Public Function CreateMySheet(ByVal catName As String) As Boolean

    Dim catSheet As Worksheet
    Dim newSheet As Worksheet
    Dim Range1 As Range
    Dim gRange As Range

        Set catSheet = Sheets("MasterList")
        Set Range1 = catSheet.Range("A1", catSheet.Range("A1").End(xlDown))

        Set newSheet = Sheets.Add(After:=Sheets("Cover"))
        newSheet.Name = catName

        newSheet.Range("A1") = "Line"
        newSheet.Range("E1") = "Line"
        newSheet.Range("B1") = "Item"
        newSheet.Range("F1") = "Item"
        newSheet.Range("C1") = "Units"
        newSheet.Range("G1") = "Sales"

        CreateMySheet = True

End Function

I've posted the code in it's entirety to try and give a good picture of what I am trying to accomplish. I was also considering a Do Untilloop after CreateMySheet catNameto check for the presence of the sheet again to prevent the code from moving forward until the sheet has been fully created.

我已经完整地发布了代码，以尝试对我正在尝试完成的工作给出一个很好的描述。我还考虑了一个Do Until循环之后CreateMySheet catName再次检查工作表的存在，以防止代码向前移动，直到工作表完全创建。

Thanks!

谢谢！

Answer 1

回答by

I think that you will still need to improve your copying and pasting, but to get you started with the sheet creation here is some of your code modified to do create a new sheet if it doesnt exist in the list

我认为您仍然需要改进您的复制和粘贴，但是为了让您开始创建工作表，这里修改了一些代码以在列表中不存在时创建一个新工作表

Option Explicit

Sub BreakOutCategories()

    Dim catSheet As Worksheet
    Dim catName As String
    Dim Range1 As Range
    Dim gRange As Range
    Dim toSheet As Worksheet
    Dim CheckSheet As Boolean
    Dim CreateSheet As Boolean
    Dim i As Long

    Set catSheet = Sheets("MasterList")
    Set Range1 = catSheet.Range("A1", catSheet.Range("A1").End(xlDown))

    For Each gRange In Range1
        i = 0
        catName = gRange.Value
        If CheckMySheet(catName) Then
            Set toSheet = Sheets(gRange.Value)
            ' sheet exists do your copying
        Else
            CreateMySheet catName
            Set toSheet = Sheets(gRange.Value)
            ' sheets didnt exist
        End If
    Next gRange
End Sub

Private Function CheckMySheet(ByVal catName As String) As Boolean
    Dim theSheet As Worksheet
    For Each theSheet In ThisWorkbook.Sheets
        If StrComp(theSheet.Name, catName, vbTextCompare) = 0 Then
            CheckMySheet = True
            Exit For
        End If
    Next theSheet
End Function

Private Function CreateMySheet(ByVal catName As String) As Boolean
    Dim catSheet As Worksheet
    Dim newSheet As Worksheet
    Dim Range1 As Range
    Dim gRange As Range

    Set catSheet = Sheets("MasterList")
    Set Range1 = catSheet.Range("A1", catSheet.Range("A1").End(xlDown))

    Set newSheet = Sheets.Add(After:=Sheets("Cover"))
    newSheet.Name = catName

    newSheet.Range("A1") = "Line"
    newSheet.Range("E1") = "Line"
    newSheet.Range("B1") = "Item"
    newSheet.Range("F1") = "Item"
    newSheet.Range("C1") = "Units"
    newSheet.Range("G1") = "Sales"

    CreateMySheet = True
End Function

UPDATE:
Further to your comments I think you need to read a bit more about functions and how they work. It's relatively easy and definitely basic stuff you need to learn if you planning on coding/programming. I can recommend Pearsons Guideas a starting point.
Now, let me show you the easiest of easy examples
Make sure you know what the difference between a functionand procedureis before you move on.

更新：
除了您的评论之外，我认为您需要阅读更多有关函数及其工作原理的信息。如果您计划编码/编程，它相对容易并且绝对是您需要学习的基本知识。我可以推荐Pearsons Guide作为起点。
现在，让我向您展示最简单的示例
在继续之前确保您知道afunction和procedureis之间的区别。

Function ReturnTrue() As Boolean
    ReturnTrue = True
End Function

Function ReturnFalse() As Boolean
    ReturnFalse = False
End Function

The above demonstrates how to return a Boolean value from a function with no criteria. If you call that from a module one will always return true and other always false.

上面演示了如何从没有条件的函数中返回布尔值。如果你从一个模块调用它，一个总是返回真，另一个总是假。

the below demonstrates how to return a Value from a function based on some criteria. This time you want to RUNthe BooleanFunctions()to better understand the code and the results. I hope this helps

下面演示了如何根据某些条件从函数返回值。你想这一次RUN的BooleanFunctions()，以便更好地理解代码和结果。我希望这有帮助

Function TrueOrFalse(number As Integer) As Boolean
    If number > 0 And number < 255 Then
        TrueOrFalse = True
    Else
        TrueOrFalse = False
    End If
End Function

Sub BooleanFunctions()
    Dim functionResult As Boolean
    functionResult = TrueOrFalse(10)
    MsgBox functionResult
    functionResult = TrueOrFalse(-10)
    MsgBox functionResult
End Sub

As you can see there value that is going to be returned by function is the functions name and whatever is assigned to it as the last call in a function

如您所见，函数将返回的值是函数名称以及作为函数中最后一次调用分配给它的任何值

Answer 2

回答by Doug Glancy

You never set the value of the function so, as you note, it returns the default value of False. Fix it by adding a line at the end:

您从未设置函数的值，因此，正如您所注意到的，它返回的默认值False。通过在末尾添加一行来修复它：

Public Function CheckMySheet(ByVal catName As String) As Boolean

    Dim theSheet As Worksheet
    Dim CheckSheet As Boolean

        For Each theSheet In ThisWorkbook.Sheets
            If theSheet.Name = catName Then
                CheckSheet = True
                Exit For
            End If
        Next theSheet

CheckMySheet = CheckSheet 

End Function

在 VBA 中返回布尔值

提问by William

回答by

回答by Doug Glancy

相关推荐

最近更新

标签

在 VBA 中返回布尔值

提问by William

回答by

回答by Doug Glancy

相关推荐

在 Excel vba 中读取单元格值并写入另一个单元格

Excel VBA 打开文件夹

“报告视图”中的 VBA MS Access 打开报告

vba 运行时错误 1004“无法获取数据透视表类的数据透视字段属性”

相关推荐

最近更新

标签