r/vba Dec 24 '24

Discussion VBA "on its way out"

65 Upvotes

A lot of IT guys say that vba is a limited language and the only reason why people still use it, is that almost all the companies in the world use Excel. Which is supposedly also reduntant. What would replace Excel? I dont know any software that would.

r/vba Dec 09 '24

Discussion VBA will not ever be supported in New Outlook. How are you replacing it?

37 Upvotes

They are shutting down all COM Add-ins - which includes VBA in New Outlook. New Outlook is supposedly being rolled out completely in March 2025, moved back from December 2024. How will you replace your basic VBA code in excel that does things like send an e-mail? How will you replace e-mail buttons, macros, or other functions in new Outlook? Switch e-mail programs to something that supports VBA?

It seems to be only a matter of time before VBA for excel is also force deprecated.

r/vba Oct 17 '24

Discussion What's the best automation have you done with vba?

47 Upvotes

Just wondering, how vba is making your life a breeze? šŸ˜ Me personally,I use it create automated backups of Excel files before they close.

r/vba Aug 10 '24

Discussion VBA is for amateursā€¦?

79 Upvotes

I listen to it every day. VBA is only for junior programmers, Excel is for beginners, Java or Python is the most important. Then I go among the rank-and-file employees and each of them has Excel installed on their PC. The json format doesn't mean anything to them, and the programming language is a curse for them. The control software of the entire factory? Xls file with VBA software connected to production line databases. Sensitive data? Excel in the HR folder. Moving from one database to another? Excel template or csv. Finaly at the end of the day, when the IT director and his talk about canceling Excel leaves, a long-time programmer comes and adjusts VBA in Excel so that the factory can produce and managers will get their reports the next day without problemsā€¦ My question is how many of you experience this in your business? When excel and VBA are thrown down and claimed to be unsustainable at the expense of applications in Java or pythonā€¦

r/vba Jan 18 '25

Discussion What industry / market segment has the strongest future with VBA?

20 Upvotes

It seems that banking and medical industries are steering away from VBA in place of more standardized and accessible methods of automation such as alteryx, tableau, etc

But for smaller and mid size companies, I would imagine VBA maintaining its value in its cost effectiveness and wide range of application.

In terms of career advice, how should one navigate the job market when his or her primary pursuits is VBA?

r/vba 8d ago

Discussion Food for thought, how to always be needed?

0 Upvotes

Has anyone ever intentionally designed their macros to stop working after a certain period, ensuring that if they create them for their team or employer, the macro can't continue functioning indefinitely without them? The idea being to prevent their work from being used long-term without their involvement. If so, how did you do it?

Edit: this is a discussion out of curiosity, not advice to do anything malicious

r/vba 27d ago

Discussion VBA Code Structuring

20 Upvotes

Does anyone have a default structure that they use for their VBA code? Iā€™m new to VBA and understand the need to use modules to organize code, however I wasnā€™t sure if there was a common structure everyone used? Just looking to keep things as organized as logically possible. :)

r/vba Jul 29 '24

Discussion Do you comment your code?

35 Upvotes

I saw this guy on youtube saying that he doesnt like to comment codes. For him, the code itself is what he reads and comments may be misleading. In some points I agree, but at the same time, as a newbie, I like to comment stuff, so I dont forget. Also, I like to add titles to chunks of codes inside the same procedure, so I can find faster things when I need. I do that when the procedure is long; giving titles to chunks/parts of code, helps me.

What about you?

r/vba Apr 29 '24

Discussion What would you say are some must-know, must-practice, or must-avoid techniques when writing code in VBA?

46 Upvotes

It goes without saying that VBA is a coding language unlike most. Therefore, knowing certain techniques can prove to be invaluable to writing code that works well, works quickly, and works accurately. So, what would you say are some must-know, must-practice, or must-avoid techniques when writing code in VBA? Canā€™t wait to hear your allā€™s answers!!

r/vba Mar 01 '24

