MS Excel Trick: Copy-Paste in Filtered/Visible Cells Only (Urdu/Hindi Tu...

Excel VBA: save a macro-enabled Excel file as a non-macro file

 In this code, you need to replace "C:\Users\UserName\Documents\NewFile.xlsx" with the file path and name where you want to save the new file. The xlOpenXMLWorkbook file format argument specifies that the file should be saved as a non-macro-enabled file.

Before saving the file, the code disables macros using the EnableReferences property of the VBE project. After saving the file, it enables macros again. This is important because if the file is saved with macros enabled, it will still be a macro-enabled file even if the file extension is changed.

Sub SaveAsNonMacroFile()

    'Set the file path and name for the new file

    Dim NewFilePath As String

    NewFilePath = "C:\Users\UserName\Documents\NewFile.xlsx"

    

    'Disable macros

    ThisWorkbook.VBProject.VBE.ActiveVBProject.References.EnableReferences = False

    

    'Save the file as a non-macro-enabled file

    ActiveWorkbook.SaveAs Filename:=NewFilePath, FileFormat:=xlOpenXMLWorkbook

    

    'Enable macros

    ThisWorkbook.VBProject.VBE.ActiveVBProject.References.EnableReferences = True

End Sub

Number Guessing Game, Using Excel VBA

In this game, the player has to guess a randomly generated secret number between 1 and 100. The player has 10 guesses to correctly guess the number. After each guess, the player is told whether the secret number is higher or lower than their guess. If the player correctly guesses the secret number, they win the game. If the player uses up all their guesses without correctly guessing the secret number, they lose the game.

Note: This game uses the Rnd function to generate a random number. However, the Rnd function is based on the system clock, so if you want to generate truly random numbers, you may need to use a more advanced random number generator.

Sub NumberGuessingGame()

    Dim SecretNumber As Integer

    Dim Guess As Integer

    Dim GuessesRemaining As Integer

    

    SecretNumber = Int((100 - 1 + 1) * Rnd + 1) 'generate a random number between 1 and 100

    GuessesRemaining = 10 'set the number of guesses

    

    Do While GuessesRemaining > 0

        Guess = InputBox("Guess the secret number between 1 and 100." & vbNewLine & "You have " & GuessesRemaining & " guesses remaining.", "Number Guessing Game")

        If Guess = SecretNumber Then

            MsgBox "Congratulations! You guessed the secret number in " & (10 - GuessesRemaining + 1) & " guesses."

            Exit Sub

        ElseIf Guess < SecretNumber Then

            MsgBox "The secret number is higher than your guess."

        ElseIf Guess > SecretNumber Then

            MsgBox "The secret number is lower than your guess."

        End If

        GuessesRemaining = GuessesRemaining - 1

    Loop

    

    MsgBox "Game over. The secret number was " & SecretNumber & "."

End Sub

Analysis ToolPak; Excel Add In for Statistical Analysis (Descriptive Statistics)

The Analysis ToolPak is an add-in for Microsoft Excel that provides additional functionality for data analysis. One of the features of the Analysis ToolPak is the Descriptive Statistics tool, which allows users to quickly calculate basic statistical measures for a data set. If you are using the Office 365 version, in your MS Excel file, go to Developer Tab (If Developer Tab not available, then right click on the top ribbon and click on customize ribbon and then check the Developer box) and click on Excel Add Ins and select the "Analysis ToolPak". 

To use the Descriptive Statistics tool in the Analysis ToolPak, follow these steps:

Click on the "Data" tab in the Excel ribbon.

Click on "Data Analysis" in the "Analysis" section.

If the "Data Analysis" option is not visible, you may need to install the Analysis ToolPak add-in first.

Select "Descriptive Statistics" from the list of tools, then click "OK."

In the "Descriptive Statistics" dialog box, select the range of data you want to analyze.

Choose the output options you want to include, such as the mean, standard deviation, and quartiles.

Click "OK" to generate the results.

The output of the Descriptive Statistics tool will include a summary table with basic statistical measures for the selected data set, such as the mean, standard deviation, minimum and maximum values, and quartiles. It can also include a histogram or frequency distribution chart to help visualize the distribution of the data.

The Descriptive Statistics tool is useful for quickly analyzing the basic characteristics of a data set. It can be used in a variety of applications, such as market research, scientific analysis, and financial forecasting.

In summary, the Descriptive Statistics tool in the Analysis ToolPak add-in for Microsoft Excel provides a quick and easy way to calculate basic statistical measures for a data set. It is a useful tool for data analysis and can be used in a variety of applications.

SQL Query in Excel using Power Query

 --For running SQL Query using Power Query, Go to Data Tab, Get Data, From Other Sources, Blank Query, then in query window click on Advanced Editor and use below syntax as per need.

let

    Source = Sql.Database("ServerName",  "DatabaseName", [Query=" Select ID, personname from abc..person where ID= 306258"])

in

    Source

And if we want to use Query syntax from Excel file where it is available as  a named range then use below syntax. Please note the QueryValue in below syntax is a named range cell where query is available

let

    Source = Sql.Database("ServerName",  "DatabaseName", [Query= Excel.CurrentWorkbook(){[Name="QueryValue"]}[Content]{0}[Column1]])

in

    Source