Excel functions in VBA (basic question)

[0d1]

Hi!

First of all, unfortunately, I have no experience with visual basic. My first question is rather stupid but for some reason I can't find any explanations or examples for that.

1: How do I use the functions already existing in excel using VBA? For example I would expect

Function bla()
Call IsNumber(3)
End Function

to give me the value "true". Instead it says "#NAME?".


2: I will elaborate on my general goal. Basically I have a set of text segments in column A (each cell contains a text segment). Now let's say I have text segments in row B. For every a_i in A I want to find the set of b's in B that are contained in a_i and put out a string in c_i (in coloum C) consisting of those b's. In pseudo code (for a specific segment in A):

Find Subsegments(Segment)
For i=1 to 10000
If b_i in Segment
Define X_i = C_i
End if
End for
Print "X_1, X_2, X_3, X_4"

Seems like an easy task but the obstacle in 1 is somewhat in the way. If someone has any tips to do this stuff it would be greatly appreciated.

Warm regards.

[Paul_Hossler]

Welcome to the VBA club. It's lots of fun ... after you get used to it

1. REALLY recommend you get a VBA book and read through it and do the examples. I think you have a few fundamental misunderstandings about the VBA language and using macros. A book will provide a much more structured learning environment than asking ad hoc questions.


2. There are VBA functions and worksheet functions. In VBA you were looking for the IsNumeric function which returns something, TRUE or FALSE, so you can act on it (e.g. diferent message boxes). Just 'Call'ing it really won't do much


3. Excel VBA has an 'object' model with things like Worksheets, Ranges, Cells, etc. These as have different properties and methods. Below 'ActiveSheet' is the Worksheet object that is active (usually the one that is selected).

InStr is a VBA function that returns a position of a string within another string, or 0 is it's not there

Option Explicit

Sub bla()
    If IsNumeric(3) Then
        MsgBox "It's a number"
    Else
        MsgBox "It's NOT a number"
    End If
    
    If IsNumeric("3") Then
        MsgBox "It's a number"
    Else
        MsgBox "It's NOT a number"
    End If
    
    
    If IsNumeric("Three") Then
        MsgBox "It's a number"
    Else
        MsgBox "It's NOT a number"
    End If
    
    
    If IsNumeric(1 + 2) Then
        MsgBox "It's a number"
    Else
        MsgBox "It's NOT a number"
    End If
    
    
End Sub

Sub Subsegments()
    Dim i As Long
    
    For i = 1 To 10000
        If InStr(ActiveSheet.Cells(i, 1).Value, ActiveSheet.Cells(i, 2).Value) > 0 Then
            ActiveSheet.Cells(i, 3).Value = ActiveSheet.Cells(i, 2).Value
        End If
    Next i
End Sub

[0d1]

Hi,
The reason I posted this ad hoc question (I know they aren't really popular) is that this is just a minor job and I probably won't use VBA in the future (then again... who knows!). I just tried to find a way to handle a lot of data in an efficient way.

Thank you, Paul, for investing your time to help me and make my life easier!

For everyone else reading this: A minor modification was necessary for the code to actually do what I want:

Sub Subsegments()
    Dim i As Long
     
     
    For j = 1 To 20
        Dim x As Integer
        x = 1
    For i = 1 To 10


        If InStr(ActiveSheet.Cells(j, 1).Value, ActiveSheet.Cells(i, 2).Value) > 0 Then
            ActiveSheet.Cells(j, 2 + x).Value = ActiveSheet.Cells(i, 2).Value
            x = x + 1
        End If
    Next i
    Next j
End Sub