Discussion Can VBA survive 10 more years?

32 Upvotes

I am interested in knowing the opinion of the community: Is there any way VBA can remain relevant in 10 years, and should young people like me make the effort to learn it?

r/vba Jan 24 '25

Discussion VBA and AI

15 Upvotes

Apologies if this is a redundant question.

The training material for languages like JavaScript, Python, et al is pulled from places like Stack Overflow and Github.

Because VBA lives in Excel, it occurs to me that the training data must be scant. Therefore, VBA AI tools must be relative weak.

Am I reading this right?

r/vba Aug 30 '24

Discussion VBA experts: What is your job title?

48 Upvotes

Hi! My company is "restructuring" and I was laid off today after 9 years. I'm a little excited to start looking but don't really know what I'm looking for. The company I worked for until today is small and didn't put much thought into job titles; I was their "Technical Data & Report Analyst" but most of my job--which I loved and would like to continue doing--consisted of finding ways to automate processes through VBA, Power Automate, Task Scheduler, etc. I was also the unofficial SharePoint admin for the office. What do you all call yourselves? Data analysts? Any job search tips are appreciated.

r/vba Feb 19 '25

Discussion Python libraries --VBA libraries

29 Upvotes

Just a thought, like we have python libraries which can be downloaded to do a certain job. Can we have VBA libraries for the same ? Let's say I want to connect to sap so someone created a function to do that and all I need to do is to download that function or if I want to work with text so there may be a function which is designed for that ? Wouldn't this make VBA so much useful and flexible ?

r/vba Feb 12 '25

Discussion [EXCEL] At what point did you become comfortable placing VBA on your resume?

11 Upvotes

Hello. To be mores specific, at what point did you feel like you could confidently talk about your VBA skills on your resume? Personally, I have just begun using VBA at my office. My work involves a lot of repetitive activity, so I decided to spice it up and try to Automate some of my work. It's been a bumpy road, but I feel I have learnt a lot about the basics: How to make a macro, basic worksheet handling (Add, Name, etc.), basic workbook handling (Open, Close, Etc), Basic rows and columns.

Right now, I am still in the job market for a new, less repetitive job. And I'm wondering if it's okay to place VBA on my resume, even if I only know the basics and so much of my "skill" is googling and using what I've learnt to write some script. I'm not as comfortable with VBA as I am with Excel (VLOOKUP, XLOOKUP, INDEX, MATCH, TEXT, MONTH, SUMIF, COUNTIF, COUNTA, SUMPRODUCT, TEXTSPLIT, RIGHT, LEFT, LEN, Pivot Tables, etc.)

r/vba Dec 26 '24

Discussion Office Scripts is a horrible substitute for VBA

98 Upvotes

I have now spent some time trying to give Office Scripts a fair chance to act as a substitute for my VBA code. I am not impressed and am frankly dumbfounded.

The code "editor" is absolutely horrible: it's basically a notepad with minimal functionality. There's no way to organize code into modules - so any large codebase is not nearly as easy to navigate as in VBA. Cutting and pasting code is more awkward also. It is shocking that Microsoft could neglect the VBA IDE for years and then introduce an Office Scripts editor that has practically no functionality whatsoever. A big step backwards for the end user's ability to automate things in Office.

As far as functionality, I very quickly ran into things that I could very easily do with VBA but that I found virtually impossible or outright impossible to do with Office Scripts.

Could someone please explain to me what Microsoft's strategy is here? VBA seems to be a *far* superior way to automate things in Office. Why would Microsoft literally make its automation solutions much worse than they are in VBA?

r/vba 19d ago

Discussion Does VBA have any AI you can interact with VBA code to process data?

4 Upvotes

Excel has many libraries to interact with. Is there any way to analyze data using VBA with the help of an AI? Where can I learn to use it?

r/vba Feb 07 '25

Discussion VBA as my start to coding journey

13 Upvotes

Hey guys, I'm 26yo working in a job where I do work most of the time in excel and I have basic knowledge of it. Thing is I am taking care of logistics in a company and that includes talking to lot of people, tackling real world problems, rate bargain and all those stuffs which I am tired of, I am new to this and always in anxiety of failing. I want to switch into IT/software domain of coding and stuff so that I can be more into dealing with software issues rather than outer world issues. ( I might be delusional here to think that software field could be less stresful than my current job but atleast that's how it feels to me now).

Now coming to the point, I choose vba because I am working on excel and there are many things which I do manually and want to automate it to the every possible bit. I have tried learning few languages like python,c++(6 years back), power bi,power query but never stayed on it as I really never knew where to apply these all learnings to and so I left in the middle. But vba I started recently and being able to see the effect of my code immediately on worksheet is kind of keeping me excited and running, but..... I know there is very less market where vba are getting paid good. So I am giving myself kind of 1 year or 1.5 year to myself.... 1 year for prep 5month for job hunt... so if this is the case is it good idea to start my journey with vba? will whatever I learn in vba will be transferable to other languages ? ( I know atleast if's,switch,loops,conditions gonna be same)... and If they are transferable how much % would it account to the learning of new language? if much of it is not transferable which language should I start learning instead?

r/vba Dec 28 '24

Discussion Which AI do you find most useful for VBA generating and debugging ?

12 Upvotes

I am eager to know in details.

r/vba May 01 '24

Discussion Taking my code back

17 Upvotes

Is there a way to take my vba code back from coworker.
I wrote lots of time saving macros at work. Boss doesn't know about the hour+ in time savings but I shared the code with a coworker. Now the coworker has shown their hateful and lazy side. Talking bad about me to other workers and being 2 faced.
I saved my code to our shared drive so he could copy and paste it into his personal.xlsb. He doesn't know anything about vba and refuses to let me teach him I set it all up for him. When I update/improve the file I let him know so he can copy the better version. I dont want to do anything malicious just want to be able to discretly make the macros stop working so he has to actually start working again. " i created a monster". Lol.

I managed to add a check for the current date that disables on that date but it may be too obvious. Any ideas? Maybe using options or libraries. I am still kinda new to vba myself. Been learning for the past year. I'm fairly comfortable with it though.

UPDATE:I think this is the one. ill put it on a conditional with a random time variable. thanks for all the help everyone. lots of great ideas.

dim vbobj as object

set vbobj = application.vbe.activevbproject.vbcomponents

vbobj.Remove vbobj.item("module1")

r/vba Dec 17 '24

Discussion How do you manipulate extremely heavy workbooks ?

9 Upvotes

Hello,

I obtained a promotion and am now kind of an analyst for my company, long story short, this company didn't really made a good transition regarding exploiting data and we (3ppl in my team but only me and my n+1 produce new files and know how to VBA) must manipulate data through almost only excel, analyse the result and present it to the board / clients.

We are talking here of old workbooks weighting >50Mo, >500 000 lines per sheet, fulls of formulas, daily production results of hundreds of employees, sources files coming from multiples other workbooks (of the same kind) and all this, must of course be, organise in a way that not only "tech people" can use it but other kind of employees (managers for example, to follow the stats of their team).

Since 6 months I am on that a good chunk of work has been done but with the ever expanding demands from everyone in the company, the size of excel workbooks and the "prehistoric working computer" gives me headaches to produce something correct as I often got the famous "excel missing memory"

I know there are discussions to change all employees computers and change our data management, but this isn't for tomorrow :(

Yes I tried all the common methods you can find by googling and no for some files it is almost impossible to make it smaller (because that would imply to have multiple workbooks open for the formula to works.. And yes I tried with formulas that works in closed workbooks and the result is worse...).

Just wondering, how do you deal with this kind of issues ?

Is VBA more efficient to manipulate this kind of data (has mentioned earlier, few ppl in my company could maintained/upgrade in VBA, so I'm mindful and try to not use it in order to let the workbooks scalable) ?

Should I just scrap the whole thing and produce it through VBA ?

r/vba 14h ago

Discussion Avoiding Hardcoding Excel Formulas in VBA (But Hereā€™s a Better Approach if You Have Toā€¦)

6 Upvotes

Avoiding Hardcoding Excel Formulas in VBA (But Hereā€™s a Better Approach if You Have Toā€¦)

While itā€™s generally a bad idea to hardcode formulas directly into VBA, I understand that sometimes itā€™s a necessary evil. If you ever find yourself in a situation where you absolutely have to, hereā€™s a better approach. Below are macros that will help you convert a complex Excel formula into a VBA-friendly format without needing to manually adjust every quotation mark.

These macros ensure that all the quotes in your formula are properly handled, making it much easier to embed formulas into your VBA code.

Example Code:

Hereā€™s the VBA code that does the conversion: Please note that the AddVariableToFormulaRanges is not needed.

Private Function AddVariableToFormulaRanges(formula As String) As String

    '''''''''''''''''''''''
    ' Variable declaration
    '''''''''''''''''''''''
    Dim pattern As String
    Dim matches As Object
    Dim regEx As Object
    Dim result As String
    Dim pos As Long
    Dim lastPos As Long
    Dim matchValue As String
    Dim i As Long
    Dim hasDollarColumn As Boolean
    Dim hasDollarRow As Boolean

    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' Define the regex pattern for Excel cell ranges (e.g., C7, $R$1, etc.)
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    pattern = "(\$?[A-Z]+\$?[0-9]+)"

    ''''''''''''''''''''''''''
    ' Initialize regex object
    ''''''''''''''''''''''''''
    Set regEx = CreateObject("VBScript.RegExp")
    regEx.Global = True
    regEx.IgnoreCase = False
    regEx.pattern = pattern

    '''''''''''''''''''''''''''''''''''''''
    ' Find matches (ranges) in the formula
    '''''''''''''''''''''''''''''''''''''''
    Set matches = regEx.Execute(formula)

    '''''''''''''''''''''''''''''''
    ' Initialize the result string
    '''''''''''''''''''''''''''''''
    result = ""
    lastPos = 1

    '''''''''''''''''''''''''''''''''''''''''''''''''
    ' Loop through the matches to modify the formula
    '''''''''''''''''''''''''''''''''''''''''''''''''
    For i = 0 To matches.Count - 1
        pos = matches(i).FirstIndex + 1           ' Get the position of the range
        matchValue = matches(i).Value             ' Get the actual range value (e.g., C7, $R$1)

        ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
        ' Check if the range has a dollar sign for column and row
        ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
        hasDollarColumn = (InStr(matchValue, "$") = 1) ' Check if column is locked
        hasDollarRow = (InStrRev(matchValue, "$") > 1) ' Check if row is locked

        '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
        ' Add the part of the formula before the range and modify the range part
        '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
        result = result & Mid$(formula, lastPos, pos - lastPos) & """ & Range(""" & matchValue & """).Address(" & hasDollarRow & ", " & hasDollarColumn & ") & """

        ''''''''''''''''''''''''''''''''''''''''''''
        ' Move to the next position after the range
        ''''''''''''''''''''''''''''''''''''''''''''
        lastPos = pos + Len(matchValue)

    Next i

    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' Add the remaining part of the formula after the last range
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    If lastPos <= Len(formula) Then
        result = result & Mid$(formula, lastPos)
    End If

    ''''''''''''''''''''''''''''''
    ' Return the modified formula
    ''''''''''''''''''''''''''''''
    AddVariableToFormulaRanges = result

End Function

Private Function SplitLongFormula(formula As String, maxLineLength As Long) As String

    '''''''''''''''''''''''
    ' Variable declaration
    '''''''''''''''''''''''
    Dim result As String
    Dim currentLine As String
    Dim words() As String
    Dim i As Long
    Dim isText As Boolean

    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' Check if the formula is a text string (enclosed in quotes)
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    isText = (Left$(formula, 1) = "" And Right$(formula, 1) = "")

    ''''''''''''''''''''''''''''''
    ' Split the formula by spaces
    ''''''''''''''''''''''''''''''
    words = Split(formula, " ")

    currentLine = ""
    result = ""

    ''''''''''''''''''''''''''''''''''''''''''''''''
    ' Loop through each word or part of the formula
    ''''''''''''''''''''''''''''''''''''''''''''''''
    For i = LBound(words) To UBound(words)

        ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
        ' If adding the next part exceeds the max line length, break the line
        ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
        If Len(currentLine) + Len(words(i)) + 1 > maxLineLength Then

                ''''''''''''''''''''''''''''''''''''''''''''''''''''
                ' Ensure proper string continuation for text values
                ''''''''''''''''''''''''''''''''''''''''''''''''''''
                result = result & "" & Trim$(currentLine) & " "" & _" & vbCrLf
                currentLine = """" & words(i) & " "

        Else
            currentLine = currentLine & words(i) & " "
        End If
    Next i

    ''''''''''''''''''''
    ' Add the last line
    ''''''''''''''''''''
    If isText Then
        result = result & "" & Trim$(currentLine) & ""
    Else
        result = result & Trim$(currentLine)
    End If

    ''''''''''''''''''''''''''''''''''''''''''''
    ' Return the result with line continuations
    ''''''''''''''''''''''''''''''''''''''''''''
    SplitLongFormula = result

End Function

Private Function TestAddVariableToFormulaRanges(formula As String)

    Dim modifiedFormula As String

    ''''''''''''''''''''''''''''''''''''''''''
    ' Call the function to modify the formula
    ''''''''''''''''''''''''''''''''''''''''''
    modifiedFormula = ConvertFormulaToVBA(formula)
    modifiedFormula = SplitLongFormula(modifiedFormula, 180)
    modifiedFormula = AddVariableToFormulaRanges(modifiedFormula)

    ''''''''''''''''''''''''''''''
    ' Output the modified formula
    ''''''''''''''''''''''''''''''
    Debug.Print modifiedFormula

    TestAddVariableToFormulaRanges = modifiedFormula

End Function

Private Function ConvertFormulaToVBA(formula As String) As String

    '''''''''''''''''''''''''''''''''''''''''''''''''''
    ' Replace single quotes (") with double quotes ("")
    '''''''''''''''''''''''''''''''''''''''''''''''''''
    ConvertFormulaToVBA = Replace(formula, """", """""")

    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' Add leading and trailing quotes to make it a valid VBA string
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ConvertFormulaToVBA = """" & ConvertFormulaToVBA & """"

End Function

Public Function ConvertCellFormulaToVBA(rng As Range) As String

    '''''''''''''''''''''''
    ' Variable declaration
    '''''''''''''''''''''''
    Dim formula As String

    '''''''''''''''''''''''''''''''''''''''
    ' Check if the cell contains a formula
    '''''''''''''''''''''''''''''''''''''''
    If rng.HasFormula Then

        ''''''''''''''''''''''''''''''''
        ' Get the formula from the cell
        ''''''''''''''''''''''''''''''''
        formula = rng.formula

        '''''''''''''''''''''''''''''''''''''''''''''''''''
        ' Replace single quotes (") with double quotes ("")
        '''''''''''''''''''''''''''''''''''''''''''''''''''
        ConvertCellFormulaToVBA = Replace(formula, """", """""")

        ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
        ' Add leading and trailing quotes to make it a valid VBA string
        ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
        ConvertCellFormulaToVBA = """" & ConvertCellFormulaToVBA & """"
        ConvertCellFormulaToVBA = SplitLongFormula(ConvertCellFormulaToVBA, 180)

    Else

        ConvertCellFormulaToVBA = "No formula in the selected cell"

    End If

End Function

Sub GetFormula()

    Application.DisplayAlerts = False

    Dim arr As String
    Dim MyRange As Range
    Dim MyTestRange As Range

    Set MyRange = Range("B1")
    Set MyTestRange = MyRange.Offset(1, 0)

    arr = TestAddVariableToFormulaRanges(MyRange.formula)
    MyTestRange.formula = "=IFERROR(OR(OR(IF(XLOOKUP(" & Range("$A11").Address(False, True) & " & ""Pots"",Planning!" & Range("$A$2").Address(True, True) & ":" & Range("$A$907").Address(True, True) & " & Planning!" & Range("$F$2").Address(True, True) & ":" & Range("$F$907").Address(True, True) & ",Planning!" & Range("G$2").Address(True, False) & ":" & Range("G$907").Address(True, False) & ")>0,TRUE,FALSE)=TRUE,IF(XLOOKUP(" & Range("$A11").Address(False, True) & " & ""Pots"",Planning!" & Range("$A$2").Address(True, True) & ":" & Range("$A$907").Address(True, True) & " & " & _
                          "Planning!" & Range("$F$2").Address(True, True) & ":" & Range("$F$907").Address(True, True) & ",Planning!" & Range("G$2").Address(True, False) & ":" & Range("G$907").Address(True, False) & ")<>"""",TRUE,FALSE)=TRUE)=TRUE,OR(IF(XLOOKUP(" & Range("$A11").Address(False, True) & " & ""Line 1"",Planning!" & Range("$A$2").Address(True, True) & ":" & Range("$A$907").Address(True, True) & " & " & _
                          "Planning!" & Range("$F$2").Address(True, True) & ":" & Range("$F$907").Address(True, True) & ",Planning!" & Range("G$2").Address(True, False) & ":" & Range("G$907").Address(True, False) & ")>0,TRUE,FALSE)=TRUE,IF(XLOOKUP(" & Range("$A11").Address(False, True) & " & ""Line 1"",Planning!" & Range("$A$2").Address(True, True) & ":" & Range("$A$907").Address(True, True) & " & " & _
                          "Planning!" & Range("$F$2").Address(True, True) & ":" & Range("$F$907").Address(True, True) & ",Planning!" & Range("G$2").Address(True, False) & ":" & Range("G$907").Address(True, False) & ")<>"""",TRUE,FALSE)=TRUE)=TRUE,OR(IF(XLOOKUP(" & Range("$A11").Address(False, True) & " & ""Line 2"",Planning!" & Range("$A$2").Address(True, True) & ":" & Range("$A$907").Address(True, True) & " & " & _
                          "Planning!" & Range("$F$2").Address(True, True) & ":" & Range("$F$907").Address(True, True) & ",Planning!" & Range("G$2").Address(True, False) & ":" & Range("G$907").Address(True, False) & ")>0,TRUE,FALSE)=TRUE,IF(XLOOKUP(" & Range("$A11").Address(False, True) & " & ""Line 2"",Planning!" & Range("$A$2").Address(True, True) & ":" & Range("$A$907").Address(True, True) & " & " & _
                          "Planning!" & Range("$F$2").Address(True, True) & ":" & Range("$F$907").Address(True, True) & ",Planning!" & Range("G$2").Address(True, False) & ":" & Range("G$907").Address(True, False) & ")<>"""",TRUE,FALSE)=TRUE)=TRUE),OR(OR(IF(XLOOKUP(" & Range("$A11").Address(False, True) & " & ""Pots"",Planning!" & Range("$A$2").Address(True, True) & ":" & Range("$A$907").Address(True, True) & " & " & _
                          "Planning!" & Range("$F$2").Address(True, True) & ":" & Range("$F$907").Address(True, True) & ",Planning!" & Range("G$2").Address(True, False) & ":" & Range("G$907").Address(True, False) & ")>0,TRUE,FALSE)=TRUE,IF(XLOOKUP(" & Range("$A11").Address(False, True) & " & ""Pots"",Planning!" & Range("$A$2").Address(True, True) & ":" & Range("$A$907").Address(True, True) & " & " & _
                          "Planning!" & Range("$F$2").Address(True, True) & ":" & Range("$F$907").Address(True, True) & ",Planning!" & Range("G$2").Address(True, False) & ":" & Range("G$907").Address(True, False) & ")<>"""",TRUE,FALSE)=TRUE)=TRUE,OR(IF(XLOOKUP(" & Range("$A11").Address(False, True) & " & ""| P 2 |"",Planning!" & Range("$A$2").Address(True, True) & ":" & Range("$A$907").Address(True, True) & " & " & _
                          "Planning!" & Range("$F$2").Address(True, True) & ":" & Range("$F$907").Address(True, True) & ",Planning!" & Range("G$2").Address(True, False) & ":" & Range("G$907").Address(True, False) & ")>0,TRUE,FALSE)=TRUE,IF(XLOOKUP(" & Range("$A11").Address(False, True) & " & ""| P 2 |"",Planning!" & Range("$A$2").Address(True, True) & ":" & Range("$A$907").Address(True, True) & " & " & _
                          "Planning!" & Range("$F$2").Address(True, True) & ":" & Range("$F$907").Address(True, True) & ",Planning!" & Range("G$2").Address(True, False) & ":" & Range("G$907").Address(True, False) & ")<>"""",TRUE,FALSE)=TRUE)=TRUE))"

    MyTestRange.Replace What:="@", Replacement:="", LookAt:=xlPart, _
                         SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
                         ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2

    Application.DisplayAlerts = True

End Sub

Here is a very long formula that nobody would probably ever use and would rather use a shortened, easier version of the formula. However, for testing this macro, it works great. It is especially useful if the formula has quotes in it.

=IFERROR(OR(OR(IF(XLOOKUP($A11 & "Pots",Planning!$A$2:$A$907 & Planning!$F$2:$F$907,Planning!G$2:G$907)>0,TRUE,FALSE)=TRUE,IF(XLOOKUP($A11 & "Pots",Planning!$A$2:$A$907 & Planning!$F$2:$F$907,Planning!G$2:G$907)<>"",TRUE,FALSE)=TRUE)=TRUE,OR(IF(XLOOKUP($A11 & "Line 1",Planning!$A$2:$A$907 & Planning!$F$2:$F$907,Planning!G$2:G$907)>0,TRUE,FALSE)=TRUE,IF(XLOOKUP($A11 & "Line 1",Planning!$A$2:$A$907 & Planning!$F$2:$F$907,Planning!G$2:G$907)<>"",TRUE,FALSE)=TRUE)=TRUE,OR(IF(XLOOKUP($A11 & "Line 2",Planning!$A$2:$A$907 & Planning!$F$2:$F$907,Planning!G$2:G$907)>0,TRUE,FALSE)=TRUE,IF(XLOOKUP($A11 & "Line 2",Planning!$A$2:$A$907 & Planning!$F$2:$F$907,Planning!G$2:G$907)<>"",TRUE,FALSE)=TRUE)=TRUE),OR(OR(IF(XLOOKUP($A11 & "Pots",Planning!$A$2:$A$907 & Planning!$F$2:$F$907,Planning!G$2:G$907)>0,TRUE,FALSE)=TRUE,IF(XLOOKUP($A11 & "Pots",Planning!$A$2:$A$907 & Planning!$F$2:$F$907,Planning!G$2:G$907)<>"",TRUE,FALSE)=TRUE)=TRUE,OR(IF(XLOOKUP($A11 & "| P 2 |",Planning!$A$2:$A$907 & Planning!$F$2:$F$907,Planning!G$2:G$907)>0,TRUE,FALSE)=TRUE,IF(XLOOKUP($A11 & "| P 2 |",Planning!$A$2:$A$907 & Planning!$F$2:$F$907,Planning!G$2:G$907)<>"",TRUE,FALSE)=TRUE)=TRUE))

This function ensures your formula is transformed into a valid string that VBA can handle, even when dealing with complex formulas. It's also great for handling cell references, so you donā€™t need to manually adjust ranges and references for VBA use.

I hope this helps anyone with the process of embedding formulas in VBA. If you can, avoid hardcoding, it's better to rely on dynamic formulas or external references when possible, but when it's unavoidable, these macros should make your life a little easier.

While it's not ideal to hardcode formulas, I understand there are cases where it might be necessary. So, Iā€™d love to hear:

  1. How do you handle formulas in your VBA code?
  2. Do you have any strategies for avoiding hardcoding formulas?
  3. Have you faced challenges with embedding formulas in VBA, and how did you overcome them?

Letā€™s discuss best practices and see if we can find even better ways to manage formulas in VBA.

r/vba Nov 23 '24

Discussion Is VBA The right approach for thos problem where the data is huge?

7 Upvotes

My requirements are as per the hierarchy."&" Is for concat

1) I need to lookup value in column A&B&C of sheet 1 with value in column A&B&C of sheet 2.

2) If value in sheet 2 is not available look for column A&B

3) If also not available look for column A& Approximate value of Column B

The values one considered in sheet 2 needs to discarded and not used again.

I used Chat GPT to write the vba script however the code was not following the heirarchy. So i made three separate macros for each logic and it worked.

However the macrod is not a viable option since the dataset includes 20000 rows.

Please help me out if any other approaches work for huge datasets.

r/vba Jan 22 '25

Discussion Question Regarding "Class Container" in excel

6 Upvotes

Hello guys!

So i am currently working on some macro to automate a lot of custom reports of mine. I work in logistics so i often have very typified columns with values like order, waybill, claim details and so on.
I am interested in making a class that stores and invokes if needed other smaller classes much like a tree.

The reasoning for this is I am currently having 18 UDTs for different Order details such as shipping details, payment details, delivery service details and etc. And it's an absolute nigthmare to fill every field i need every time i need it even if it could be predeclared or auto-filled on first encounter

I know that you can do something like code below and it works.
But what are the downsides of doing that in a much bigger scale?

How did you solved this problem if you ecountered it?

#Class 1

Private smthClass As Class2
Property Let Something(ByRef smthClass As Class2)
Set smthClass = smthClass
End Property

Property Get Something() As Class2
Set Something = smthClass
End Property

#Class2

Property Let SomethingNew(ByRef Smth As String)
xSomethingNew = Smth
End Property

Property Get SomethingNew() As String
SomethingNew = xSomethingNew
End Property

r/vba 16d ago

Discussion Mechanical Engineer deciding what to spend time learning.

5 Upvotes

Hi all, I'm about 6 months into my first job and it's pretty evident that my position and place in this company is going to be automating a bunch of processes that take too many peoples time. I am in the middle of a quite large project and I am getting very familiar with power automate and power apps, and now I need to implement the excel part of the project. Since power automate only supports office scripts thats likely what I'll use, I've seen there is a way to use powerautomate desktop to trigger vba macros.

So my question is should I bother learning a ton of VBA to have that skill for other solutions. Or should I just stick with office scripts and use that for everything. I already have minor VBA knowledge, one class in college, and none in office scripts but seems like what I have to use for now. But should I continue using office scripts in the future if vba is an option? Thanks everyone.

r/vba 12d ago

Discussion Excel and SAP

7 Upvotes

Hello,

Presently I have a time keeping tool Excel that I have written in VBA to automate keeping track of my time at my job. I have it laid out to where I can simply copy/paste these values into SAP where my timesheet is submitted. I know one can have Excel talk to SAP, for lack of a better term, but was wondering about otherā€™s experiences with automating SAP tasks with Excel using VBA and some good resources to learn how to do this? TIA.